frevvo + SQL: Part 3. Master-Detail Views

db-master-detail

Master-detail 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. In Part 2 of this series we saw how to create dynamic pick lists initialized from SQL.

Today, we’ll take a look at master-detail views. It’s another very common scenario in real applications – look up details for a customer or a student or a part number. With frevvo’s Database Connector, complex forms like this can be created without programming.

As before, you’ll need to install and configure the DB Connector one-time so it can connect to your SQL database. After that, the steps are as follows:

1. Define the SQL Query

<queryset name=”BIRT” …>
  <query name=”customerByNumber”>
    <retrieve>
      <statement>SELECT * from Customers where customerNumber={cnum}
      …

This is a straightforward query that simply retrieves customer details. It can be accessed via a URL: http://<server>/database/BIRT/customerByNumber?cnum=242.

In this query, notice the {cnum} in curly braces. That’s a variable. When frevvo asks the DB connector to run this query, it will provide a value for cnum in the URL. Try it now in your browser. You’ll get back an XML document with customer details for Alpha Cognac.

Now, we need to connect this to the form. Since customer details is a complex structure (first name, last name, phone etc.) we will use Live Forms’s built-in XML features to generate controls and populate them from the database.

2. Generate an XML Schema

The DB connector can generate an XML schema from any query’s <retrieve> operation. This schema can then be used to generate a Live Forms form. Try it now in your browser. You’ll get back an XML schema. Save the resulting XML schema file to disk and upload it to Live Forms. See the documentation for detailed instructions. Note that we need to pass in a known customer number so that the query can successfully execute.

dbconnector-master-detail

Automatically populate master-detail view from SQL

3. Create the form

We’ll use a copy of the form from the previous article. It already has a dynamic pick list which lists the customers in the format value=label (e.g. 242=Alpha Cognac). Edit the form and:

  1. Create a new data source from the Schema uploaded above in Step 2.
  2. Add controls to the form and rearrange them as desired.

4. Link to the database

Now, we connect to the DB query defined above. We use Doc URI wizards to do this. In a nutshell, this means configuring the form so that the data source is bound to the URL for the query that we saw in Step 1.

Try it now using our example form.

Interested in learning more?The Database Connector Tutorial explains this in great detail. Register for an upcoming webinar and visit our website to view examples, watch videos, sign up for a free 30-day trial or contact us for more information.

3 Responses to frevvo + SQL: Part 3. Master-Detail Views

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

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

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