When you create forms, you would need to store or fetch data from the sheet. Studio provides the Update sheet tasks which can be used to define the workflow for inserting, updating and deleting data from the sheets.
Following are the actions that can be performed using the update sheet task
- Insert record which allows you to add new records to the sheet. It creates a new entry in the selected sheet.
- update record which allows you to update or modify the existing records and optionally add a new record in case the record does not exist. You would have to add a filter to update the specific record.
- Delete record which allows you to delete record from the sheet based on the filter value provided to find the specific record
Let us now understand how we can add various tasks using workflows. For all the three actions you would be using the Update Sheet task.
Adding or Inserting new record
You need to add a workflow to your form that would be used to insert data to the sheet. Select the Update Sheet task from the tasks list and click Continue. Add Task name and click Next.
Now select the sheet to which the information is to be updated. From the Where to update > dropdown list, select the sheet name.
As we are adding records, set the ‘What to do’ for the action to be performed on the sheet as ‘Insert Record’.
You will also need to link the specific control from your form to the columns of your sheet. Under Insert Data, type the control name, that is available as suggestions representing the Uniquename for the control.
Optionally you can select it from the Tasks option in the F option. To add a control, Click F and select Tasks > Initiated > Control’s Uniquename.
Once all the columns are bound to the respective controls, click Done and your workflow is ready.
The last thing that remains now is to link the Workflow to the Submit Button. In the properties of the Submit button select the workflow created for inserting records. Now whenever you submit the record successfully, you can view the data in the sheet.
Now that you have seen how to add records, you also would have use cases where you need to update the existing records. It is important to remember that you would have to update records based on certain criteria. A commonly used criteria is to use the unique id.
Select the Update Sheet task from the tasks list and click Continue. Add Task name and click Next.
Select the sheet to which the information is to be updated and set the ‘What to do’ for the action to be performed on the sheet as ‘Update Record’.
After all columns are linked click Done to save. Once done link the workflow to the Submit button.
Now whenever you run the form and initiate the action to update the record the Update Task is triggered and the record updated to the respective sheet.
Now that you have seen how to update records, you also would have use cases where you need to delete the existing records. It is important to remember that you would have to delete based on certain criteria. A commonly used criteria is to use the unique id.
You need to add an Update Sheet task to delete the record which would be linked to the Delete/ Submit button. Select the Update Sheet task from the tasks list and click Continue. Add Task name and click Next.
Select the sheet from which the information is to be deleted and set the ‘What to do’ for the action to be performed on the sheet as ‘Delete Record’. Now add a filter to fetch the record that is to be deleted, based on UniqueID in this case. You can make use of any column as per your business rule you are applying. However, UniqueId would provide the exact record in case it is a single record you are updating. Depending upon the type of column you use for the filter, you can select the operators accordingly.
Now in case there are multiple matches to the filter you can assign it as either All, First, Last or None of the records to be updated.
Once done, link the workflow to the submit button. You can now run the form and view the results. The workflow would be triggered to delete the record based on the value passed using the filter.
We have now seen the different Update tasks for Insert, Update, and Delete and their working. The filter is an important aspect of the update and delete tasks as they would allow you to fetch the specific record and update it accordingly.
You can also have a look at the video and tutorials here to understand how to use the update sheet task for inserting, updating and delete records from a sheet.