This guide will show you how to create a process that takes data from several different subforms and neatly merges them into a single, consolidated view.

The logic behind this process is simple but clever:

You need two forms to start: a main form for your data and a simple "helper" form.

1. The Main Form: "Project Expense Report"

This is your primary form where users will input data and where the final consolidated view will appear. Create it with the following subforms:

In your workflow, you can create parallel tasks for "Marketing Team Reporting" and "Sales Team Reporting" so team leaders can submit their expenses simultaneously. You can view the responsible persons and field permissions for each node on the right side.

2. The Helper Form: "Row Generator"

This simple utility form has one job: to generate a numbered list of rows. This allows us to tell our main form exactly how many rows to create in the consolidated table.

Create the form with these fields:

Then, import the sample data from the attached file at Row Generator > Manage Data > Import. This populates the form with a pre-made list of numbers for the data linkage to use.

When importing data, please ensure that the sample data is mapped to the corresponding form fields as follows:

Now, let's make the Consolidated Expenses subform automatically create the right number of empty rows.

1. Select the Consolidated Expenses subform. In Field Properties, set the Initial Value to Data Linkage.

2. Set up the linkage:

This linkage tells the system: "Calculate the total number of expense items I have, then create that many empty, numbered rows in my Consolidated Expenses table."

This is where the magic happens. We'll first merge all the data into a temporary text field and then use formulas in our consolidated table to pull the right pieces of data into each cell.

Part A: Merge all data into one place

1. Select “Marketing Team Expenses - Merged Field” and go to “Field Properties > Initial Value.” Set the default value to “Formula”, then click “Edit” below.

2. On the formula editing page, set the formula for “Marketing Team Expenses - Merged Field” as:

This formula runs for each row in the "Marketing Team Expenses" subform, joining the data together with a "+" as a separator.

3. Do the same for "Sales Team Expenses - Merged Field":

4. Finally, set the formula for the main "Merged Fields Collection" field to combine the two previous fields:

Now you have a single string containing all your data, with rows separated by "," and fields separated by "+".

Part B: Split the data into the consolidated form

Now, we'll add formulas to the columns in the Consolidated Expenses subform to parse the "Merged Fields Collection".

To ensure your formulas update correctly every time data changes, make one last adjustment:

And you're done! You've successfully built an automated workflow that intelligently consolidates data from multiple sources, saving you time and preventing manual errors.