Creating, Updating and Deleting Rows in a Data Source

Often you may want to allow the user to create, update or even delete rows on your Data Sources.

This is supported on our platform by setting up a Form in a few ways.

Note, the following functionality only works with existing data sources, hence you need to create the data source first before creating any data entry forms to create, update or delete rows from a data source.

This is key as Choices fields or Hidden Data Source fields need to be linked to the existing data source and additional fields could be required or optionally bound to columns in the data source, to display a row's column data.

• Creating or Updating a Row (New Screen)
• Creating or Updating a Row (Existing Screen)
• Deleting a Row
• Creating or Updating Multiple Rows
• Tips & Tricks

Creating or Updating a Row (New Screen)

When you're looking to create a new Data Entry Form that can create or update a row in a data source, the simplest way to achieve this is to link a Data Source to the form before creating the form, before clicking "Create".

Screens Page: Create New Screen

What this does is, when the form is created and the page is refreshed on the Form Designer page, the form will be prepopulated with a Choices field that is linked to the data source with its "Create/Update Row" property ticked, allowing the creation and updating of bound fields.

Form Designer: Choices field linked to Data Source and configured to Create/Update Row

And, all columns from the Data Source will be added as fields, titled accordingly to column headers from the Data Source and bound respectively.

This binding means that the selected row's value for the given column will be set into the field, and also any edits in the field will be written back to that column.

Form Designer: Text field bound to Data Source column

Note, prepopulated fields will only be of Text/Numeric type, if your data source contains Date/Time, Location, Image data etc then you'll need to change these field types in the Form Designer and bind them to their respective Data Source column.

In-app, a user will now be able to select a row from the Choices field and update all fields except the Data Source's initial/unique ID column (column 0).

If the Choices field is left blank, a new row will be created and the platform will generate a unique ID for the data source's initial column

However, if you're looking to create a new row and enter your own row ID into the data source's initial column and not use the platform's generated ID.

Then we suggest adding another field that is bound to the initial column to only display when the Choices field is blank.

Note, this field's data will need to be unique and not match any existing column data as the platform will only identify rows with non-duplicate values within the unique column/column 0.

Most importantly, this method demonstrates the required steps for linking a data source to a form and binding fields to data source columns for row creating/updating/deleting.

Basically:

1. Have a Choices or Hidden Data Source field in your form
2. Link a Data Source to the Choices or Hidden Data Source field
3. Make sure the value column of the linking field (Choices/Hidden Data Source) is set to the Data Source's unique column
4. Tick the desired property - Create/Update Row OR Delete
5. If Creating/Updating, add additional fields and "Bind to Data Source Column" to display data, respectively
6. If Deleting, add at least one additional field and bind it to the unique column of the Data Source. Additional fields can be added and bound accordingly to display data aiding the user in confirming the correct row is being deleted.

Creating or Updating a Row (Existing Screen)

Using a Choices field (allows for initial row selection)

• Add a Choices field
• Link to Data Source
• Tick "Update/Create Row" property
• Add additional fields and set the "Bind To Data Source Column" property accordingly to update column data

