Tag: database forms

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.

Announcing frevvo 7.2

form-workflowToday we’re happy to announce the general release of frevvo 7.2. Cloud customers were automatically upgraded on April 29. On-Premise availability is coming up shortly – subscribe to Release Announcements for the precise date. This release also includes upgrades to the Database Connector.

We know that frevvo is powerful software but there’s also a learning curve. frevvo 7.2 is the first in a series of updates this year focused on making it easier to use.

Visual Rule Builder

VRBTopDiagram3

One of the main areas customers tell us about is Rules. They’re really really powerful and many customers love the flexibility of being able to write JavaScript code and create very complex, dynamic behavior. But, they can be hard to use. Enter the Visual Rule Builder. Using it, you can create many rules visually rather than writing JavaScript. Read more in this blog article and find detailed documentation here. We’re excited about this new capability and expect improvements over multiple releases to make it easier and increasingly capable of replacing JavaScript-based rules.

This release also includes some security enhancements – e.g. user ids no longer appear in URLs and are replaced by opaque UUIDs – and other under-the-hood improvements needed for future ease-of-use improvements.

Database Connector

The DB Connector is one of the most widely used connectors – customers have created all kinds of integrations with their SQL databases. Most of the improvements made in this release are fairly technical and we won’t go into details here. See the Release Notes for reasons to upgrade. We’ve also made several under-the-hood improvements so we can make this oft-used connector easier to use and as point-and-click as possible. We’ll detail those in future blog posts.

We’re excited about this release and several upcoming ones. The focus is squarely on updating and making so many of the good things we do easier to use.

 

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.