Creating, Updating and Deleting Rows in a Data Source

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

This is supported on our platform by setting up a Form in the way described below.

Creating/Editing Rows in a Data Source

Lets assume you have a data source called “Projects”, and you want to use a Form called “Project Editor” to add/update rows in the Projects data source.

  1. Open the Form designer for your Project Editor form, make sure you have an editable Draft/Test version.
  2. Ensure you have a Choices field on your Form, which is set to use the “Projects” data source.
  3. Tick on the “Create/Update Row” property on that field.
    This instructs the Form to create/edit the selected row from your Choices field.
  4. Add some fields – one for each column that you want to allow to be edited on the Projects data source.
    On each of these fields make sure you set the “Bind to Data Source Column” property to assign that field to be bound to the specified column.
    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.
  5. To enable the creation of a new row in the data source, you must have a “dummy” row in your data source which has it’s first column value set to 0.
    This way the user can select this row from the list to add a new project.
    The app is also 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.
  6. We recommend you also set the displayable title column of this dummy row to be something like “Add new project” or something like this.
    This will make the purpose of the dummy row clear to the user.
  7. 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 Projects data source will be automatically updated with the new row/edited row, both on device and then on the central data source on the web platform, once the form has been completed and uploaded.

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.

Otherwise you can stop reading right here – you’re all done 🙂

Deleting Rows from a Data Source

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 that is linked to your target Data Source.
    e.g. if following the example above, this would be your “Projects” Data Source.
  2. Tick the “Delete Row” property on your Choices 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 unique identifier.
  3. You don’t need any other fields besides the Choices field above to perform the row delete operation.
    However you may want to add some readonly 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.

Hosted GET Considerations When Creating New Rows

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

1. A new 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 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 NewRows and DeletedRows 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 next app sync from the user’s device (identified by their device id), your Hosted GET should then send a DeletedRows value containing the stored Form entry id, along with a NewRows 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.