Using a Hidden Data Source (doesn't allow for initial row selection hence requires filtering for row selection)

• Add a Hidden Data Source field
• Link to Data Source
• Tick "Update/Create Row" property
• Add a field that will allow the user to enter in a value to match the data source's initial column or select from a list (via Choices) to filter the Hidden Data Source
• Add criteria or formula to the Hidden Data Source allowing the Hidden Data Source to filter the row based on the user's entered/selected value (This is what selects the row to be updated)
• Add additional fields and set the "Bind To Data Source Column" property accordingly to update column data

Deleting a Row

For deletion cases, you must build a separate Form that will perform row deletes - i.e. you might have a Form already that does add/edit, but to do deletes you create a separate Form design.

This is an intentional platform approach since it means the deletion operation is always silo'd to a dedicated Form design, which in turn avoids situations where a user might delete a row when they thought they were inserting/updating it.

Once you have created your dedicated "Delete" Form:

1. Add a Choices field
2. Link the Choices field to your target Data Source.
   
3. Tick the "Delete Row" property on your Choices field or a Hidden Data Source field.
   
   This instructs the Form to delete the selected row found in your Choices field when the user uploads the Form entry.
   Deletion is performed based on the value in the first column of the row - remember our system always treats the first column as the row's unique identifier.
   
   
   
   
   
4. You then need to have at least one field that is bound to a column (i.e Column 0) in the data source. This is by design in order for the form screen to validate exactly which data source row should be deleted when a form entry gets uploaded.
   
   You may want to add some read-only fields which use the "Bind to Data Source Column" option to display additional column information to the user, allowing them to confirm the correct row is selected for deletion.
   
   
   

Creating or Updating Multiple Rows

If your Create/Update form has a repeatable page similarly set up as the example from the above "Creating or Updating a Row (New Screen)" section, then any row selection for each repeat from the Choices field will be updated or if not selected will create a new row.

Page Field: Repeatable

1. Add/Select Page field
2. Tick the Page field's "Repeatable" property

Alternatively, a Table field can be used with its "Populate From Data Source" property set, containing fields bound to desired data source columns that will not only display rows from the data source but also create new rows added to the table and update any changes made to existing rows displayed in the app.

Table Field: Populate From Data Source

1. Add/Select Table field
2. Tick "Populate From Data Source" property of Table field
3. Link a Data Source
4. Tick "Create/Update Row"
5. Add fields into the Table field and "Bind to Data Source Column" property accordingly

More information on our Table field type can be found here.

Tips & Tricks

Adding a Dummy Row to Display "Add New Row" Text as a Choices field Option (instead of leaving blank)

In reference to the example provided in "Creating or Updating a Row (New Screen)", and the functionality of leaving the Choices field blank, i.e. no option selected, to create a new row.

If you'd prefer to have the end-user select an option of "Add New Row" for example, this is achievable by creating a dummy row in the Data Source.

Create a "dummy" row in your Data Source by adding a row with the column 0 (initial column) with the value 0 (zero), and in column 1 with the text "Add New Row" for example.

The app is coded to treat 0 value rows as being a new row, and it will auto-generate a 36 character unique key (a GUID) for the row to ensure uniqueness.

This way the user can select this row from the list to add a new row. When a Choices field's display column is set to column 1 displaying "Add New Row" and value column set to column 0 when linked to Data Source. 

Setting the displayable title column of this dummy row will make the purpose of the dummy row clear to the user.

If you want the dummy row to always be visible as an option on the Choices field, then set the Default Value property to 0 and tick on the "Always Show Default Option" property.

Once you have implemented the above, test out your Form to see the results.
Try selecting an existing row and editing it, and also try creating a new row.

The Data Source will be automatically updated with the new row/update row, both on-device and then on the central data source on the web platform, once the form has been completed and uploaded.

Adding a dummy row also becomes handy when you need to conditionally add or update a row based on a condition, you can then use a filter formula that will specify the dummy row for a new row or an existing row to update.

Hosted GET Considerations When Creating New Rows

If the Data Source you are updating is supplied to the app via a Hosted GET, then read on to review the integration considerations involved.

Here's what happens when a new row is created on the app:

1. A new record gets created with the Form Entry Id as the row's identifier in the local app-side Data Source copy.

This is done to give a consistent user experience, in that the user can see the new row they created any Data Source listings immediately after completing their creation Form entry.

2. When a Hosted GET is involved, we expect your system to receive the new Form Entry (e.g. via a REST connector on the Form), process it and check whether the Choices/Data Source field value matches the Form Entry id.

If your service finds this, then your system knows that this entry created a new row and you can process accordingly, creating a matching new row on your system with a new system-assigned id.

3a. If your Hosted GET does a full replace of rows on every call from the app - i.e. using the Rows property on your responses -  then you're done, nothing more to do :)

The next Hosted GET sync will clear all existing app rows (including the one stored locally with the Form Entry id) and replace it with your system's rows (including your new system assigned row).

OTHERWISE

3b. If you are making incremental updates on the Hosted GET - i.e. sending New Rows and Deleted Rows back on your responses - then there is still the matter of removing the local app created new row identified by the Form Entry id, and adding your new system assigned row.

So when receiving the Form Entry on your web service, you will need to track the user's device id and Form Entry Id for use by your Hosted GET service, since you need to replace the app's local new row with your system assigned new row.

On the next app sync from the user's device (identified by their device id), your Hosted GET should then send a Deleted Rows value containing the stored Form entry id, along with a New Rows value containing the new system assigned row values.

This will cause the app to delete the local entry id based row, and add the new system assigned row.