Overview
Configure data table requests to action on an existing Data Table in the Mirata Admin Tool.
Data Table Configuration
Select Request Type
To retrieve information from a data source, select the 'Query' request type. This action allows you to access specific data based on your defined criteria, providing the information needed for your analysis or operations. Within Mirata's Integration Actions, select 'Query' from the available Request Types.
Query Type is Key
Select 'Query' to ensure correct data retrieval. Selecting 'AddUpdate' or another option will prevent data from returning.
Specify Data Table
To retrieve specific data, select the desired data table. This action informs Mirata which dataset to query, allowing you to access the precise information required for your tasks. After selecting 'Query' as the Request Type, choose the relevant data table, such as 'US States', from the list.
Understanding Different Data Table Request Options
Within Mirata there are a few different data table requests that can be performed:
Add - adds a new record to the data table
Update - updates an existing record within a data table
AddUpdate - adds a new record to the data table or updates an existing data table record
Delete - delete an existing record within the data table
Query - search for a specific record or records within a data table based on specified input parameters
QueryDelete - search for a specific record or records within a data table based on specified input parameters and then delete the record
QueryToCSV - query a data table and then convert that to a CSV file - for more information on this request please visit the Create CSV Reports article.
Example - Data Table Add
Suppose we have a sample data table with the following schema. Remember that Record Key is a default schema item and cannot be deleted:
Let's also suppose that the Record Key will follow the pattern of 'Name'+'Age'+ a random string between 000000-999999. In database terms this is technically called a composite primary key, since the Record Key is composed of multiple fields and/or strings. Each record needs to be unique, so by applying a random number, this reduces the chance of creating a data table record with a duplicate record key.
The following is the configuration for the Add request. The data table has been set to the table shown above, the request type is Add. Each field in the Request Editor is set to an input parameter, meaning that the user would theoretically need to add a value for this to be successfully added.
Suppose the following Test is run in Admin - this should Add the record to the data table:
After running this test, it appears that the Add request was preformed successfully. Let's navigate back to the data table to check if the record was properly added. Remember to use the refresh button on the table to see the update:
Example - Data Table Update
Now, suppose that the user would like to update a specific row in the data table to a new age. A new integration action needs to be set up to update the records. Notice now, that only the Record Key and the Age are set to input parameters because there will be no change to the Name or the Birthdate in the data table record. Also notice how the request type is Update.
Now, this can be tested. The important thing here is to remember that the data table can only be updated with record keys that exist, so make sure to create a few sample records (maybe with the previous Add request!) before trying this integration action out. Here is an example of what the table looks like in this example prior to testing the integration action:
Now, suppose the record with Record Key = Tim-24-000003 is wrong, and the Age is actually 25. Here is the test that would be run on the Update integration action. It is important to note a few things. First, the integration is going to look for the record with the Record Key = Tim-24-000003 and update the Age field to be 25. This will change the Age value but breaks the pattern that was used to construct the record key (Name-Age-######) because the Record Key will not be updated, even though the Age is. This is a good point to make because it is best practice to set up the Record Key in real scenarios using a special string/number or fields that will never change:
After running the Update and refreshing the data table, the record has been successfully updated.
Example - Data Table AddUpdate
A data table request for AddUpdate essentially combines the last two examples, but requires all fields to be input parameters (or at least have a constant value) because if there is a brand new record being added, all fields need to be checked to make sure there isn't going to be a duplicate record, otherwise an update will try to be performed. If a record is trying to be added/updated except none of the information is different, then nothing will happen (the AddUpdate will run but no change will be made and nothing will be added).
Technically the order of operations is check for existing record, update fields if new information is present, if record does not exist add record to table.
The following is the set up for the AddUpdate request using the same table that was used in previous examples.
Suppose before performing any AddUpdate requests, the data table looks like the following:
Let's suppose there was a new record following the schema below
Record Key = Eric-28-000004
Name = Eric
Age = 28
Birthdate = 9/4/2035
Since this record doesn't exist yet in the table, the data table request should add this to the table as a new record. Let's try it out:
Let's check the data table to see the new record added to the table:
Now suppose Eric's age is actually 29. Using the same integration action that was just used, the age can be changed and the table will update that record without creating a new record because there is an existing record with that Record Key (assuming nothing else changes)
After refreshing the data table, this is what is seen:
Example - Data Table Delete
Suppose that the ask was to now delete a specific record based on it's record key. There needs to be a new integration action that runs a delete on the data table. With a Delete request, the only thing that needs to be set as an input parameter is the Record Key. It is also important to know that this will only run if the Record Key actually exists within the data table. If the Record Key doesn't exist, then the integration action won't work (this makes sense because there's nothing for the integration action to delete)
Let's try and delete the record that was added in the last example (Eric-28-000004):
Let's navigate back to the data table and refresh to see if this record was actually deleted:
Example - Data Table Query
Suppose the ask was to find all the records in the data table where the Age = 24. There needs to be a new integration action to find those records that is a data table Query request.
This is what the data table looks like before the integration action is configured:
The Query request is set up as the following. The important thing to note with querying a data table is that the user has the ability to control which fields of the data table will be queried and if there are multiple fields that need to be checked, and if there are any other logical conditions (and/or) that will determine which records get pulled down. Since the only thing that is being looked for is the Age, that is the only Query Parameter that needs to be set.
Now, when testing this for the Age = 24, the response will be all the records in the data table for where that condition is satisfied. The age being = to 24 can be configured either by setting an input parameter like the image above OR setting the Age = a constant (24). It will work both ways - mostly, it just depends on the scenario.
After running the test, it is clear that the Query is correctly working and is finding both records that have the Age as 24. It is important to note that this does NOT actually modify the table, this is simply for returning information contained in the table.
Example - Data Table QueryDelete
Suppose now, that the ask was to query (find) specific records and then delete those specific records. There will need to be another integration action set up to perform this QueryDelete request.
This is the data table prior to configuring the integration action:
This request is set up the same way as the Query request, however, the result of the action is different (record will be deleted from the table). Let's say that the job was to query and delete where the Age equals a specific input parameter that is specified.
Assume in this example, the records will be deleted where the Age = 25. This should delete one record (Olivia-25-000002).
After checking the data table, it is clear that this record has been deleted: