Case Study: frevvo + Confluence. Integrated, Cost-effective, & Feature-rich.

This large international provider of sports betting and casino games in Europe has over 6,000 employees with branches in several countries.

international-sports.jpg

The company uses frevvo with Confluence on daily basis for a multitude of tasks from employee on-boarding to budget management to online ordering. frevvo’s Database Connector allows them to surface frevvo forms and workflows in Confluence that read/write data from their SQL database and easily create dynamic content.

frevvo integrates with our Confluence system as well as other systems without complex APIs. And the best part of all is that frevvo is very cost-effective and feature-rich compared to products that are many times the price.

Gary J.
Service Owner

The reduction of development time to build these business-critical applications has allowed them to focus on other priorities. Business users also appreciate the fact that information is easily available and they have more time to spend with customers.

Read the Case Study and learn more by visiting our website. Explore some of the examples, read customer success stories and contact us for more details.

Aero Communications replaces paper, Excel and InfoPath with automated workflows integrated with SQL

Aero Communications inc. provides customer-focused technology solutions to broadband and wireless providers, businesses, public venues, government facilities, and residential subscribers.aero-communications

The company was using a number of paper, Excel & InfoPath forms for everyday business processes such as Vehicle Inspections, Facilities Maintenance, Financial Forms and Warehouse Orders. Many of them require approvals forcing employees to email them around and print/sign/scan for approval. In addition, the existing forms could not pull data from SQL databases resulting in excessive and duplicate data entry, errors, corrections and inefficiencies.

“We were looking for a single, affordable solution that could address all our requirements.

frevvo’s modern, online system is easy-to-use, and meets our needs at a reasonable cost. Live Forms is the best web-based forms & workflow solution on the market today for our needs.”

–Adam Wiener, Applications Specialist

Aero Communications considered a dozen+ solutions before selecting frevvo Live Forms because it provided an easy-to-use solution that works with their database, and integrates with Active Directory at an affordable cost.

They have already replaced multiple forms and workflows with fully automated online versions. Read the Case Study and learn more by visiting our website. Explore some of the examples, read customer success stories and contact us for more details.

[eBook & Video] frevvo +SQL: dynamic forms & workflows

db-video

Dynamic database forms and workflows with frevvo + SQL (3m video)

Day-to-day business activities are simply more useful if they can access information from SQL databases on-demand. If Customer Service has visibility into order history from the database, they can better help the customer. HR applications can access employee data and so on.

Download frevvo + SQL eBook or Watch 3m video

frevvo provides a Database Connector that allows you to perform sophisticated integration with your database. From dynamic dependent pick lists to master-detail views to creating and updating records in SQL and running stored procedures, you do it all with frevvo.

Learn more by downloading our new eBook or by watching the video at the links above and visit our website to view customer success stories and signup for a free trial.

frevvo + SQL: Part 5. Create/Update records

DBConnectorPrevious articles in this series (Create dynamic pick lists initialized from SQLMaster-detail views from SQL and Handling multiple results from a Query) have focused on reading information from the database and dynamically initializing fields on your form.

But, many business problems require creating new records or updating existing records in the database. Today, 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 article so we’ve only touched upon the highlights here. You can explore in detail by reading the DB Connector Tutorial in the documentation.

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 very similar to the previous articles:

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.

dbconnector-post

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.

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.

frevvo + SQL: Part 4. Multiple Results

db-multiple-results

Multiple Rows 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 and Part 3 explored how to create master-detail views from SQL.

Today, 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.

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=”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: 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. Try it now in your browser. You’ll get back an XML document with order details.

Now, we need to connect this to the form. As before, 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, 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.

dbconnector-multiple-items

Automatically populate multiple rows in a form Table from a SQL query

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.

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.

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.