Using Sheet Operation functions

Studio provides us ways and means to build different forms with input controls to add and update data from sheets. However, the limitation lies in providing a means to view the entire data at a time. For this, you do have the Table grid controls which enable you to present data from the Sheets in a tabular and easily understandable format. We have also seen how we can add different functions to the table grid control using the custom actions.

Now consider a scenario where you are able to view the data in the table grid and feel the need to update, delete, or insert records. You can use the workflow Update Sheet task to carry out these operations. However, you would have to wait for the workflow to be completed to see your updates being reflected in the sheets. Instead, the Sheet operation actions or functions carry out the same sheet actions but can give you a completion confirmation immediately. In such scenarios where you simply want to perform the sheet update actions, these functions are preferable over the workflows. The functions can be used with table grid control as well as with any other action button as well.

Functions

  1. INSERTRECORD - This function is used to Insert record in a sheet. It creates a new row to the sheet and inserts the values into the respective columns.

    SYNTAX
    INSERTRECORD([sheetname.columnname1 = value1, sheetname.columnname2 = value2, …])

  2. UPDATERECORD - This function is used to Update data of all rows in a sheet based on filters. The filters are mandatory as they define the column for which the values are to be updated.

    SYNTAX - UPDATERECORD([sheetname.columnname1 = value1, sheetname.columnname2 = value2, …], filters)

  3. DELETERECORD - This function is used to Delete all the rows which match the filter.

    SYNTAX - DELETERECORD ([sheet name], filters)

Using the Filter property: The filter property works similar to the LOOKUP function.

  1. For a single filter you can directly specify the filter as SHEETNAME.COLUMNNAME = VALUE;

  2. For multiple filters you can use operators such as AND & OR.

Using the Functions

Now let us take an example here to understand how the functions can be used. Let us add a screen to update data from the Prod_docTest sheet that has been already created to store product information.

Add a text control for the product name and numeric control for the quantity column. Now let us see how the functions can be used with the Table grid control. So add the table grid control bound to the Prod_doctest sheet.

Now let us add the functions to the respective action buttons first.

  • So now under TableGrid > Control Properties you need to select the Click Event as Selection and Selection Type as Single Select.
  • To show the three actions added to the table grid you need to enable the buttons. So under Table Grid > Display let us select Display Add, Display Update and Display Delete.

Now go to the Table Grid > Actions tab. Here we would add the click events for add_click, update_click, and delete_click.

  • Add Record: add_click event
    For the sake of this example, let us add the product name and quantity record to the sheet. You would add the data using the input controls. The record would be added when you click the ( + ) Add button on the table grid. So now we need to define the formula under the Add record add_click > Step 1 > Custom Action. Click Edit and add the function as follows:

Click Save. So here the data from the two controls would be added to the sheet with the Insert record function.

  • Update Record: Now let us understand how to use the update record function. In this example let us update the quantity for the product name entered. It is important to provide the filter column as all records matching the condition would be updated.
    So in this example, you need to select the row from the table grid for which the quantity is to be updated.
    For the sake of understanding let us add that record in the Product_name text control using tablegrid10.prod_name (tablegridname.columnname) to understand the value selected. You would first select the record and then click the Update option from the menu on the table grid. So now we need to define the formula under the update_click > Step 1 > Custom Action as shown below and click Save.
    update record

So here we added the first parameter as the column for the quantity which is the updated value and the filter used is the prod_name column to which we are passing the product name from the text control.

  • Delete Record: Now let us understand how to use the delete function. Here it is a simple input that we provide. The parameters are the sheet name from which the record is to be deleted and the filter which specifies the record/s to be deleted. So in this example, you need to select the row from the table grid to delete. You would first select the record and then click the Delete option from the menu on the table grid. So now we need to define the formula under the delete_click > Step 1 > Custom Action as shown below and click Save.

Now in this example, we have added the three action buttons as well to the form. It is important to understand that the same formulas would be used for each of the actions specified with the buttons.

The only change is that you would add the formula under respective button’s Actions > button_click > Step-1 > Custom Action > Edit.

Now when you run the form you can see the results as follows:

1. Add record:

Add the product name and quantity and click the (+) Add button on the table grid. The record is now added to the sheet. You should refresh the table grid to view the record added now.

2. Updaterecord:

Select the product name from the table grid. It can be seen in the Product name text control. Enter the quantity. Click the Update option from the table grid menu. The record is now updated to the sheet. You should refresh the table grid to view the record updated now.

3. Delete record:

Select the product name from the table grid. Click the Delete option from the table grid menu. The record is now deleted from the sheet. You should refresh the table grid to view the updated sheet now.

Thus the functions to update records enable you to update the sheet without having to use the Workflow > Update sheet task thus enabling you to provide improved functionality to the table grid control as well as other action button for a quick response of the action completion into the sheet.