Database

The Database is where you manage and manipulate data within the Mitra platform. It allows you to create tables, attributes, load external data, and create scripts, queries, and database views.

2. Creating New Items

At the top of the data list, the “+ New” button allows you to add tables, import CSV files, import from external databases, and create queries, scripts, and views.

2.1. Table

Allows you to create tables directly in Mitra’s database. There are two types of tables: with a single key or with a composite key.

2.1.1. Single Key Table

A single column uniquely identifies each record. This type of table is used for registrations or transactions. The configuration includes the ID field, where you define if it will be Auto Increment, Numeric, or Text. Other configurations, such as the group to which the table belongs, can be adjusted as needed.

Standard attributes such as ID, Description, Creation Date, Created By User, Last Modification Date, and Last Modified By User are created automatically and cannot be changed or removed.

After creating single-key tables, the following configurations and data loads are allowed:

Configurations:

Change the group to which the table belongs.
Define the ID type: numeric, text, or auto incremental.
Adjust the display type: only description, only code, or both.
Define the default sorting: by code or by description.

Data Loads: Create an external data load for this table, such as importing data from a CSV file or from a connected external database. For more details, see the "Connections" documentation.

2.1.2. Composite Key Table

Composed of two or more columns that together uniquely identify a record. When creating a composite key table, you select the fields that will make up this key.

After creating composite key tables, the following configurations and data loads are allowed:

Configurations:

Change the group to which the table belongs.
Manage the composite key fields: Add or remove fields that make up the composite key.

Data Loads: As with single key tables, it is possible to configure external loads to populate the table from various data sources, such as CSV or external databases. For more details, see the "Connections" documentation.

2.2. Import CSV

Allows you to load data from CSV files. It can be used for tables with either single or composite keys. Refer to the "Connections" documentation for more details.

2.3. Import from External Database

Connects and imports data from external databases. It can also be used for tables with single or composite keys. More details can be found in the "Connections" documentation.

2.4. Query

Create and execute custom SQL queries. Use the data dictionary and filter variables to build queries efficiently and integrate them with filters applied to screens.

2.5. Scripts

Allows you to write DML operations such as INSERT, DELETE, and UPDATE. The data dictionary and filter variables are also available to help create the scripts.

3. Table Configurations

In the table configurations, you can manage how the data will be displayed and sorted in selectors and views, as well as choose whether the table will be displayed in Drill operations or not.

3.1. Creating a New Attribute

To add a new attribute, click the “+” icon next to the last listed attribute. You can define the name and type of the attribute. It can be numeric, text, date, single selector, or a Foreign Key (FK), which creates a relationship with another table.

3.1.1. Dynamic Attribute

Mitra also allows you to create dynamic attributes, which are a type of view that pulls data in real time based on a query. It enables the values to be updated dynamically according to the underlying data.

When configuring a dynamic attribute, your query must return two columns:

A column with the ID of the record where the attribute will be created.
A column with the result of the dynamic attribute.

These two columns need to be mapped, meaning they must be identified and marked in the system so that Mitra correctly recognizes their values and relationships.

Practical Example:

Let's illustrate the process of creating a dynamic attribute with a simple example: imagine you have a "Financial Transactions" table containing unit price and quantity data for each transaction. Our goal is to create a dynamic attribute that automatically calculates the total value of each transaction (quantity × unit price). Thus, whenever the quantity or unit price is changed, the total value will be updated in real time.

Step-by-Step:

Access the Table: Navigate to the table where you want to add the dynamic attribute. In this example, we are in the "Financial Transactions" table.
Add New Attribute: Click the “+” button at the top of the table screen to add a new attribute.
Select Attribute Type: Choose the attribute type you want to add. In our example, the attribute will be numeric (as we are calculating a value).
Mark as Dynamic: When selecting the attribute type, check the “Dynamic” option. This will enable a field for you to write the query that will be used to calculate the dynamic value.
Write the Query: Now, in the query field, you should write the SQL statement that will return the desired values. In our example, the query will return the "ID" of the transaction and the calculated "Total Value."
Column Mapping: After executing the query, you will need to configure the mapping of the results to define which column from the query will be associated with which attribute. The first mapping should be on the "ID" column returned by the query, adding the "ID" chip from the "Financial Transactions" table. The second mapping should be on the "TOTAL_VALUE" column of the query, adding the "Total Value" chip.
Expected Result: After following these steps, you will have a dynamic attribute configured to display the total value of each financial transaction in the table, based on the multiplication of "quantity" by "unit price." Refer to the video tutorial for a detailed configuration example.

3.2. Native Tables in Mitra

Mitra automatically generates some native tables, such as Users, User Types, Calendar, Screens, and Modules when a project is created.