This article will explain how to directly write data from checkboxes/dropdown checkboxes into a subform, and automatically add corresponding rows in the subform based on the number of selected items.
When to Use this Feature?
This method is applicable when you need to convert data from checkboxes or dropdown checkboxes into subform data. Common scenarios include employee interest surveys, multiple product selections, etc.
How does it work?
First, calculate how many items were selected in the checkbox:
COUNT(checkbox)Then, set up the subform to automatically adjust the number of rows as shown in the example.
Finally, split the selected checkbox data into the subform using the formula:
SPLIT(TEXT(checkbox), ',')[Subform Row Number - 1]
What does it Look Like?
For example, selecting multiple products from a dropdown checkbox will yield the following result:
(Insert expected result screenshot)
Note: Due to the template installation limitation of a maximum of 100 records per form, when using the template, you need to re-enter data according to the steps in 2.2 "Subform Row Number Base Table" Data Entry to achieve the desired effect. Otherwise, the effect cannot be experienced.
How to Set up?
It is recommended to first install the Subform Row Number Auto-Adjustment application template before proceeding. This article will explain the process using the example data in the template.
Step1 Creating the form
Create a "Product Selection Form" with the following field settings:
Field Name | Field Type |
Select Product | Dropdown |
Row Count | Number |
Product Details | Subform |
Serial Number | Subfield - Number |
Product | Subfield - Single Line Text |
2. Create a "Subform Row Number Base Table", used for recording subform base data for different row numbers. Field settings as shown below:
Field Name | Field Type |
Total Rows | Number |
Subform | Subform |
Serial Number | Subfield - Number |
Number | Subfield - Number |
Step 2 Importing data from "Subform Row Number Base Table"
In the "Subform Row Number Base Table >> Data Management >> Import" section, import the example data from the attached file: 200-row Subform Base Table Serial Number Complete.xlsx.
Ensure the data is correctly linked to the form fields as shown in the example.
Step 3 Setting Up Data Linking
Open the "Product Selection Form" form, select the entire "Product Details" subform. Under Field Properties >> Default Value, set the option to Data Linking and click Data Link Settings:
In the data link settings page, configure the following:
Linked Form: "Subform Row Number Base Table"
Condition: Set "Total Rows" to equal "Row Count"
Trigger Link:
Show values from the "Subform" field in "Product Details"
Add the "Serial Number" subfield and display its value
Click Confirm after finishing the setup.
Step 4 Setting Up Formulas
4.1 "Row Count" Formula Setup
Select the "Row Count" field, go to Field Properties >> Default Value, and set the default value to Formula Editing. Then, click Edit Formula:
In the formula editing page, set the formula for the "Row Count" field as follows:
COUNT(Select Product)
4.2 "Product" Formula Setup
Select the "Product" subfield, go to Field Properties >> Default Value, and set the default value to Formula Editing. Then, click Edit Formula:
In the formula editing page, set the formula for the "Product" field as follows:
SPLIT(TEXT(Select Product),",")[Product Details.Serial Number - 1]
Step 5 Removing Visibility Permissions
Since the "Row Count" field is an auxiliary field, you can remove its visibility permission. Click Row Count, and under Field Properties >> Field Permissions, uncheck Visible and Editable to make the field invisible:
I have finished reading. 🏆
👉I can't wait to Try for myself.
👉I need more HELP in Discord Forum.