Aggregate data from multiple operations

I am trying to aggregate data that is “deeply” nested in Collections, the total number of which is variable.

In this data an array of Customers collections each includes an array of “Custom Field Values” collections, in which I am trying to sum() up the “Display_Value” in the “PAX 12pk” collections. These are text format so I do have to parseNumber() on it.

So far I have a Repeater based on length(Customers[]) which feeds into a Set Multiple Variables that Maps the “Customers[].Custom_Field_Values[].Name” and “Customers[].Custom_Field_Values[].Display_Value” into respective.
This feeds into an Iterator that breaks down the “Customers[].Custom_Field_Values[].Name” array from the Set Multiple Variables, goes through a Router and filters on the “Customers[].Custom_Field_Values[].Name” for contains(PAX).
I use a second Set Multiple Variables to parseNumber(Customers[].Custom_Field_Values[Iterator.Bundle.Bundle_Order_Positions].Display_Value).
Note: Though the PAX collection is currently always in the first position, the reason I’m doing it this way is in case the “Custom_Field_Values” order/variables ever change in the future.
In the example shown 2 Operations are generated. How can I sum up the “PAX 12pk” values from the 2 Operations back into a single variable/operation?
I tested an Aggergator, but that only works within operations, not over multiple operations which I’m trying to solve for.
Am I even on the right track for the solution?

A solution which is working:
Since I’m saving the data in Data Store I can use a variable in the struct that is preset to 0.
By using Get Record I can then sum up the variable in the Update Record.
In this example the record variable is called “PAX”, thus in the Update Record I run Get_Record.PAX + parseNumber(Customers[].Custom_Field_Values[Iterator.Bundle.Bundle_Position].Display_Value)

This also saves on a module and the associated operations.

Let me know if you have another solution. I would be interested what else is possible if, for example, I wasn’t using a Data Store.

1 Like

Hi @Eli_Rozen I would do it in 3 modules:

  • an iterator to iterate through your main array Customers[]
  • A Set Variable module to first map all the values of the Pax 12pk for each item of that main array, using a filter, then sum them up:
    {{sum(map(Customers[].Custom_Field_Values[]; "display_value"; "name"; "PAX 12pk"))}}
    (Make sure to pass the raw values for diplay_value and name )
  • a Number Aggregator module to sum all those previous sums:

    Hope that helps :slight_smile:
1 Like

@Lauren_Moineau thank you but I’m a little confused about your approach.

In my example, I need to sum up Customers[1].Custom_Field_Values[x].Display_Value + Customers[2].Custom_Field_Values[x].Display_Value +... Customers[n].Custom_Field_Values[x].Display_Value

I’m not seeing how I iterate through each “Customers[1 to n]” in the summation/aggregation step, or basically how I’m creating the array of <Customers[1].Custom_Field_Values[x].Display_Value, Customers[2].Custom_Field_Values[x].Display_Value,... Customers[n].Custom_Field_Values[x].Display_Value>

Simple update, I moved the Get Record before the Router so that the record is available in all the branches in case I need to aggregate other variables.

UPDATE: scratch that approach, since I have 8x Custom Field Values in each Customers, the Get Record runs 8x # of Customers, so 8 operation at minimum and 16 operations in my example (didn’t see it initially because I was filtering out everything but PAX before) :face_with_spiral_eyes: :sweat_smile:

Where to put the Get Record will depend on the most efficient # of operations you get each run based on average possible loops, bundles generated in earlier steps, and filtering of the links.

1- Iterator
We iterate through your Customers[] array, creating a separate bundle for each customer: one bundle for customers[1], then one for Customers[2] and so on.

2- Set Variable module
At each iteration, for each bundle of data, we go into that Custom Field Values array.
I assumed you could have several items named PAX 12pk in that array.
We create a sum of the values of all the PAX 12 pk for that given customer (thanks to the map() and sum() functions).
We are returning the sum of Customers[n].Custom_Field_Values[x].Display_Value you need (if I understand correctly) for a given customer.

(map(Customers[].Custom_Field_Values[]; "display_value"; "name"; "PAX 12pk") says
“For this customer, take the nested array Customers[].Custom_Field_Values[] and return an array of display_value where the name is PAX 12k
(we do this at each iteration, for each customer)

Then sum() returns the sum of the values of that array.

If you only have one item named PAX 12pk in the Custom Field Values array for each customer, you could replace sum() with first(), which will return the first and unique value of the map() array. The function would then be:
first((map(Customers[].Custom_Field_Values[]; "display_value"; "name"; "PAX 12pk"))

3- Aggregator
We take the value generated for each customer/bundle and sums them all.

Hope that’s clearer. Have you tried it and does it work for you?


I believe I understand what you mean now, but correct me if I’m still missing it based on my response here:

Unfortunately in my case, there is only 1x PAX 12pk type Custom Field Values container inside each Customers container, and I need to aggregate the PAX 12pk values ACROSS Customers[], not WITHIN

See the structure in the picture, PAX 12pk is only Custom_Field_Values[1] in each Customers[], Custom_Field_Values[2-8] are something completely different

Thank you for the discussion.

@Eli_Rozen No problem :slight_smile:
Based on your screenshot, the Set Variable in my scenario would return 13 for the first bundle (customer 1) and 6 for the second bundle (customer 2).
If you don’t have more customers, the Numeric Aggregator will then return 19.

Now, correct me if I’m wrong, you’re saying that PAX 12pk will always be in the 1st item of the Custom Field Value.
If you’re 100% certain that it will always be the case, then you can keep the same scenario and just change the function in the second module.

  • Iterator (passing in your main Customers array)
  • Set Variable (getting the value of display_value for each customer) with the following function:
    parseNumber(get(Customer Field Values[1].display_value; 1))
    (you don’t really need the parseNumber here as Make will transform the text into number when you create the sum later, but it doesn’t cost anything more :slight_smile: )
  • Numeric aggregator, a sum() of the previous variables.

Now I’m thinking, do you need to have an intermediary step, i.e an array of all the display_values? Or is the final sum okay (as here)?


hey @Lauren_Moineau,
In answer to your question, it’s not 100% the PAX will remain in the first position. I Iterate the Custom Field Values by "name", to obtain its position.

But I’m still missing a couple of things:
1 - why am I iterating the Customers[] array specifically? I’m not seeing what data I’m using from that module
Can’t I just reference Customers[].Custom_Field_Values[].... as needed, and I know the number of bundles based on length(Customers[])
2 - I still haven’t gotten your approach working properly, but won’t Set Variables break the 2 (or more) bundles into separate operations, not just bundles. This was my initial issue of how to bring values from 2 different operations.

On a side note, that PAX variable will be named one several different ways; PAX 12pk, PAX 10pk, or PAX ski, but it will always contain “PAX” in the name.
This is why I’m mapping the Custom_Field_Values[] name, then iterating to filter the bundle containing “PAX,” and using that bundle position in the next module. I wonder how this changes your solution?