Skip to main content

 

 

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.

Summary
Loop using line items
Google Sheet Lookup
Pack 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 ended
The final action where I was attempting to put the item array from the Line Itemizer step into the Products array in this action step.

 

I’ve done some testing and it is possible that the items could be added to storage out of sequence, which is frustrating. 

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.

 

You can do this using a combination of storage and Formatter, and I think it’ll do what you need it to. 

 

  1. Store the product code and quantity in Storage together with a space or other character in between them (eg Z800.159.6 2)
  2. After the filter in the loop, get the information from Storage and add a Formatter by Zapier step and choose Text >Split Text
  3. Use the values from Storage as the input (they will need to be in line items, one code quantity pair on each line)
  4. Add :space:] as the Separator and for the Segment Index select ‘All (as line items)

 

The formatter step will split each pair and give you them as line items:

You can then add each line item set to the relevant field in the Create consignment action. 

 

I think that’ll get the information in the format you need, could you give it a try and let us know how you get on?

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 list
Get item collection from list as single output
Line itemizer to split out calculated quantities from single output to an array
Using 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

 

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 items for that loop but 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!

Terms & Conditions