You can help by commenting or suggesting your edit directly into the transcript. We'll review any changes before posting them. All comments are completely anonymous. For any comments that need a reply, consider emailing training@inductiveautomation.com.
Version:
LESSON LIST
-
2:27Named Query Binding
-
2:40DB Browse Binding
-
2:05DB Browse Binding – Dynamic Filters
-
1:16SQL Query Binding
-
2:26SQL Query Binding – Polling
-
1:47SQL Query Binding – Dynamic Filters
-
1:55SQL Query Binding – Scalar Query and Fallback
-
1:47SQL Query Binding – Scalar Query and Update
Take Topic Challenge
LESSON
DB Browse Binding – Dynamic Filters
Description
Learn how to use the DB Browse binding to filter results from values on the screen.
Video recorded using: Ignition 7.7
Transcript
(open in window)[00:00] In the previous lesson, we saw how to use a DB browse binding type to bring back information from a database. Essentially, we bound the data property of the table to DB browse, bringing back the first name, last name, and email from the customers table, bringing back every single record. There was no filtering here. So I have 599 rows that I brought back from the table. The DB browse binding type does allow us to do filtering where I can add key columns where I can search for particular records that meet some criteria. So in this case, I have a column in here called "store_id", where the values are either one or two. I have customers in Store 1, I have customers in Store 2. So I've added a dropdown list to my window that has these two options, so we can see Store 1 and Store 2 as options. Behind the scenes of course, the ID 1 and 2 is what we're going to use in the actual query. So I can go back to my binding here and I can actually use a store ID as a key column, so I can filter on it. So I'm going to click on "store_id" here and then I'm going to click on the key to actually use it as a key column. It puts this down here, where it says "store_id =" and then the value. I can hardcode the value by putting a one here. You see at the bottom it's going to generate a query where it's going to select the information from the customers table, where store_id = 1. So it's going to filter for that particular criteria. I can still then bring back the first name, last name, and email, I can select those three columns. Then it builds that query out for me. So rather than hardcoding a value, I can actually link this to a value on the screen. So I can bind this to a property, which is going to be the dropdown's selected value. That will then, whatever I've selected in the dropdown will come into my query, making it dynamic. I'm going to bring back the first name, last name, and email. I can also do more than one key column here if I want, I can add multiple at the same time. As soon as I do this and press OK, now it's going to show me 326 rows for Store 1. If I go and change this to Store 2, the query's going to run again and now I have 273 rows. So I can definitely make a dynamic query using the DB browse feature if you don't know how to construct a SQL query.