Help to complete this workflow with iterators and array!

[Davide_Fenati]

I need your help on how to complete this workflow.

Expected outcome: Group information by surname and, at the same time, perform some calculation operations.

Starting from this example file:

The expected outcome is to output the following result into a Google Docs document using the “creating a document from a template” Google Docs module:

Surname | sum(Hours*HourlyCost) | Join of the services | sum(Hours)

Which for the surname “Rossi” would become:

Rossi | (5*50)+(12*100) | Service1, Service 2 | (5+12)
Zambianchi | … And so on.

This is the flow I’ve currently set up, and it correctly returns the calculations and unions for each person. However, I don’t understand how to “join” the results of the numerical and text aggregation operations I’m getting from the three different router branches, using the surname as a reference.

This is part of the output from module 6 (array aggregator), where “surname” was used as the grouping element.

The iterator then iterates through the array elements, which are now ordered by “surname.” Below there is part of the output.

Finally, the router performs the three calculation operations:

• Module 31 = multiplication of Hours*HourlyCost, and sum
  Here the setup:

  

• Module 34 = union of services

• Module 38 = sum of Hours.

This is part of the output from module 31, which as you can see does the operations as it is expected to do.

Now, what I can’t figure out is how to combine all three results that I’m getting from the three branches based on the surname so that I can map the respective variables and insert them into the placeholders of the Google Docs template.

From the branch of module 31 → I get surname & total cost;
From the branch of module 34 → I get surname & services;
From the branch of module 38 → I get surname & total hours.

Attached the blueprint if it might help…

Please, I need your help to figure out the solution. Thank you!

Invoice_generation.json (23.8 KB)

[Donald_Mitchell]

Hey there @Davide_Fenati and Welcome to the Make Community!

You could accomplish this in about 6 modules, but would require some advanced usage - a custom data structure and JSON modules. Curious to see who else comes up with more efficient ways of solving it.

Starting from Google Sheets, you could aggregate to JSON - this JSON would include all the data from your sheet plus one extra field - to calculate the total (hours * hourly cost). You’d have to build a custom data structure for this - when you add a JSON aggregator and prompted to select a Data Structure you click Add. Add text and number fields for all your Google Sheet fields + an extra total field.

Follow that with Parse JSON (to turn your JSON into an object) then Array Aggregator to turn that into an Array. This array would be an array of 5 collections. Each collection looks like the output from Google Sheets, except for that added total field. Two of these collections would have surname = Rossi.

Finally, one last Iterator and Text Aggregator combination.

In the Iterator, use map() on the array you created and use surname as the key. This map function will return to you an array of all the surnames, which includes Rossi twice. Use deduplicate on that to leave yourself with only one of each name in the array, which should now be an array of 4 (Rossi only once now).

In the Text Aggregator, you’d build your final output text.

Each element separated by pipe would have a map() function combined with either a sum() function to total up numbers or join() to concat some array elements into a string of text.

If I get more time I’ll build this and share, but custom data structures can’t be shared so you’d have to build that yourself!

[Davide_Fenati]

Thank you Donald!
Ok up to here it’ok:

With the following output for the aggregator (Grouped by surname):

But I can’t understand the logic of what’s next (iterator, map and aggregator).
I suppose the iterator allows you to do the mathematical operations (sum of the TotalCost for “Rossi” surname for example); Below the output:

But I do not understand how the iterator + 1 single module of text aggregator allow you to complete the workflow and obtain the aggregation I need.

Thank you for the clarifications!

[Donald_Mitchell]

In this case we wouldn’t use the Group By in the Aggregator. Perhaps you can, but in my example we aren’t. The goal is to get a single bundle consisting of all the lines/rows.

Then you get a unique list of the surnames, iterator through them and with each iteration you’re aggregating total cost and services and creating your final output line.

Here’s what the Iterator looks like:

Then the final Text Aggregator:

I put the four columns on separate lines to make them easier to read.

The first is just the surname (value of the iterator - since you’re iterating on the unique list of surnames)

Second is all totals from the array where surname = the value of the iterator (current surname you’re iterating on). Then we sum up the totals using the sum() function.

Third is all the serviceDescriptions from the array where surname = the value of the iterator.

Fourth line is all the hours from the array where surname = the value of the iterator. Again, we use sum() on those to get the total.