Hi, I am building an automated invoicing workflow That is pulling data from multiple sources to create unique line items on an invoice.
The Main Fields are Cost, Quantity and Product name.
The quantity comes from the survey we give our customer.
That survey is checked against prices in a google sheet and an invoice is generated.
My problem is that I am using an iterator to get
The names of the Product (Header Row of the Google Sheet)
The Price of the product which is matched with one of the vendors in the subsequent rows
The quantity of product (provided by the survey)
When I attempt to add multiple iterators it compounds the data output
and when I try an place aggregators in between I am not able to get the last aggregator to get the multiple outputs to map the final array.
I’ve tried the split function as well but have had little luck.
Any assistance trying to resolve this would be appreciated.
Let’s say you have a Google Sheet with names and prices.
Then, in a form/survey you have a list of product names and quantity of each.
And you want to iterate each item in the form, get the price from the Google Sheet, multiply by quantity and that’s your line item.
After Google Sheets Get Rows, you have an aggregator that will aggregate the name and price columns into an array.
Then, while iterating each item in the form, you build a new object (either array or JSON) that will contain the product name (from iterator), quantity (from iterator), price (see get-map function below), and price*qty.
To get the price, you’d use get(map(array_from_gsheets;price;name;name_from_form))
I don’t have time to build an example at the moment, but I hope this makes some sense!
Please see this blueprint as an example. It’s self-contained and doesn’t require any additional setup to run it, but it should help explain what I was trying to describe. MultipleLookups.json (13.9 KB)
Modules 2 and 3 represent the Google Sheets containing the price list. Column 0/A is the Name and Column 1/B is the Price.
Modules 6 and 4 represent the order form data.
Module 7 iterates the order form data.
Module 8 Aggregates the order form data back into a new object, which also includes data looked up in the array (by using map() function) created by module 3.
Module 8 outputs JSON so module 9 just parses it and makes it easier to read and use for any modules that follow.
Thank You so much for taking the time out to Help @Donald_Mitchell.
As a little bit of clarification, Currently my data is row by row, (A Client Database with additional Header properties for the product or service deliverable).
Each Row Containing a Unique Client ID and Numbers that represents “their price” for the product/service
I did have success when I transposed the Rows as vertical data only using 3 columns (Product name, Price, Quantity) then using an array aggregator thereafter. However Got stuck again trying to format it using the automation.
I am a bit of a novice when it comes to the JSON Get and Map functions but am determined to figure it out. I do believe that this is the direction I should be heading in but getting the formula correct has been my fustration.
Please let me know if this new information changes anything regarding the approach I should taking. Again I thank you for taking the time out to assist.
