How To Modify Tables Within the App Editor
Here's how to read and write to Tables from frontline operations apps.
In this guide, you will learn...
- How to read existing Table Records in an app
- How to update existing table records in an app
- How to create new table records in an app
- How to analyze data in tables based on app Completions
In the App Editor, you can read, edit and create individual table records. Since Tables do not require any code, you can run your apps in the Player and watch the Tables update in real time.
This guide will cover the various ways that you can use tables and apps together to create more powerful apps.
When building apps, you may need to decide whether a specific piece of data should be stored in a Variable or in a Table Record.
Variables are better suited for data that is relevant to individual usage of that particular app. Tables are better suited for data that needs to be accessed by multiple apps.
Tables are frequently used to track concepts like:
- Work orders
- Individual product SKUs
- Bill of Materials (BOM)
- Skills matrix
- ...and much more
Before reading this article, you should first read "An Overview of Tables to get a full understanding of the capabilities of Tables.
If you would like to see a full example of a Table and App being used together, check out this walkthrough of a work orders table
There are three steps to reading or updating any Table Record:
1- Create a "Record Placeholder" to give a name to that particular Table Record within the app
2- Load (or create) the record in a Trigger Action
3a- If you would like to write logic around values in the Table Record, then include the Table Record in an "If" statement
3b- If you would like to update individual fields in the record, include the Table Record in a "Then" statement.
Creating Record Placeholder
First, you will need to create a “Record Placeholder” in the app where you will be accessing the Table Record. These are placeholders for records that will later be dynamically loaded in Trigger logic.
For example, if you wanted to edit a record from a “Work Orders” table in an app, you might have a Record Placeholder called “Active Work Order”. You would later load a Record dynamically based on operator input, but it would always be referenced as the "Active Work Order"
To set up these placeholders, click the Records Tab next to the Steps List.
Then choose “Add a Table”. Next, click add a "Record Placeholder". You will be able to give a name to the record that you will load dynamically from a Trigger (ie "Active Work Order").
Note there is a setting "Save for Analysis". When selected, this will save the Table Record loaded into this Record Placeholder into the app's Completion Record. In the completion's data you'll see a link to the record.
Deleting Record Placeholders
To delete a Record Placeholder, select the placeholder from the Records List on the left side of the screen. In the example below, you would choose "Test Record Name".
Then, choose "Delete" from the following menu.
you must first remove the record placeholder from all triggers where it is referenced. You can use the "Steps where used" list to find the steps with the triggers that need to be modified.
Now you are ready to use the Trigger Editor to dynamically select records.
Accessing Records in the Trigger Editor
After creating theRecord Placeholder, you can now write the logic to dynamically load or create records based on operator input.
You can use the "Table Records" and "Data Manipulation" Trigger actions to accomplish this. Here's how to do it.
You can read or modify table records with multiple types of Trigger Actions. Here is a quick summary.
Table Records/ Load Record: Load a record with a specific ID
Table Records/ Create Record: Create a new record with a specific ID
Table Records/ Create or Load Record: IF a record with a specific ID does not exist, create it. Otherwise, load the record with a specific ID.
Loading Existing Records
In order to read or edit a Record, you must first load the record into an app and give it a placeholder name. This is frequently triggered by a step opening or a button press.
You can load one Record at a time, and you must use the unique ID (first field of the Table, text value) to choose the record.
Use the “Table Records” “Load Record” statement to accomplish this.
- “Load Record”
- By ID: “text” - 0146
- Into the “active_wo” placeholder
In this example, a record with ID "0146" already exists in a work orders Table.
Since the “ID” field in each table is a text field, you must use text to access individual records. You can use all the typical options to choose an ID:
- Table Record
- Static Value
- App Info
Here's an example where a variable is used:
- "Table Records" "Load Record" by ID: "Variable" "text_wo_id" into: "Active Work Order"
You will likely need to use a variable when the input is dynamic, like when it is tied to operator input in a text Widget in a Form Step.
Creating New Records
If you want to create a new Record in a Table, you must use the “Create” or "Create or Load" option.
If you use "Create", you must create a new record with an ID that does not already exist in the table. If the record already exists in the table, the app will throw an error.
If you use "Create or Load", a record with a specific ID will be loaded if it already exists, or created if the ID does not exist in the table.
In this case, we use the id “newID” to create a new record.
- "Table Records" "Create Record"
- by ID: "Static Value" "text" "newID"
- into: "active_wo"
Usually, you must use a dynamic value in this field, like a variable, since every time an operator uses the app, you will want to create a new value.
Editing Records in the Trigger Editor
To edit a record, you will use the “Data Manipulation” action, then “Store” to edit or update the value in a record.
In order to edit a record, you must already have it loaded as a record placeholder via "Table Records" "Load" or "Table Records" "Create".
Here’s an example where you will update the “status” field of a record loaded as “active_wo”.
- “Data Manipulation”
- “Text”: "Passed"
- "Table Record" “active_wo”
- Field: “status”
You would commonly use a Widget Trigger to do this, for example, when an operator presses a button to indicate a change.
You can also increment a value from a table record. See this guide to inventory tables to learn how
Using Conditional Logic with Table Records
You may also want to change app logic based on the value in a field of a specific record.
To do this, make sure a record has already been loaded. Then, select a field from the record and use the same logic that you would use in any other “if” statement.
Table Record: Check for a specific value in the field of a loaded table record.
"Table Record" "TestImg" "ID" "Contains" ...
Table:* Check if a record with a specific ID already exists in a Table.
- "Table" "workorders" "has record with id" "Static Value" "text" "001"
Here's an example. In this case, we will check whether the “status” field contains some other text.
- “Table Record” “active_wo” “status”
- "Static Value" "text" "Passed"
You can also use an "If" statement to check if a record with a given ID already exists, and then throw a specific error message, for example.
Here's how you might create an error message the prompts the operator to input a different work order number.
- "Table" "workorders" "has record with id"
- "Static Value" "text" "024dfg45"
- "Show Error" "Static Value" "text" "This work order already exists, please enter a new one".
Displaying Table Records On Steps
To add this Record to a step, choose the “Table Record” option under the “Text” button in the Widget Menu.
In the Widget Tab of the Context Pane, choose your Record Placeholder from the options next to “Record” to see it in the Player.
This is commonly used to display work order details to an operator, for example.
It is also helpful when you are building apps and you are unsure of the values in a specific table record.
To reorder/remove individual fields from the display, click the x or the grid symbol on the property in the Context Pane.
To change the number of columns, use the Columns option in the Context Pane.
To change the display of the labels and values, adjust the color and font size in the Context Pane.
To see how this data will be shown the operator, run the app in the Player, and you will be able to see the whole Table Record. Values will be included if you first load a record.
Clearing Record Placeholders
Sometimes, you may want to clear a record placeholder so that there is no specific record loaded into the app under that Record Placeholder.
Here's how to do that:
- "Table Records" "Clear Record Placeholder"
Displaying Users and their Fields
You can also create a Record Placeholder for users and their associated fields and then load a specific user with the Embedded Table. For more details, see the separate guide on User Fields.
Did you find what you were looking for?
You can also head to community.tulip.co to post your question or see if others have faced a similar question!