Displaying & Updating Data Rows From/To An External System

Step 1: Define and Connect a Data Source

To display and manipulate data rows from an external system, you will first need to define a Data Source which models the particular data entity in question.

Effectively a Data Source is just rows and columns of data.

The rows and columns will need to be connected or otherwise pushed into our system for use in your apps.

You can get the required rows into our system by one of the following means:

• Manually import the rows from an Excel or CSV file.
  This is not recommended if the data is going to change regularly since you'll have to keep manually importing to keep the data up to date in your apps.
  
  
• Use a built-in Connector.
  We provide various Connectors for Data Sources that allow you to pull data rows in your Data Source with an ongoing, automatic refresh.
  There is also a special Hosted GET connector which allows you to expose a REST web service that returns rows directly from your external system to the app, using our required documented Hosted GET response format.
  
  
• Push data into your Data Source via our API.
  We provide a Data Source endpoint on our REST API that allows you to programmatically push rows into your Data Source on a regular basis.

Step 2: Design a Form which allows create/update of rows

Once you have a Data Source in place, create a Form screen with a Choices or Data Source field that is linked to your target Data Source.

You can then use filters and user interaction to select a row in the given Choice/Data Source field type.

Once a row is selected, you can populate column values into other Form fields using the "Bind to Data Source" field property or via the Dynamic Value property.

We also provide the ability to reflect changes to rows on the app and against server-side Data Sources, provided the server-side Data Source does not have a Connector on it.  All connected Data Sources (i.e. those that have a Connector) are treated as being "one way" - i.e. the external data provider is the "source of truth" and we do not push any changes back to that outside provider.

See our detailed recipe on this approach.

Step 3: Push row changes back to your external system

There are three ways to effect an update of rows to an external system:

• Use a "row-based" Connector on your Form
  We provide several Form Connectors that can create/update rows directly in an external source.
  e.g. our SharePoint List and Google Sheet connectors
  NOTE: We don't currently support updating of rows via our Google Sheets connector due to a lack of high-performance options for this on Google's Sheets API.
  
  
• Use a REST Connector on your Form
  The recommended approach is to have a REST connector on your Form, which will push changes to a web service/API on your side.
  You can then perform the necessary data manipulations and reflect these changes on the results you return on any connected Hosted GET data sources.
  
  

  If you don't have access to software developers to build a custom web service, then maybe try using automation tools like Zapier to create such a service.

  Note that these services are great for small numbers of monthly transactions, but can cost hundreds of dollars monthly if you're going to be doing thousands of transactions per month.

  
  

  The other non-programming option is to use our Sync product, which basically provides an "integration in a box".

  You would install Sync on your own web server, along with a local SQL Server database to hold the data.  
  Note that SQL Server Express - which is the free version of SQL Server - works fine with Sync.

  After installing, Sync will plug into our APIs and it will be ready to receive incoming REST connector Form data, automatically creating and updating rows in your SQL Server database.

  Get a free 30 day trial of Sync by using the TRY button against this option in the Billing area.

• Use our API to retrieve Form entry results
  You can also poll our API for new Form entries and apply the data row changes as needed on your side based on the Form entry answer values.
  This is a slower approach that we don't recommend. It also doesn't scale well, since you run the risk of exhausting your daily API usage limits.
  
  

Despite which approach you choose, we still recommend that you use the "Create/update" options on a Choices/Data Source field in a Form, as outlined in our Form Recipe.

The reason for this is that while there will be no Data Source changes made on the server side (see our "one-way" rule above), the changes will still be reflected against the app's local cached Data Source rows.

This way, the app will display a consistent view of any Data Source row changes to the user, at least until the next time the app syncs with your Data Source. When the next sync occurs, we assume that your system will reflect the results of any submitted Form entries, as those apply to your row sets.