DB Browse Binding

DB Browse Binding

Watch the video

Binding Properties to Database Tables

The DB Browse binding is technically equivalent to the SQL Query binding, except that it helps write the queries for you. Using the Database Browse binding type, you can pick the table from a list of tables in each database that you want to pull content from. If you have a fixed range of data you need to return, simply select it in the table, and watch the query get generated.

In the Browse Database tree, you can choose which columns in your table should act as your keys (these columns get put in the WHERE clause based on your selection) and which columns should be used to sort the data (these columns are put in the ORDER BY clause).

tip

This binding type also serves as a convenient jumping-off point for the more flexible SQL Query binding. Construct the basic outline of your query in the DB Browse section, and then select the SQL Query radio button to convert to the new binding type. Your query will be retained and can then be modified manually.

note

Note, the examples in this section assume Ignition is connected to a Database, and the connection contains some database tables.

Configuring a DB Browse Binding

Key Column

The DB Browse binding is easy to get started, and helps create queries for you. Let's get started.

1. Place a Table component on the window.

2. With the Table component selected, click the binding icon next to the Data property in the Property Editor.

3. The Property Binding window will appear. Select the DB Browse radio button.

4. A tree of available database tables will appear under the Browse Database heading. Once a table is selected, you can see a sample of the data in the Preview pane.

5. If you expand a database table (click the + button next to the table name) you may find a Key button . This denotes that the binding will attempt to use this column in a WHERE clause in the resulting query that is automatically generated.

   1. In this example, you'll notice that the Generated SELECT Query is returning all columns from the table. This is because the plant_information table is selected instead of a column, so the binding will attempt to return every column from the selected database table.

      

6. To remove the WHERE clause, simply select the id column in the Browse Database section, and click the Remove Key button to the right of the Browse Database window. You'll notice a couple of things:

   1. The Key icon next to the id column in the Browse Database disappeared since it is no longer being used as a Key Column.

   2. The table Preview panel on the right highlighted the entire id column.

   3. Since the Browse Database in not set to identify a Key column, there is no longer any filtering of our query under the Key Columns section, so it will bring back all of the data in the columns that we select.

   4. Notice that our Generated SELECT Query changed. It no longer has the WHERE clause, and since the id column is the one currently selected, we have gone from selecting all columns to only selecting the id column.

      

Sort Order

In the DB Browse binding, you can also do ordering of data in the table. There is a Sort button that allows you to sort in ascending and descending order. Using the same example, let's have the area column of our table be the key column so you can only see the entries for area B, and return all the operators, siteid, and supervisors columns in the table. We'll also order the data by the number of operators at each site in ascending order.

1. Select the area column in the Browse Database section, and hit the Key button to have the query filter by area.

2. Select the operators column in the Browse Database and then click the Sort button. This will sort based on the column in ascending order. (Click the Sort button again to sort in descending order, and click it a third time to remove the ordering).

3. Select the operators, siteid, and supervisor columns from the Browse Database section since those are the columns we want returned. No need to return the area column since we only want to see the entries for area B.

4. Enter the area key to equal 'B' in the Key Columns section.

   

5. Confirm the binding by clicking OK to produce the specified data in the table. Only area B rows are shown, and our data is being sorted in ascending order of our operators column.

   

   note

   This example only had one key and one order column, but you can add as many as you want. Just select a second column and hit the Key or Sort buttons.

Dynamic Filters

DB Browse Binding - Dynamic Filters

Watch the video

DB Browse bindings also give the ability to bind a property to the key column to allow for dynamic filtering of the returned data. This allows you to give the operators some control over the data they are seeing. This example is using a list of companies that has their respective city and state.

1. In the designer, drag a Table component and a Text Field component on a window.

2. With the Table component selected, click the binding icon next to the Data property.

3. Let's pull all the data from this Table except for the id, and filter on state. Remove the Key from the id column and place it on the state column.

   

4. Select the cityname, company, and state columns. You can do this with Control+Click, or by clicking and dragging in the results table in the upper right.

5. Instead of statically typing in a value like we in did in the above example, let's make it dynamic using the Text Field. Click the Binding button next to the value in the Key Columns section, and select the Text property of the Text Field. Notice there is now a property reference in the Key Column as well as the Generated SELECT Query.

   

6. Confirm the binding by clicking OK, and you'll see the Table change based on the value typed in the Text Field.

Dynamic Filter: Arizona	Dynamic Filter: California	Dynamic Filter: Colorado