The following shows an example of how to create an application that lets the end-users view, add, and delete records from a table in a base. The end-users can edit the records collaboratively so that when one end-user deletes a record, another end-user immediately sees the change after refreshing their browser.
The example procedure consists of these main steps:
On an empty sheet, in cell B2, enter a base name.
Create a base using the address of the base-name cell as the argument:=?BASE_ADD(B2)
In cell B6, enter a table name.
Add the following table definition into the cell range B7:C9:
A B C 1 first_name string 2 last_name string 3 age number 4
In cell A1, use the following formula to create a table.=?TABLE_SPEC(B2,B6,B7:C9)
In the range B13:D14, enter data to add to the table:
A B C 1 first_name last_name age 2 John Doe 25 3
In cell B15, create a frame using the range with data as the argument:=FRAME(B13:D14)
In cell B16, use the following formula to add records to the table:=?ADD_RECORDS(B2,B6,B15)
In cell D16, use the following formula to display all records.=?GET_RECORDS(B2,B6)
Add to the page three Text components to display records and an Icon component to delete a record.
Change the default icon to a trash-bin icon.
Lay the components out in a row and group them.
Rename the box to "Record".
Adjust the layout as shown in the video above.
To delete a record from a table, you need to execute a sequence of operations using the DO() function:
Follow these steps to create your logic for deleting a person record from the base:
- In cell B20, use the following formula to return User Interaction event information in the form of a frame.=?E()
- In cell B21, use the following formula to get the index of the repeated Record box whose child trash-bin icon is clicked.=?GRAB(B20,"$..repeated_item_index")
- In cell B22, use the below formula to get the record at the clicked index.=?ROW_AT(D16,B21)
- In cell B23, use the below formula to retrieve the _id value of the record:=?GRAB(B22,"$.._id")
- In cell B24, use the below formula to delete the record from the table using the retrieved record ID.=?DELETE_RECORDS_BY_ID(B2,B6,B23)
- In cell B25, use the DO() function to re-evaluate the cell that returns all records from the table.=?DO(D16)
- In cell B20, use the following formula to return User Interaction event information in the form of a frame.
In cell B19, use the DO() function that references the range defined in step 1.=?DO(B20:B25)
Select the Record box, and then, in the Box properties panel, click on Repeater and bind the Based On Spreadsheet property it to the topmost cell of the id column of the frame that is returned by cell D16. Set vertical direction for auto-propagate.
Bind the text value properties of each Text component in the Record box to the respective columns of the frame that is returned by cell D16.
Bind the onClick event of the trash-bin icon to cell B19. While binding, make sure that the cell value is fixed in both directions so that when the end-user clicks on any trash-bin icon in the repeated group, the same cell (B19) gets executed, deleting the respective record from the base table.
Click on the Preview button to enable the preview mode and make sure that records are added and deleted on the page.
To add a vertical scrollbar to the displayed records:
Right-click the Record box, and then select Group. A new box component appears in the outline as a parent of the Record box.
Select the parent box component, and then, above Overflow-Y, click on the Auto button
To add a title to the UI section, add a Text component to the page and, in the Text value property field, type "Employees".
To add the New button:
- Add a Button component to the page.
- Set the button's Caption property to New.
- Change the background and font color, as shown in the video above.
Add a new page and rename it to "Add Person Dialog", as shown in the below video:
This page will be used as an overlay on "Page 1"
Set up the Add Person Dialog page, as shown in the video below or described in the procedure under the video.
- In the Add Person Dialog properties panel, select Close overlay on ESC when open and select Closes the overlay, deselect Sends event to pages below
- Set the background color to black and reduce the Opacity to 0.4
Add a group of TextInput components, as shown in the video below or described in the procedure under the video.
- Add to the Add Person Dialog page three TextInput components for first_name, last_name, and age.
- Group the TextInput components.
Set placeholders, add the Add button, and create bindings, as shown in the video below or described in the procedure under the video.
- Change the Placeholder values of the TextInput components to "First name", "Last name", "Age".
- Add to the page a Button component, change its caption and background, as shown in the video above.
- Bind the onClick event of the Add button to cell B16 (the cell that contains the ADD_RECORDS function).
- Bind the TextInput components for "First name", "Last name", and "Age" to cells B14, C14, and D14.
- Test the functionality by adding a record and making sure it is displayed, as shown in the video above.
Configure the New button to display the Add Person Dialog page as an overlay, as shown in this video or described in the procedure under the video.
To configure the New button to show the Add Person Dialog page as an overlay:
In cell B28, use the following formula to show the Add Person Dialog page as an overlay.=?SHOW_OVERLAY("Add Person Dialog")
Bind the onClick event of the New button to cell B28.
Click the Preview button and make sure it works as expected.
Adjust the position of the overlayed dialog. Make sure the Add button is grouped into the box.
Let's enhance the functionality of the Add button so that when the end-user clicks, it the following operations occur:
- The record is added to the table.
- The list of records is refreshed.
- The Add Person Dialog page gets hidden.
To implement the above-mentioned enhancements, follow the steps in the video below or the procedure provided under the video.
- In cell D20, use the following formula to add the record to the table.=DO(B16)
- In cell D21, use the following formula to get the list of records in the table.=?DO(D16)
- In cell D22, use the following formula to hide all overlays:=?HIDE_ALL_OVERLAYS()
- In cell D19, use the following formula to execute the cell range D20:D22:=DO(D20:D22)
Add highlight to the records in the UI when they are hovered over, as shown in the video below or described in the procedure under the video.
- Select the Record box, and then, in the Box properties panel, select Hover.
- Enable Hover Styles.
- In the Background area, above Color, select a highlight color you want to use to highlight a record when it is hovered over.
- Make sure the highlight on hover works as expected.