DB Browse Binding

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.
   
   
   
   

   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 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.