Posted in Database

How to easily create Dynamic Database Forms

Useful processes connect to the database.

Practically every organization has at least one SQL database and often many of them. Many day-to-day business activities are simply more useful if they can access information from SQL databases on-demand.

For example, when a customer calls about a mistake on their order, your Customer Service personnel will be able to better help the customer and solve the problem quickly if they have visibility into order history from the database. Faster problem resolution leads to happier customers.

Forms connected to back end systems are easier to use.

When HR needs to on-board or off-board a new employee, it’s easier for them to do their job if the process is electronic and connected to HR and other business databases with relevant information. Save time for HR personnel and they can focus on what’s important – attracting and retaining talent.

Database forms are easy with frevvo’s connector

This is a fairly technical article that contains SQL queries, JavaScript etc.

Let’s take a look at how you can easily create powerful dynamic forms and processes using frevvo’s Database Connector.

The Database Connector allows you to perform sophisticated integration with your database. We’ll explore several possibilities in detail such as dynamic dependent pick lists, master-detail views, creating and updating records in SQL and running stored procedures.

First let’s take a look at how the DBConnector works. frevvo communicates with the outside world using XML (or JSON) over HTTP(S) i.e. it’s RESTful. Databases want you to talk SQL and return data as ResultSets.

DBConnector translates between XML, JSON and SQL.

With frevvo, the steps are straightforward:

  • Configure the Database Connector using SQL or Stored Procedures.
  • Generate form controls from the configured queries (no programming).
  • Drag and drop in the Form Designer to rearrange controls.
  • Connect forms/workflows to queries using URLs exported by the Database Connector.

frevvo takes care of all the data transformations in both directions. JSON, XML, SQL – it’s all transparent to the form designer and you don’t have to worry about programming any of this. The result of a SQL query could be a single row or multiple rows each with many columns – the form will instantly update to show you the information relevant to your request.

Try now: Master Detail form gets customer information from SQL.

You’ll need to install and configure the Database Connector one-time so it can connect to your SQL database. Then follow the steps below for each section.

Use Case: Dynamic Pick Lists

Let’s 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. 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>
All Customers Resultset.

This is a straightforward query that simply retrieves the list of customers from the customers table in the database. Once the query is configured, it can be accessed via a URL that looks something like this:  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 business 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’)) – 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 now: SQL Dynamic Picklists example.

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.

Use Case: Master-Detail Views

Master Detail forms.
Master-detail from SQL

Master-detail views are another very common scenario in real applications, for example, when you want to 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.

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 that looks like this: 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. You’ll get back an XML document with customer details for Alpha Cognac.

Try now: Get Customer Information by Customer Number.

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

XML schema generation.

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 now: Generate an XML Schema. 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.

3. Create the form

We’ll use the form from the Dynamic Pick Lists section discussed above. 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

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. The Database Connector Tutorial explains this in great detail.

Try now: SQL Master Detail example form.

Use Case: Multiple Results

The form can display multiple rows from SQL.
Multiple Rows from SQL

Now we’ll take a look at queries that return multiple results. It’s another very common scenario in real applications e.g. look up order line items for a particular order. With frevvo’s Database Connector, complex forms like this can be created without programming.

The steps are as follows:

1. Define the SQL Query

<queryset name=”BIRT” …>
  <query name=”orderDetailsByOrder”>
    <retrieve>
      <statement>SELECT p.productName as product,
o.quantityOrdered as quantity, o.priceEach as
price, p.productDescription as description, p.MSRP FROM
OrderDetails o, Products p 
WHEREo.productCode=p.productCode and o.orderNumber=
{onum} ORDER by o.orderLineNumber
      … 

This is a straightforward query that simply retrieves order details for a particular order number. It can be accessed via a URL that looks like this: http://<server>/database/BIRT/orderDetailsByOrder?onum=10100. As before, the {onum} in curly braces is a variable whose value should be passed in the URL.  You’ll get back an XML document with order details.

Try now: Retrieve details for a particular order number

