When to Use This Feature?
When recording employee activities, such as leave requests, check-ins, or late arrivals, you may need to number records separately for each employee and date.
For example, records with the same employee and date should be numbered sequentially, even across different submissions. When the employee or date changes, the numbering starts from 1 for the new combination.
How Does It Work?
This solution uses formulas to count rows with the same condition within the current subform. An Aggregate Table stores the historical count for each condition, and Data Linkage retrieves that count to continue numbering across submissions.
What Does It Look Like?
For example, when using a unique employee-and-date code as the condition:
Submission | Condition | Sequence No. |
First | A20261020 | 1 |
First | A20261020 | 2 |
First | B20261020 | 1 |
Second | A20261020 | 3 |
Second | A20261021 | 1 |
The same condition continues its previous sequence, while a new condition starts from 1.
How to Set It Up?
Step 1 Creating the Form
Create a form named Employee Activity Records with a subform named Activity Details.
Add the following fields:
Field Name | Field Type | Location |
Condition | Single Line | Subform |
Sequence No. | Single Line | Subform |
Count Helper | Number | Subform |
Row UUID | Single Line | Subform |
Condition Code | Single Line | Subform |
Row Index | Number | Subform |
Previous Conditions | Single Line | Subform |
Historical Count | Number | Subform |
Condition Code List | Single Line | Main Form |
UUID List | Single Line | Main Form |
Set the default value of Count Helper to 1. Leave Historical Count unconfigured for now.
Step 2 Configuring the Formulas
Select the corresponding field and go to Field Properties > Initial Value > Formula > Edit.
Configure the following formulas:
Field Name | Formula |
Row UUID |
|
UUID List |
|
Row Index |
|
Condition Code |
|
Condition Code List |
|
Previous Conditions |
|
Sequence No. |
|
The Sequence No. formula counts preceding rows with the same condition and adds the historical count to generate the next number.
Note: The formulas use a fixed length of 9 characters for each condition. Use unique condition codes containing no more than 9 characters.
Step 3 Creating an Aggregate Table
Go to App Management > Aggregate Tables and click New Aggregate Table.
Configure the following settings:
Data Source: Select Employee Activity Records and its Activity Details subform.
Dimension: Select Activity Details.Condition Code.
Metric: Name it Historical Count and use SUM to aggregate Activity Details.Count Helper.
Since Count Helper is always 1, the aggregate table counts the submitted rows for each condition.
Click Save to complete the configuration.
Step 4 Setting Up Data Linkage
Return to Employee Activity Records and select the Historical Count field in Activity Details.
Go to Field Properties > Initial Value > Data Linkage > Data Linkage Settings.
Configure the following settings:
Setting | Configuration |
Linked Form | Aggregate Table created in Step 3 |
Condition | Aggregate Table.Condition Code equals Activity Details.Condition Code |
Triggered Linkage | Display the aggregate metric Historical Count in Activity Details.Historical Count |
Click OK to save.
The field retrieves the historical count for the matching condition, allowing the numbering to continue from previous submissions.
Step 5 Hiding Auxiliary Fields
Select the auxiliary fields and go to Field Properties > Field Permissions. Uncheck View.
Go to Form Properties > Hidden Field Default Value and select Always Recalculate.
Keep Condition and Sequence No. visible.
Notes
The condition must uniquely identify the group being numbered, such as an employee-and-date combination.
For conditions without historical records, the historical count should be treated as
0so numbering starts from 1.This method is intended for sequential submissions. Concurrent submissions or changes to historical records may affect numbering accuracy.