Control - Relationship
In the application, each worksheet records data for different business objects, and these business objects often have close relationships. By properly setting up relationships, you can not only reduce data redundancy but also enable quick data querying and display, thereby improving work efficiency.
Take the customer worksheet and the contact worksheet as an example. These two worksheets store detailed information about customer companies and contacts, respectively. In actual business, a customer company usually has multiple contacts, while a contact may be an individual or belong to a company. Without setting up relationships, you may face issues like duplicate data entry, and when viewing related data, the disconnected workflow could cause inconvenience.
By establishing relationships, these problems can be effectively resolved. When creating a contact record, you only need to select and link an existing customer record from the customer worksheet, without re-entering the customer's details. This not only reduces data entry workload but also ensures data accuracy and consistency.
Additionally, setting up relationships allows for quick access to related data. When viewing a contact record, you can easily check the detailed information of the related customer company through the relationship field.
Example
-
Relate multiple records
-
Relate one record
Field Configuration
Use Case: In order to better manage customer information and its related contacts, we need to establish a relationship between the customer worksheet and the contact worksheet. The following are the specific steps:
1. Add a relationship control
2. Relate to the target worksheet
Select which worksheet to relate to the current worksheet. Here, we choose the Contacts worksheet in the current app. Users can also select a worksheet from another app.
If you want to relate to a worksheet in another app, you must be an administrator or developer of the target app to have permission to select it.
If a relationship has already been added in the other worksheet, you need to choose from the existing relationships. For example, if we have already enabled the relationship with the Contacts worksheet in the Customers worksheet, then when setting up the relationship in the Contacts worksheet, you only need to add it from the existing relationships.
Bi-directional relationship
Take the Customers and Contacts worksheets as an example. In a customer record, you may need to relate certain contacts, while in a contact record, you may need to specify which customer it belongs to. These two worksheets can be related and synchronized through relationship fields — this is called a bidirectional relationship. If a customer record is related to a contact record, the contact record will automatically display its link to that customer.
In relationship field settings, the following icon indicates a bidirectional relationship.
Uni-directional relationship
In relationship field settings, the following icon indicates a unidirectional relationship.
3. Attributes of relationship field
Basic settings
Field name
Set the relationship field name to display the contact records in this customer worksheet.
Relationship quantity
Multiple rows: Allow relating one or multiple records.
Single row: Only allow relating one record.
Choosing the proper relationship quantity has a significant impact on data loading efficiency and data display. It is recommended to choose relating a single record whenever possible, if it logically allows and meets the business requirements. This helps improve data consistency and query efficiency.
Always choose the correct relationship quantity (single or multiple), as this affects data loading performance and display. Follow this rule: If single row is sufficient, always prefer single row over multiple rows.
Introduction to three types of relationships
Display modes of related records
To select and display multiple related records
When there is a need to select and display multiple related records, we provide the following four methods:
-
Card: Display selectable records in a pop-up window, showing the related records in card format.
-
Dropdown List: Select and display related records through a dropdown menu.
-
Table: Display related records in a table, providing a clear view of detailed information for multiple related records.
-
Tab table: Display records in a table under a tab, suitable for requirements where detailed display and switching between different related records are needed.
If selecting the cards and dropdown list mode, a maximum of 50 records can be related; while if choosing the table and tab table, there is no limit on the number of related records.
To select and display a single related record
There are two ways, dropdown list and pop-up. After selecting the related records in the pop-up, display these records in card format.
Title field
In dropdown list and card modes, users can set the title field of the related records, which is by default the title field of the related worksheet.
Effect:
Display fields
When selecting related records, in addition to the title field, it is often necessary to display more field information of the records. Users can drag and drop display fields to sort them.
-
Card
When selecting and displaying related records, other display fields and the title field are shown in the same card, displayed below the title field.
-
Dropdown list
When displaying related records, other display fields are shown in a card below the dropdown menu.
If more fields need to be displayed in the dropdown list when selecting related records, users can check "Show additional info and cover in the dropdown list" to set the display fields.
-
Table
Cover
Select a file field to use as the cover image for cards and selectable records list.
-
In the card of related records, a file field from the record can be displayed as the cover, but it must be a file or image that can be previewed.
-
Display cover image in the selectable related records list.
-
When displaying related records, the cover image is only shown on mobile in dropdown lists and tables, not on PC.
-
Cover image in dropdown list
-
Card/Table: Cover image in the selectable records list
When relating multiple records, the cover image is only displayed in card mode.
Sort related records
Set a sorting rule to sort and display the related records. Users can set multiple sorting fields, and adjust the order by dragging the fields.
In card and dropdown list, sorting related records is not supported.
As shown below, sort the related records in descending order by name.
In table mode, there are three ways to sort the related records, prioritized as follows:
-
If a sorting rule is set here, the related records will be sorted according to this rule, with the highest priority given to this setting.
-
If no sorting rule is set here, but a "Related view" is set below, the sorting rule configured in the view will be taken.
-
If neither of these settings is applied, newly added related records will be displayed at the end.
Default value
When creating a new record, you can default to relating one or more records, with the following three ways to set default values for relationship fields:
-
Fixed default value: Each new record is related with the specified record.
-
Other field values: Automatically reference records related with the other fields in the related record (not available in table mode).
-
Query worksheet: Get records by querying the worksheet and automatically relate them (up to 500 records)..
Check out this article for more details about setting default values
Operation settings
Allow selection of existing records
-
If checked, users can directly select and relate existing records in the Contacts worksheet.
-
If this option is not checked, the "Relate existing record" button will be hidden.
When relating existing records, users can set filters to display which records are available for selection, such as only choosing from records visible to themselves.
What to do when the dynamic value in the filter condition is empty
Users can set filter conditions to control the list of selectable records. When a dynamic value used in a condition is empty, we provide four ways to handle it.
Explanation of Filter Modes
-
AND
Condition group: (Condition 1: Field A = Field B) AND (Condition 2: Field C = Field D)
Description: Records will only be filtered if both Condition 1 and Condition 2 are met.
-
OR
Condition group: (Condition 1: Field A = Field B) OR (Condition 2: Field C = Field D)
Description: Records will be filtered if either Condition 1 or Condition 2 is met.
Handling of empty dynamic values
-
No filter results for the current conditions
-
When Field D is empty:
AND: If Condition 2 (Field C = Field D) is not met (because Field D is empty), the filtering result will be empty.
OR: Ignore Condition 2 and only consider the filtering result of Condition 1.
-
-
Ignore this condition (return all records when all are ignored)
-
When Field D is empty:
Both AND and OR modes ignore Condition 2 and only execute Condition 1.
If Field B is also empty, return all records due to the lack of valid filter conditions.
-
-
Ignore this condition (return no results when all are ignored)
-
When Field D is empty:
Both AND and OR modes ignore Condition 2 and only execute Condition 1.
If Field B is also empty, return no results and do not display any records due to the lack of valid filter conditions.
-
-
Query null values:
-
When Field D is empty:
Use the empty value of Field D as a query condition for filtering, i.e., find records where Field C is equal to a null value.
-
Allow adding records
If this option is not checked, the "New Record" button will not be displayed, and users can only select existing records to relate.
Allow unrelating
For records that have already been related, users can set whether to allow operators to unrelate. If not checked, the "Unrelate" button will be hidden.
Allow opening records
By default, this option is checked. Users can click on a record or record card to open the details page of the related record.
-
When setting "Allow opening records," users can select a view to display record details based on the operator's permissions.
When the operator viewing records in the worksheet (contact worksheet), the view ("My Contacts" view) is not visible to the operator, or this record is not visible in the view, the operator cannot open the details page of the record.
-
If a view is not selected here but a view is selected in the "Related view" section below, the settings below will apply.
If this view is not visible to the operator or this record is not visible in the view, the operator cannot open the details page of the record.
-
If no view is selected in either of the above settings, the record details page can be opened, displaying only the fields visible to the operator.
Allow export
If checked, users will see an "Export" button when viewing related records, allowing them to export the related records to Excel (exporting only the displayed fields).
Allow batch operations
For records that have been related, users can set batch unrelating, batch editing, batch exporting, batch deleting, and other operations. Click the Settings button on the right to set which batch operations are allowed.
After checking "Allow batch operations", batch operations buttons will be displayed above the table of related records.
Show the record shortcut
If not checked, the shortcut buttons on the left side of the table of related records will be hidden, and users cannot quickly copy records, delete records, unrelate records, etc.
Table settings
Interaction mode
-
Classic Mode (default mode)
Open Record: Click on the blank space of each cell to open the details page of the record.
Quick Edit: Click on the edit button displayed when hovering over a cell to edit the record.
-
Spreadsheet Mode
Open Record: Click on a cell and then press Space to open the details page of the record.
Quick Edit: Click on a cell to select it, similar to operations in Excel, such as navigating cells using the arrow keys or copying content. Press Enter or click again to activate the cell for editing. When editing a field, you can input data using the keyboard. For fields like number fields or single-select fields, use the up and down keys on the keyboard to select options after the field is activated.
Row height and column width
The table of related records can have customized row heights and column widths.
-
Row Height
Choose the appropriate height in the field settings.
-
Column Width
Dragto adjust the column width, and then click the Save button on the left. The adjustments made to the table by the applicatiion administrator apply to all members.
Tree table
For multiple related records, they can be displayed as a tree table, allowing users to visually show hierarchical relationships between data by collapsing or expanding.
Example: In a project worksheet that is related to tasks, if the task worksheet has configured parent and child tasks, users can select the "Parent Task" field as the basis field in the tree table.
Effect:
Allow inline editing
If checked, users can directly edit cells in the table of related records. If unchecked, fields need to be edited on the record details page.
Show alternate row colors
Showing alternate row colors in the table of related records can improve readability.
Advanced settings
Query settings
When selecting records to be related, users can quickly locate the target record through searching and filtering.
Query: Search and Filter
-
Search fields
Only one field can be specified, and users can choose between exact search or fuzzy search.
-
Filter fields
It supports configuring multiple fields.
Related view
If a related view is set here, it will affect the sorting of related records, the selection range of records that can be related, and the view when opening records.
If the relationship field has already been set with sorting rules and a view in the "Allow opening record" section, those settings take precedence over the settings here.
It is important to note that even if a related record no longer appears in the selected view after being modified later, the relationship still exists.
Filter the displayed results
For already related records, users can also filter to display only certain records.
-
Filter by criteria
Set a specific condition to show only related records that meet that condition.
-
Show by user permissions
If checked, data will be displayed based on the operator's permissions on the related worksheet or view, hiding data or fields that are not visible to them.
For records that are hidden due to lack of permissions, it will display "X records are hidden".
Show count
When "Filter the displayed results" and "Filter by user permissions" are set, the number of records displayed may be less than the count. Therefore, it is recommended to uncheck this configuration option.
Mobile settings
-
Scan content
Scan to add the related record, which means identifying a record based on the scanned content. In addition to using QR codes from record links to locate a specific record, users can also specify a field value to identify the target record for relationship. For example, if you select the Name field, the system will scan the content, recognize the name, and then search for the matching record to establish the relationship.
-
Disable manual input
If checked, users can only add related records by scanning codes on the mobile app. Manual addition of related records is disabled on both the PC and mobile.
-
Disable album
If checked, users can only take photos and cannot select existing images from the album.
-
Scan before displaying the form
If checked, when creating a record on the mobile app, it will first initiate a scan input. This feature also works when filling in through custom buttons.
-
Submit the form after scanning
Automatically submit the form after scanning on the mobile app. This feature also works when filling in through custom buttons.
4. Operations on related records
Add related records
New record: Create a record in the related worksheet and automatically relate it with the current record.
Relate existing record: Select an existing record from the related worksheet and relate it.
Unrelate records
Unrelating does not delete the record.
Display related records in columns
No additional configuration is needed; the related records just need to be displayed in the tab table.