Auto-populate subform data from a form based on lookup selection
Requirements
Streamline the process of retrieving and displaying relevant data by auto-populating a subform based on the value selected in a lookup field.
Use Case
Consider an IT asset management application used by organizations to conduct asset audits. The process is streamlined through a dynamic data-loading feature in the asset audit form. When an auditor selects an employee’s name from the lookup field, the subform automatically populates with all relevant asset details associated with that employee, retrieved from the asset details form. This feature provides auditors with immediate access to employee-specific asset details, enabling real-time asset tracking and verification.
Steps to follow
- Create forms with the following fields for storing employee and asset details, which can auto-populate in a subform.
Form
Form Link Name
Field Type
Field Name
Field Link Name
Employee Details
Employee_Details
Name
Employee Name
Employee_Name
Email
Email
Email
Phone
Phone
Phone
Address
Address
Address
Asset Details
Asset_Details
Look up
(Employee Details)Employee Name
Employee_Name
Drop Down
Asset Category
Laptop
Mobile
Hardware
Asset_Category
Single Line
Model
Model
Single Line
Serial Number
Serial_Number
Asset Audit
Asset_Audit
Lookup
(Employee_Details)Employee Name
Employee_Name
Subform
Single Line
Single Line
Single Line
Drop Down
Assets Owned
Asset Category
Model
Serial Number
Verification (Passed/failed)
Assets_Owned
Asset_Category
Model
Serial_Number
Verification
- Create a workflow to execute on user input of the Employee Name lookup field in the Asset Audit form. This workflow will be configured to auto-populate the asset details of the employee in the subform.
- Click Add New Action and enter the following script in the Deluge editor.
- //Fetch all assets of the employee
- fetch_records = Asset_Details[Employee_Name == input.Employee_Name];
- //Create a collection to store the fetched subform data.
- rows_collection = Collection();
- for each data in fetch_records
- {
- row = Asset_Audit.Assets_Owned();
- row.Asset_Category = data.Asset_Category;
- row.Model = data.Model;
- row.Serial_Number = data.Serial_Number;
- rows_collection.insert(row);
- }
- //Insert the collection to the subform
- input.Assets_Owned.insert(rows_collection);
- Create another workflow in the Asset Audit form by selecting the Record Event as Created or Edited and Form Event as Field rules. This workflow will prevent the user from adding and deleting the auto-populated subform rows.
- Click Add New Action > Show/Hide subform actions > Hide subform add entry and select the Asset Owned subform. In the same workflow, click Add New Action > Show/Hide subform actions > Hide subform delete entry and select the Asset Owned subform.
- You can also choose to disable the auto-populated subform fields by creating another workflow on load of the Asset Audit form by selecting the Record Event as Created or Edited, as shown.
- Click Add New Action > Deluge Script and add the following script in the Deluge editor.
- disable Asset_Owned.Asset_Category;
- disable Asset_Owned.Model;
- disable Asset_Owned.Serial_Number;