Learn how to make your SQL queries dynamic by including property values and Tag values in the query.

Video recorded using: Ignition 7.7


(open in window)

[00:00] In the previous lesson, we showed you how to use the SQL Query Binding to bring back data from a database on the table. So you have a table and the data property is bound to a static SQL Query, bringing back the first name, last name and email from a customer table. Then we're bringing back every single record from the table. Since there is no 'where' clause here. It is possible to make this query dynamic where we can bring in values from the screen as well as bring in tag values to possibly filter for particular records. So let's say that we want the user to be able to search for a last name, let's put a text field right above the table where they can enter in that search criteria. We're then going to use a text field in the query making it dynamic. So let me go back to the data property here and go into the binding. I'm going to add a where clause to this query. I'm going to say, "where the last name is like, deal search for a wild card here." Right in the middle of that string, I'm going to insert the value of the text field by using the link icon on the top right, I can go and search for the window, grab the text field and get it text property. Soon as I bring it in, you can see now the query is dynamic. And that is relying on a value from the screen.

[01:04] So the very bottom, I'm going to set this query to poll off, which means only going to run once. Now the special thing about polling off is, if the text field value ever changes, the query will run again, but just once. So as soon as I press 'OK,' I'm going to bring back all of the records that meet this, wild card here, which right now is empty strings, everything's coming back. If I go into preview mode here and start typing in a value, like I want to search for any customer with last name that starts with 'Stan,' I can see there are two customers there. I look for 'go,' I'm going to see there are quite a few where G.O was somewhere within that last name. So it's definitely possible to make a query dynamic and you can use one or more properties from the screen or tag values in your query.

You are editing this transcript.

Make any corrections to improve this transcript. We'll review any changes before posting them.