Add subform rows with dynamic data from another subform

Add subform rows with dynamic data from another subform

Requirement            

Automatically add rows to a subform and populate it with dynamic data fetched from another subform.

Use Case  

A form named Orders Database contains customer details and their frequent orders in a subform. We have another form, New Order, which customers use to order products. When a customer enters his/her customer ID, their frequent orders are fetched from the Orders Database form and populated in the New Order form's subform.

Steps to follow 

1. Create two forms with the following details:
Form
Form Link Name
Field Type
Field Name
Field Link Name
Orders Database
Orders_Database
Name
Customer Name
Customer_Name
Single Line
Customer ID
Customer_ID
Subform
  • Single Line
  • Number
Order History
  • Product Name
  • Quantity
Order_History
  • Product_Name
  • Quantity
New Order
New_Order
Single Line
Customer ID
Customer_ID1
Single Line
Customer Name
Customer_Name1
Subform
  • Single Line
  • Number
Order Details
  • Product Name
  • Quantity
Order_Details
  • Product_Name1
  • Quantity1

2. Create workflow with the following details :

The workflow will be triggered when a customer enters a value into the Customer ID field, so we are selecting the Form Event as User input of a field.

3. Click Add New Action.

4. Save the following Deluge snippet in the Deluge editor: 
  1. // Fetch customer records from the database based on the specified Customer ID
  2. database_records = Orders_Database[Customer_ID == input.Customer_ID1];

  4. // Assign the customer name from the fetched record
  5. input.Customer_Name1 = database_records.Customer_Name;

  7. // If a customer record is found, fetch data from the subform
  8. if(Customer_Name1.isEmpty() == false)
  9. {
  10.  input.Order_Details.clear(); // Clear subform of any previous entries
  11.  for each line_item in database_records.Order_History // Iterate through each subform row to fetch their values
  12.  {
  13.                     // Create subform rows with fetched data
  14. row1 = New_Order.Order_Details();
  15. row1.Product_Name1=line_item.Product_Name;
  16. row1.Quantity1=line_item.Quantity;
  17. rowcollection = Collection();
  18. rowcollection.insert(row1);
  19. input.Order_Details.insert(rowcollection);
  20.  }
  21. }

  23. // If a customer record is not found, clear any previous entries
  24. else 
  25. {
  26.  input.Order_Details.clear();
  27. }

See how it works