Line items, Google Sheet Lookup, and Looping

Question

Hi all,I have a trigger that is returning an order.  The order has header data (such as order number, delivery address and a product line item array containing values for Product Code and Quantity for all the items ordered.For each of the line items, I need to look up a value in a Google sheet based on the Product Code in the line item array in order to get a pack size for that product code.The quantity ordered needs to be divided by the pack size to know the number of packs required. At the end of all this, I need to complete a single action to pass the order onto our inventory system (it’s not possible to complete an action for each item ordered because the inventory system needs a single record with line items).The action takes header values, and then requires a line items collection of all the things ordered, including the Product Code and the calculation of Quantity / Pack Size. I’ve tried to tackle this from a number of different angles, but have finally got stuck.My last attempt took the line items from the trigger, created a Loop From Line Items action.For each loop, I performed a Lookup Spreadsheet Row in Google Sheets to get the Pack Size for the Product Code.I then created a Numbers in Formatter action to do the division.I then created a Utilities in Formatter action using the Line Itemizer (Create/Append/Prepend) with the intention of adding each Product Code and Calculated Quantity value back into line items (hoping that each loop would append the data to the existing Line-item group).I then added a Filter action to filter on the Loop being the last item, and then attempted to use the Line Itemizer data in the final action’s Product Items collection.But it obviously doesn’t work, because the Line Itemizer doesn’t actually continue to append data on each loop. So my question really, is how to look up data from Google Sheets for each line item and then use the collection and fetched data to plug back into my action. Screenshots attached.SummaryLoop using line itemsGoogle Sheet LookupPack Size Calculator (using Qty from ordered item, and pack size lookup from Google)Line Itemzier (where I attempted to append this data to a line item array on each loop)Filter to ensure that the next action only ran after the loop was endedThe final action where I was attempting to put the item array from the Line Itemizer step into the Products array in this action step.

Danvers · Accepted Answer

Hi @wittemann!Can I check that I’ve understood the issue here? Your looping and Formatter calculation steps all work as you want them to, but the line item append step is adding each item to the line itemsfor that loopbut that doesn’t correctly amend the information for the whole data set, is that right?I’m wondering if you could use Storage by Zapier to store the line item data at a specific key and then retrieve all of it in a step that would sit after the filter step in the loop and before the Create Consignment step. You can use Storage to add items to a list and then retrieve them in later steps, would that enable you to use the line-itemizer in the way you need?Let me know what you think!

wittemann · Answer

Hey @Danvers,Yep, you’ve understood my problem correctly.I didn’t even know that Storage by Zapier was an offering. I’ve managed to get it working, but not sure I’ve done it exactly the right way.I’ve completed this by storing the calculated quantity values into a list, then after the loop I’m fetching the values from the list into a single output, then I’m running it into the line itemizer to get an array.Then in the Create Consignment action, I’m using the Product Code array from the initial trigger, and then the Calculated Quantity array from the itemizer.Whilst it works, the quantities are now disconnected from the product codes, and relies on the assumption that the quantity values added to the list will be in the same order as the product code data returned by the initial trigger.What would be ideal, is if there was a way to store an object of ProductCode/CalculatedQuantity when pushing each list item, and then use that list in the Create Consignment step, referencing a ProductCode property and a CalculatedQuantity property in the appropriate fields of the Create Consignment action.Not sure if that’s possible though?Push calculated quantity onto listGet item collection from list as single outputLine itemizer to split out calculated quantities from single output to an arrayUsing both the original product code array and the new calculated quantity itemizer array to populate the last action - but these items are now disconnected and assumed to be using the same index position as each other

Sign up

Use your Zapier credentials

Log in to the Community

Use your Zapier credentials

Scanning file for viruses.

This file cannot be downloaded