When using data sources, there are efficient and inefficient ways of structuring, binding, and storing your data. However, we'll focus on efficient usage by establishing a few basics, how to structure your data, and best practices for specific scenarios that might get a bit CRUDdy—aimed at demystifying data source configurations and your data within.
With that out the way, you're up to speed on what this article is about. We'll also assume you're ready to go with another tab open on the platform's data source areas to cross-check content coming your way.
Starting with a few basics ensures you're familiar with data sources unless you tactfully skip ahead. Either way, you're bound to find useful information anywhere you land.
TABLE OF CONTENTS
What is a Data Source?
A data source is a tabular storage of columns containing row data that can be created for use within your digital solutions. Data sources can be populated manually, imported from a .xlsx or CSV file, or pulled from external systems using Data Source Connectors.
Once a data source is created, but not necessarily populated but at least contains two columns and a single row with data. Solutions can then be created to add, read and update rows or delete.
There are four types of data sources on our platform that can be used in various ways, but we'll be focusing on User Defined data sources created by you.
User Defined
A default data source created by a user on the platform. Populated manually or by importing a .xlsx or CSV file.
Connected
A user defined data source connected to an external system. Automatically pulling data from i.e. Google Sheets, OneDrive, AWS, FTP, or a REST API endpoint by configuring a Data Source Connector.
System
A system data source for i.e. active users or published docs.
System maintained and uneditable.
App
A system data source that stores user or device specific data (in-app) i.e. incomplete tasks, entry drafts and history.
System maintained and uneditable.
Data Source Basics
Take a moment to brush up on a few basics before getting practical.
Default Requirements
Requires at least two columns and 1 row. The 1st column's row (Column 0) must have a value to save successfully.
Unique Column (Column 0) / Unique Identifier
The 1st column (Column 0) is normally the unique identifying column that can identify rows in the platform for referencing/updating/deleting and, as such, needs unique values per row.
Editing Data Source Row
If you do not have a unique value for each row in column 0 (the first column in the data source), then you risk generating duplicate values when you export your data source.
How our platform handles a "0" (zero) value in Column 0 / Row 1
The platform doesn't overwrite this row if selected but adds a new one. It is a "dummy" row where the second column (Column 1) of a data source could contain custom text data i.e. "Add New Row". This text can then display in-app for the user to select instead of leaving a Choices field blank ,for example, when adding new rows.
Creating, Updating and Deleting Rows in a Data Source
Data Normalization
Before looking into best practices, it's ideal that your tabular data is structured in normal forms to prevent data anomalies when your screens interact with data sources or external system data.
Poorly structured data in forms weigh equally with poor form design regarding overall solution performance and maintenance. Some might say it's inversely proportional to headaches.
If you're familiar with headaches and data normalization, then the following best practices based on normal-form structured data await you.
In a nutshell, a data source's 1st column (index 0) ideally needs to contain unique row values (unique identifier - no duplicates) with additional columns for single-valued data.
Best Practices
Now that you're familiar with data sources, some basics, and recommended structure, let's get practical.
If you process concepts more efficiently with a supporting example app, then head over to Design Tools > Form Templates, search, and install the Inventory App. Providing prebuilt functionality to guide you.
Whether you're using a Choices or Data field within your form design to link a data source and use its data, creating, reading, and updating rows, or deleting a row, applies to both.
But, before linking or binding fields to a data source, ensure you save any changes made to the data source to reflect in the form designer. This might require you to save your form's design, refreshing the page for new data sources to be visible and linkable. Including any row data and column header changes for field binding.
Linking
After adding a Choices or Data field to your form's design. Configure the respective Answer/Data properties of the field to Data Source/Existing Sources. Then choose the desired data source linking it to the field.
(Choices field properties)
Binding
Once linked, optional filtering and modification types can be set depending on requirements. Other fields can now be bound to the data source columns, automatically displaying data for file outputs, updating rows, or creating new ones.
Binding is essential for fields to modify data sources but not for reading.
(Under Advanced Options on most field types, text, numeric, date/time, location, etc.)
The property's three drop-down options are:
- Data Source - The data source's name that's linked to a form field.
- Data Name - The data name of the form field linking the data source. A single data source can be linked to multiple fields in a form. This allows for the desired linked field to be selected that might have different filtering or modification settings to other similarly linked fields.
- Column - The data source column that dynamically drives the field's value depending on field selection or filtering.
Reading
By default, any screens or fields linked or bound to a data source and its columns will read the device's locally stored copy (sync updated) and display as configured.
For example, linking a Choices field to a data source to display a list of item names in-app for selection.
Subsequently, binding a Text field to a linked data source column. When a Choices field selection or filtering is applied to a Data field, the Text field will display that row's column data.
Alternatively, fields can display data without binding to a data source column. By referencing the linked field's data name and data source column index in a field's Dynamic Value property. The field will display data as if it were bound.
Example formula
{{itemInventory[5]}}
Linked Choices or Data field's data name, linked to a data source.
Data Source column index, starting from 0.
Creating
Any fields bound to a data source column either display data or are blank, depending on how the linked fields are configured. Usually, when blank, the user must capture data, and on submission, a new row is created in the data source.
For this to occur, the Choices or Data field linked to the data source must have the Create/Update Row option ticked. And additional fields bound to the linked data source's columns to capture new row data.
(Data field properties)
If a unique value for column 0 isn't specified, the platform automatically generates a GUID value, not leaving the cell blank.
Updating
Similar to creating, but a specific row has been identified via a Choices field selection or user-captured data filtering a Data field. Fields bound to data source columns displaying editable row data in-app, can update the data source.
If the Choices or Data field's property Create/Update Row is ticked, any in-app data changes will update the data source on form submission. Other users with apps using the same data source will see new data after successfully syncing their devices.
Deleting
Ideally, a separate form for deleting a data source row should be created. Similar to updating where a specific row is identified but, in this case, for deleting. By ticking the Delete Row option on a Choices or Data field linked to a data source.
And binding a field to the initial or unique identifying row's column telling the platform which row to delete. This field can be a Hidden field but is required, other than the Choices field selection or Data field filtering by user-captured data.
We hope this helps your single-row modification needs. However, if you're looking to create or update multiple rows per submission, repeatable Pages or Table fields might interest you, or Gallery fields if it's media you're after.