Connect this to the form. 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 now: Generate XML schema in your browser, 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 order number so that the query can successfully execute.

3. Create the form

We’ll use a copy of the form we created for the Dynamic Pick List. 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

Connect to the DB query defined above. As before, 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. The Database Connector Tutorial explains this in great detail.

Try now: Dynamic sales order line items from a database.

Use Case: Create/Update Records

frevvo + DB Connector = EZ dynamic forms

Many business problems require creating new records or updating existing records in the database. We’ll take a look at these scenarios and see how you can create complex forms without programming using frevvo’s Database (DB) Connector. Fair Warning: this is a fairly technical topic so we’ve only touched upon the highlights here. You can explore in detail by reading the DB Connector Tutorial in the documentation.

1. Define the SQL Queries

This time, there are multiple SQL queries.

<queryset name=”BIRT” …>
  <query name=”createOrder”>
    <retrieve>
      <statement>SELECT orderNumber as onum,
  customerNumber as cnum from Orders WHERE
  orderNumber=10100
      …
    </retrieve>
<create>
      <statement>INSERT into Orders (orderNumber,orderDate,
   requiredDate, status,customerNumber) VALUES ({onum}, Now(),
   Now(), ‘In Process’, {cnum})
      … 

2. Generate an XML Schema

The first query is used to generate an XML schema and form controls bound to that schema. There will be two (onum and cnum). In this case, you’ll have to modify the XML a bit due to a current limitation in the DB Connector. See the documentation for detailed instructions.

3. Create the form

Generate the form using the above XML schema as a data source.

4. Link to the database

Connect it to the DB query defined above. We use Doc URI wizards with the only change being that we configure a Write URL using HTTP POST (create) instead of a Read URL.

That’s it. When the form is submitted, frevvo will generate an XML document with values for onum and cnum and send the XML to the DB Connector. The Connector will extract onum and cnum from the XML and execute the INSERT operation.

Insert Multiple Records

It’s easy to build upon this to satisfy far more complex business requirements. For example, in this case, you want to insert an Order but there will also be multiple Order Line Items. This means running an INSERT for the Order itself + multiple INSERTs for the line items with different data. Fortunately, with frevvo you can create forms with multiple Data Sources. Each one generates its own XML document and each one can have its own Doc URI.

One form can display multiple documents
A single frevvo form can process multiple documents and execute multiple SQL queries when the form is submitted.

Update Existing Records

You can equally easily update an existing record with the DB Connector. The process is almost identical. The only difference is that you will use an <update> query instead of a <create> query and the document is sent using HTTP PUT instead of POST.

 <queryset name=”BIRT” …>
  <query name=”customerCreditLimit”>
  <update>

      <statement>UPDATE Customers SET creditLimit = 
   {creditLimit} WHERE customerNumber={cnum}
      … 

The rest is identical. Generate the form using an XML schema, connect the resulting document to this query and frevvo + the DB Connector take care of the rest.

Auto Create Rows

You can also setup the queries so that they will first try an update and if it fails, create a new record.

 <query name=”customers” autocreate=”true”> 

The autocreate feature is particularly useful when working with Repeat Controls or Tables. With these controls, your forms can work with dynamic collections, for instance: customers, cars, addresses, dependents and others. When the user loads the form, the form may be initialized with some items. If the user adds new items to the collection, updates a couple and submits the form, the items will be automatically added to the database and the existing ones will be updated if autocreate=true.

Auto Delete Rows

Auto-creation raises the obvious question. What if the user deletes a row from the repeating collection described above. If you want the DB Connector to delete the corresponding records from the database, you can simply set the autodelete property and provide a key.

 <query name=”customers” autocreate=”true” autodelete=”true”
 deleteKey=”customerId”> 

Behind the scenes, the connector compares the items in the database with what is submitted in the form. That comparison is based on a key that you define with the attribute deleteKey (required). The deleteKey value is normally the name of the primary key in the table that contains the repeat items.

EZ Database Forms from frevvo

Photo by fabio on Unsplash

Leave a Reply