When building forms, you may encounter a scenario where you need to extract a specific row of data from a Subform in Form A and auto-populate it into Form B.
Background & Challenge:
By default, fields within a Subform cannot be directly targeted by Data Linkage from another form. Because a Subform contains multiple rows of data, the system cannot automatically determine which specific row you want to retrieve.
The Solution:
To solve this, we use a workaround called Single-Record Linkage. The core logic involves using an Aggregate Table as a bridge. By selecting the original form as the data source for an Aggregate Table, we can "split" or "flatten" the Subform fields into standard rows. Once flattened, you can use Data Linkage to pull values directly from the Aggregate Table into your target form.
This guide will walk you through how to set this up using a combination of Auxiliary Fields and an Aggregate Table.
How It Works: The Logic
To successfully pull a specific Subform record, we need to follow three main phases:
Identify: Add an Auxiliary Field (a Number field) to both forms to act as a unique identifier or "row number".
Flatten: Create an Aggregate Table to transform the nested Subform data into a flat, searchable format.
Link: Set up Data Linkage in the target form to match the Auxiliary Field and pull the exact record from the Aggregate Table.
Step-by-Step Configuration
Let's use an inventory management scenario as an example: We have a Product Model Registration form (where details are stored in a Subform) and a Stock-in Registration form (where we want to pull specific product details based on selection).
Step 1 Designing Forms
First, we need to create the two forms and ensure both have a matching Auxiliary Field.
1. Create a Product Model Registration form and add the following fields:
Field Name | Field Type | Field Setting |
Registered at | Date&Time | Select Current Time for Initial Value. |
Registered by | Member | Select Current User for Initial Value. |
Product Model Details | SubForm | / |
Product Name | Subfield > Single Select | Select Custom for Option and customize the product names. |
Quality Level | Subfield > Single Line | / |
Number – Auxiliary Field | Subfield > Number | Set Initial Value to 0. This is crucial for identifying the row. |
2. Create a Stock-in Registration form and add the following fields:
Field Name | Field Type | Field Setting |
Registered at | Date&Time | Select Current Time for Initial Value. |
Registered by | Member | Select Current User for Initial Value. |
Number – Auxiliary Field | Number | Set Initial Value to 0. |
Product Name | Single Select | / |
Quality Level | Single Select | / |
Quantity | Number | / |
Step 2 Creating an Aggregate Table (Act as The Bridge)
Now we will use an Aggregate Table to flatten the Subform data from the Product Model Registration form.
1. Go to App Management > Aggregate Tables, and click New Aggregate Table.
2. Name the aggregate table as Single-Record Linkage, click Data Source > Single Form, select Product Model Registration from the drop-down list, and click OK to save the settings.
3. For the Row Header of the aggregate table, add the Product Name and Quality Level subfields from the Product Model Registration form.
4. For the Measure of the aggregate table, set the Auxiliary Field to Number – Auxiliary Field. The Number – Auxiliary Field is from the Product Model Registration form.
After the settings, click Save.
The Aggregate Table now holds your Subform data as flat, individual records that Data Linkage can read.
Step 3 Setting Data Linkage
Finally, we configure the target form to pull data from the Aggregate Table.
1. Select the Product Name field of the Stock-in Registration form, go to Field Properties > Option, then select Data Linkage.
2. Click Data Linkage Settings and make the following settings:
Linked Form: select the Single-Record Linkage form from the drop-down list.
Condition Settings: select the Auxiliary Field field of the linked form and the Number – Auxiliary Field field of the current form, then set the relationship as equals.
Triggered Linkage: display the value of the Product Model Registration.Product Name field in the Product Name field.
After the settings, click OK.
3. Similarly, set the data linkage for the Quality Level field：
Linked Form: select the Single-Record Linkage table from the drop-down list.
Condition Settings:
Select the Auxiliary Field field of the linked form and the Number – Auxiliary Field field of the current form, then set the relationship as equals.
Select the Product Model Registration.Product Name field of the linked form and the Product Name field of the current form, then set the relationship as equals.
Trigger Linkage: display the value of the Product Model Registration.Quality Level field in the Quality Level field.
After the settings, click OK.
Step 4 Setting Field Permissions
Since the Number – Auxiliary Field is only used by the system to match records, it shouldn't be visible to the user filling out the form.
For the Number – Auxiliary Fields in the Stock-in Registration form and the Product Model Registration form, go to Field Properties > Field Permissions, and disable the View permission.
