SQL Query Binding - Dynamic Filters


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

Video recorded using: Ignition 8.1


(open in window)

[00:00] In this lesson, we'll demonstrate how to dynamically filter data that's been returned into a table from a database. In a prior lesson we showed how to use a SQL query binding to bring back data from a database onto a table. If we select this table and then scroll down to its data property binding we see that it's bound to a SQL query which returns first name last name and email from a customer's table. All such records are returned since this query has no where clause, but it's also possible to make this query Dynamic, whereby we can bring in some external search patterns from screen components or even as tag values to filter for particular records. So back in our table, let's say we want the user to be able to search for a last name We'll add a text field component above the table make it a little bit bigger like so where the user can specify some search criterion, then we'll incorporate this value into our query to make it Dynamic. So if we reselect our table and And click on its data binding to return to its backend SQL query. Let's add one line to filter the return database records. After the from customers we'll add where last name like and then we'll add this wild card search pattern placeholder.

[01:28] Like so. Within single quotes. Then right in the middle of that search pattern placeholder, we will insert the value of the text field using the property value icon here at the top. Right we can navigate down to the text field and expand it and select its text property. Once we click OK we can see that our query is now Dynamic and now it relies on a search pattern path provided from the screen down near the bottom. We'll leave the polling mode set to off which means it will only run once but the special thing about polling off is if the text field value ever changes the query will run again, but only once so to test our changes, let's go to preview mode, you know the top and by entering various search patterns such as AR or ER or even some specific last name we can see the search returns.

[02:25] Just those last names containing that pattern. By the way note also that the searches are case insensitive. Finally if we want to see all the records again. We can just search on an empty string. So summarizing this lesson. We've shown how to make a SQL query binding Dynamic by adding a where clause which uses a parameter binding back to some screen component for a filtering pattern.

You are editing this transcript.

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