On the Regions tab you click a button (1) to populate a list of Regions (2). If you select a Region (3) then you are shown the Capital City and Departments (4) associated with the selected Region.

Note: All of the scripts that shown in this lesson are in the group "RegionsGroup" script.

A SQL Query object provides a simple way to get data from a database and into a format you can work with in Revolution. To populate the list of Regions you simply have to:

1) Create a SQL Query object using the table you are targeting. The object is stored in the array variable theQueryA.

2) Set properties so the query returns the expected results (e.g. the column the regions will be sorted by).

3) Tell the SQL Query object to send a request to the database. This example converts the result to an array.

4) Use the data however you need to. This example displays the array in a Data Grid. Note that the data grid has already been configured with a column named "name" so that the "name" property of the regions table will be displayed.  

A SQL Record object provides a simple interface for creating, retrieving, updating and deleting records from your database. Let's look at how to load a Region's capitol city using a SQL Record.

1) Use sqlrecord_find to locate records in the database. This example searches the "towns" table in the database for one that has the 'code' matching the capitol city for the region. The found SQL Record object is stored in the array variable theRecordA. A SQL Record object is just an array that has a some special keys prefixed with @. These keys enable SQL Yoga to work its magic.

2)  Assign the "name" column of the Record to a field. Data in the database is stored as UTF-8 which is why the unicodeText is set.

Here is an example of how to filter the results that a SQL Query object returns. In this case only departments related to the selected Region will be returned in the query.

1) Create a SQL Query object that targets the "departments" table.

2) Set the conditions property. The ":1" used in the search condition is a binding variable. The ":1" will be replaced with variables that you pass in. In this case the ":1" will be replaced with the value of theRegionA["code"]. Also note that when setting the conditions property you can specify search conditions using english terms. "region is ':1'" translates to "region = ':1'".

3) Set the order by property so that results are returned in the right order.

4) Tell the SQL Query to retrieve the data from the database and to store it as an array in the variable theDepartmentsA.

5) Assign the array variable theDepartmentsA to a Data Grid.