frevvo + SQL: Part 1. Dynamic database forms
August 31, 2016 Leave a comment
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.
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.
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. See: Part 2: Dynamic Pick Lists, Part 3: Master-Detail Views, Part 4: Multiple Results, and Part 5: Creating/Updating Records.