frevvo + box: Automatically save documents to box

In recent articles, we talked about how you can easily connect your frevvo forms and workflows to Google Drive and use it as a low-cost ECM system. While frevvo also works with more traditional ECM systems, we’ve noticed that some of our customers prefer to use box.com for their enterprise grade data and information security (HIPAA, PCI compliance etc.). Naturally they want to save documents (Student Registrations, Purchase Orders, W-4, Patient Referrals etc.) generated by automated frevvo forms and workflows to their secure box storage. We’re getting ready to release a box connector in V7.1 to enable this.

The steps are straightforward. Obviously, you must have a box account and will need the connector installed and configured (it’s already done for you if you’re using frevvo Cloud). Once that’s all setup:

box-1

Connect a frevvo form or workflow to your box account

  1. Login to your box account and create a folder for this workflow.
  2. Create your form or flow as usual. Generate a frevvo-PDF, map to your custom PDFs, upload attachments etc. Then, open the box wizard.
  3. Enter your box account.
  4. You’ll be redirected to login to box if necessary.
  5. Select the folder you just created in Step 1, setup a submission folder name (typically, this is set dynamically using form fields) and select the documents you want to upload (Snapshot PDF, XML data, uploaded files and generated PDFs).
  6. Use the form/flow normally, fill in data, sign the form etc. and submit it.
box-2

Files for each workflow submission are uploaded automatically in the proper subfolder

Now, login to your box account and navigate to the folder you created earlier in Step 1 above. See that it now contains a subfolder. The subfolder is dynamically named based on form data e.g. FirstName Initial_LastName. Every time a workflow is submitted a new subfolder will be created in this parent folder.

box-8-w-4

Generated W-4

Inside the subfolder, you’ll see the uploaded files e.g. the frevvo generated PDF and a Federal W-4. You can click on the PDF to preview it directly in the box UI.

That’s all there is to it. Interested in learning more? Visit our website to view examples, watch videos, sign up for a free 30-day trial or contact us anytime for more details.

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.

[eBook] -05- ways you can use frevvo + Google Apps today

frevvo-google-apps-2

Download e-Book

In previous articles, we’ve described the many ways you can combine frevvo + Google Apps to solve real business problems, improve productivity and efficiency and allow employees to focus on customers, students and patients instead of being bogged down in paperwork.

Download this new e-Book that describes “05 ways you can benefit from combining frevvo with Google Apps in your organization TODAY”.

From dynamic pick lists to using Google Drive as a content repository to approval workflows, the combination just makes completing routine, day-to-day business tasks easier and faster.

It’s 2016! There’s simply no excuse for outdated, manual systems that cause delays, result in unhappy employees and wasted time and money.

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

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.

frevvo + SQL: Part 1. Dynamic database forms

Practically every organization has at least one SQL database and often many of them. And, many 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. Ordering Managers don’t have to look up part numbers or type them in manually if the order form is integrated with SQL. HR applications can access employee data and so on.

frevvo provides a Database Connector that allows you to perform sophisticated integration with your database. In this multipart series of articles, we’ll explore several possibilities in detail from dynamic dependent pick lists, master-detail views, creating and updating records in SQL and running stored procedures.

DBConnectorFirst 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.

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.

You can check out a couple of examples here on our website. We’ll get to HOW they work in the next several parts of this series or there’s a link to the Database Connector Tutorial on the above page.

Contact us for more information if you’d like to learn more or register for an upcoming webinar.