frevvo + SQL: Part 2. Dynamic Pick Lists

db-dynpicklist

Dynamic pick list from SQL

Everyday business activities (Customer Service, HR etc.) are simply more useful if they can access information from internal SQL databases when it’s needed. With frevvo’s Database Connector, it’s easy to create powerful, dynamic forms and workflows that put your business information at your employees’ fingertips in a timely manner.

Today, we’ll take a look at dynamic pick lists (drop downs). It’s a very common scenario and, with frevvo, you can use business rules to dynamically initialize the options (choices) in a pick list from a SQL query.

You’ll need to install and configure the Database Connector one-time so it can connect to your SQL database. The steps are as follows:

1. Define the SQL Query

<queryset name="BIRT" ...>
       <query name="allCustomers">
           <retrieve>
               <statement> SELECT customerNumber,customerName from Customers order by customerName </statement>
           </retrieve>
       </query>
</queryset>

DBConnectorAllCustomersResultSetThis is a straightforward query that simply retrieves the list of customers from the customers table in the database.

Once, this query is configured in the database connector, it can be accessed via a URL: http://<server>/database/BIRT/allCustomers. BIRT is the queryset name above and allCustomers is the query name. You can name these whatever you want. The Database Connector Tutorial explains how URLs map to queries in more detail.

This URL will return the data in the ResultSet above in the appropriate format for frevvo to use.

2. Create the frevvo form

For our example, we create a new form in the Designer. Drag in a Dropdown control. Label it whatever you want. In the Properties panel, change the Name to sc.

3. Write a business rule

To connect the Dropdown to the query, we use a rule.

/*member customerName, customerNumber, resultSet */
var x;
if (form.load) 
{
    eval ('x=' + http.get('http://<server>/database/BIRT/allCustomers')); 
    var opts= [];  
    for (var i=0; i < x.resultSet.length; i++) {    
        if (x.resultSet[i]) {       
            opts[i] = x.resultSet[i].customerNumber + '=' + x.resultSet[i].customerName;    
        }  
    }  
    sc.options = opts;
}

Let’s analyze this rule.

  1. if (form.load) – this implies that the rule will execute when the form first loads.
  2. eval (‘x=’ + http.get(‘http://<server>/database/BIRT/allCustomers&#8217;)) – this calls the above URL and returns the data. Since it’s a rule, the data will automatically come back as JSON.
  3. Loop over the JSON and create an array in the format value=label (e.g. 242=Alpha Cognac).
  4. Finally, set the options of the dropdown to the array of options using the Name we chose earlier (sc).

That’s it. When the form is loaded, the drop down will be populated with the list of customers as shown in the image above. Try it yourself using our example form.

The example initializes the customers pick list as above. When you select a customer, it uses a similar rule to initialize a second (dependent) pick list using the value from the customer pick list. You can read more about this in the Database Connector Tutorial.

Interested in learning more? Visit our website to view examples, watch videos, sign up for a free 30-day trial or contact us for more information.

4 Responses to frevvo + SQL: Part 2. Dynamic Pick Lists

  1. Pingback: frevvo + SQL: Part 3. Master-Detail Views | frevvoblog

  2. Pingback: frevvo + SQL: Part 4. Multiple Results | frevvoblog

  3. Pingback: frevvo + SQL: Part 5. Create/Update records | frevvoblog

  4. Pingback: frevvo + SQL: Part 1. Dynamic database forms | frevvoblog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: