Update sheet task

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.

  • The insert record task allows you to add new records to the sheet. It creates a new entry in the selected sheet.
  • The update record task 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.
  • The delete record task allows you to delete record from the sheet based on the filter value to find the specific record

Let us now understand how we can add various tasks using workflows:

Adding or Inserting new record

Let’s take a use case for updating the Product details. You need to create a form to enter details for the columns in the sheet namely the ProductData Sheet. Add a Text control for the Product name, TextArea control for a Detailed description, Numeric control for the Quantity and as the data is to be submitted to your ProductData Sheet you need to add a Submit button control and your form design is ready.

Once done we would now add a workflow 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 as AddProductInfo and click Next.

Now select the sheet to which the information is to be updated. From the Where to update > dropdown list, select ProductData sheet. 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, click Select Workflow and Select AddProductInfo workflow that we created in the previous step. Set the properties on the form for the submission confirmation and so on.

After you submit the record successfully, you can view the data in the sheet.

Updating records

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 we would have to update based on certain criteria. A commonly used criteria is to use the unique id. As the name suggests, being a unique record, you can easily set the filter using the ID and make sure that the specific record is updated. However, it is not limited to using the Unique ID, you can make use of any column as per your use case.

Let us take the same sheet as above for the Product data. Now create a form Prod_data_grid and add a Table grid linking all the columns from the sheet. Further, if you click on any of the records, you need to navigate to another form to update the record. So set the Control properties for the Table grid control as Navigate (without validation > Navigate to the Updateproddata form.

Now create a new form UpdateProddata and add text control for unique id and other controls as Text control for the Product name, TextArea control for a Detailed description, Numeric control for the Quantity. Add a Submit button and label it as Update record. UniqueId text control will be a hidden control. Now that you want to get the same record that you selected from the Prod_data_grid form, for all the forms use the Unique name of the table grid and get the column name. So in our example here where the Unique name is tablegrid5, you would assign as tablegrid5.unique_id, tablegrid5.prod_name and so on.

You need to add a different task to update the record which would be linked to this Submit button. Now create a new workflow UpdateRecord to enable updation of the selected records.

Select the Update Sheet task from the tasks list and click Continue. Add Task name as Updatetask 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’. Now add a filter to fetch the record that is to be updated based on UniqueID. 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 need to update. Depending upon the type of column you use for the filter, you can select the operators accordingly. You can assign multiple filter criteria using AND or OR operators depending upon your use case. 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.

You will also need to link the specific control from your form to the columns of your sheet.

In case there are no matching records in the sheet you can also assign to Insert new record wherein you would bind the columns to the control and the insertion of data is done accordingly.

After all columns are linked click Done to save.

Once done link the workflow to the Submit button.

If you run the form now starting with selecting the record from the table grid, you will note that you can now update the record on Update Product form. When you click the Update Record button, the Update Task is triggered and the record updated to the respective sheet.

Deleting records

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. As the name suggests, being a unique record, you can easily set the filter using the ID and make sure that the specific record is updated. However, it is not limited to using the Unique ID, you can make use of any column as per your use case.

Let us take the same sheet as above for the Product data. We will use the same UpdateProddata form from Updating records for this example. Just add another button for Delete Records. Add a Popup screen named Deletepopup which will have a Text control for the UniqueId, a label for the ‘Are you sure you want to delete?’ message, a Submit button for the Delete action and an action button for Cancel. When you click the Delete button, your Delete task would be triggered. When you click the Cancel button you would be navigated to the UpdateProddata form again.

delete record

You need to add a different task to delete the record which would be linked to the Delete button. Now create a new workflow Deleteprod_data to enable the deletion of the selected record.

Select the Update Sheet task from the tasks list and click Continue. Add Task name as Deletetask 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 ‘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 Delete button. You can now run the form and view the results.

If you run the form now starting with selecting the record from the table grid, you will note that you can now view the record on Update Product form. When you click the Delete Record button, you would be navigated to the Popup screen named Deletepopup. If you click Delete, the record is deleted from the sheet and a confirmation message is shown.

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.