DB Browse Binding

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 browser, you can pick the table that you want to pull content from. If you have a fixed range of data to choose, simply select it in the table, and watch the query get generated.

In the browse tree, you can choose which columns 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).

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 flip over to the SQL Query binding. Your query will be retained and can then be improved by hand.

The DB Browse binding is easy to get started with, and helps to build queries for you. I opened up a DB Browse binding on the Data property of a table. By default, it will select a key for you, usually the id column of your dataset. You can leave that alone, or remove it if it is not needed or should be changed. Also notice that because I have the table highlighted instead of specific columns, it will pull back all of the columns in the query that is generating below. Selecting a specific column or columns either from the list of columns on the left or the table preview on the right will modify the query to only bring back the selected columns. Also, because I have not yet specified an id for the key, the WHERE clause in the generated query is blank.

images/download/attachments/6047964/image2016-9-2_10_21_59.png

I don't want my WHERE clause to be based off of the id column, so I removed that by clicking on the id column and clicking the now lit up key button to the right of the list of columns. This will change many things. First, the key icon next to the id column disappeared since it is no longer being used as a key column. Second, the table preview on the right is now highlighted the entire column. Because we don't have a key column anymore, there is no longer anything filtering our query, so it will bring back all of the data in the columns that we select. Finally, notice that our generated 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.

images/download/attachments/6047964/image2016-9-2_10_32_13.png

In my example, I would like the area column to be my key column. So I select the area column, and hit the key ( images/download/attachments/6047964/image2016-9-2_10_41_18.png ) button next to it. Now my query is being filtered by the area. I know I want to return the operators, siteid, and supervisors columns, but I also want to order my data by the number of operators at each site. So before I select my columns, I first select the operators column and then click the sort ( images/download/attachments/6047964/image2016-9-2_10_40_2.png ) button. This will sort based on the column in ascending order. I can click it again to have it be in descending order, and a third click will remove the ordering. I then get to select the columns operators, siteid, and supervisor, since those are the columns I want returned. I don't need to return the area column, since I will only be seeing the entries for area B anyways. Notice that I have changed my area key to equal 'B'.

images/download/attachments/6047964/image2016-9-2_10_37_19.png

Confirming the binding like this will produce our data into the table like we specified. Only area B rows are shown, and our data is being sorted in ascending order of our operators column.

images/download/attachments/6047964/image2016-9-2_10_43_6.png

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.

Again, I have a table component, but I also selected a Text Field component and placed it on the screen next to my table. I opened up the binding on my data property of my table, and selected my table. This table has a list of companies and their respective city and state. I want to pull all of the data from this table except for the id, and I want to filter based on the state. So I removed the key from the id column and placed it on the state column.

images/download/attachments/6047964/image2016-9-2_14_13_56.png

I then selected the three columns that I want to select. Instead of statically typing in a state like the image above, I want to make it dynamic using our text field. So I click the binding button next to the key column, and select the text property of my text field. Notice how now there is a property reference in the key column as well as the generated sql query.

images/download/attachments/6047964/image2016-9-2_14_16_12.png

After confirming the binding, now our table will change based on the value we have typed in the text field

images/download/attachments/6047964/image2016-9-2_14_17_46.png images/download/attachments/6047964/image2016-9-2_14_18_13.png images/download/attachments/6047964/image2016-9-2_14_20_53.png

Similar Topics ...