Inductive Automation
News Room

You can cut down a lot of database traffic using a single SQL query versus multiple queries to connect every component to a screen. For example, if there are 20 displays on the screen from one table in the database, you can use one query to perform the same job. Network traffic could be decreased to 1/20 of what it was for each open instance of that screen.

In addition, this technique is key in fetching an index when you want to select a record in a table and click a button to open another screen and interact with the selected data.

1. Open your project in the designer.
   
2. Open or create a window to design the objects.
   
3. For this we need to create a Dynamic Property on the root container to hold our dataset. Right click on the Root Container and select Customizers > Dynamic Properties.
   
4. Press the green + icon to add a new Property. Name the property myData and give it a Dataset data type. Press OK.
   
5. Now the myData dynamic property should appear on the root container. Set the initial values in the dataset by clicking on the dataset viewer icon to the right of the Dynamic Property name.
   
6. Click the Add Column icon to add a name column. Name it label and select the String data type.
   
7. Click the Add Column icon again to add a value column. Name it value and select the Float data type.
   
8. Click the Add Row icon three times to add three rows. Fill in the cells by double clicking the field you want to modify. Set up the table to look like the following and then press OK.
   
   

   label	|	value
   -------------------------
   Apples	|	13.26
   Oranges	|	84.09
   Bananas	|	76.45

   
   
9. Drag a Numeric Label component from the Display tab of the Component Palette into the Root Container and position it at the top.
   
10. In the Property Editor click on the bind icon to the right of the Value property on the Numeric Label property.
    
11. Select the Expression radio button and copy the following expression:
    lookup({Root Container.myData}, "Apples", -1.0, "label", "value")
    This looks up the word Apples in the dataset and returns the value which is 13.26.
    
12. Repeat steps 9-11 using a lookup expression for the Oranges and Bananas. lookup({Root Container.myData}, "Oranges", -1.0, "label", "value")
    
13. Using the Lookup() function requires that you know a value in the dataset to get the right row. Conversely, you can access the data directly by using a column and row number. Create another Numeric Label and click on the bind icon to the right of the Value property on the Numeric Label property.
    
14. Select the Expression radio button and copy the following expression:
    try({Root Container.myData}[2,"value"],-1.0)
    
    This gets the value in the 2nd row under the value column. If it does not exist -1 is returned. You can also reference the column by index like the following:
    try({Root Container.myData}[2,1],-1.0)