How to extract a single line item regardless of number or order of line items

  • 25 August 2020
  • 4 replies
  • 1738 views
How to extract a single line item regardless of number or order of line items
Userlevel 4

Hi everyone,

Clint here, from Zapier support!

I’ve been meaning to write some guides for interesting workflows we’ve developed in support to solve some difficult issues. Today I want to share a formula that lets you pick a specific value from a set of line items, even if you’re not sure of the order or number of line items that are going to come in.

Let’s say you’re triggering on a new purchase, maybe from Shopify or Square, and you’re getting some of the data as line items. You need only one of those line items, like a specific piece of metadata about a purchase, the shipping cost, or additional fees. But! Sometimes there are 3 line items, like this:

 

And sometimes there are more, or they’re in a different order:

 

How would you get the Shipping fee out of both of these using a single step? You can’t say “always give me line item 2” or “always give me the last line item” because they’re not consistent enough for that.

Luckily, that’s where this formula comes in:

 

That looks like a bit of a mess, doesn’t it? Hopefully, once I explain what’s happening here, it will make sense!

First of all, that is a “Spreadsheet-Style Formula,” which you may be familiar with if you’ve used Excel or Google Sheets before. Essentially, it’s doing this:

IF X is true, then return this text, otherwise return this other text

The trick here is that all of our formatters support line items. In other words, if we give this formula 4 line items, it will run 4 times, once for each item, and return all of the results. In our case, what this is doing is running through all of the titles, and only returning the value if the title is “Shipping.” Otherwise, it returns “”, which is just a nice way of saying nothing at all.

Here’s a screenshot of the results of this formula:

As you can see, all of the other fields have been returned as blank, leaving only the Shipping field. Now we can map this output to any step that doesn’t support line items, and it will just send “3.00”. And since we’re using that formula, it will work no matter how many line items there are or in what order they’re in! 

Here’s a step-by-step guide on how to get this to work, along with a copy/paste version of the formula above.

Step-by-step Guide

  1. Add a new Formatter step to your Zap, and choose Numbers as the action
  2. For the transform, choose Spreadsheet-Style Formula
  3. For the Formula, copy and paste this:

IF("{Title}"="Shipping","{Value}","")

  1. Replace {Title} with the line items that have the name of the data you want
  2. Instead of “Shipping”, type in the exact name that you want to match to. Make sure to leave the quotes!
  3. Replace {Value} with the line items that have the value you’re trying to extract

Once you test that, you should be left with just the matching value that you need, and you can map it in later steps.

One caveat: If the field you want to map this value to supports line items, you’ll need to add an extra step. Specifically, you would add a Formatter > Utilities > Pick from List, and use the input from the formatter you just created. Then choose to pick the First from the list, and this will return the only value on the list. Now you can map that to any field at all!

I hope this helps out! Line items can be a bit tricky to work with, but hopefully you’ll find this useful. :)


4 replies

Userlevel 2

Thank you for this solution. I’ve had a similar issue previously that I’d like to share and hopefully it will help some folks.

 

We had some data come in from a shopping cart embedded in an online store that had no direct API but a webhook was available. All of the purchase data, 20 or 30 so individual fields, were embedded inside the webhook which comes into Zapier catch webhook as one piece of data. A creative solution was in order - a sort of divide (step 2) and conquer (step 3) strategy. Although I am sure there are other ways to achieve this, here’s how we got it done:

  1. Catch hook
  2. Formatter > transform > string.split > Seperator = name of variable inside the webhook metadata > segment index = second (see screenshot)

     

  3. Formatter > transform > string.split > seperator = we now have an isolated from one end piece of the variable we are trying to isolate from the metadata, we take the very right digit/letter/symbol > segment index = first (see screenshot)

     

Output of step 3 will be the variable you are trying to isolate. 

 

Pros of this solution are that all variables/fields are extracted via a single Zap per transaction.The cons are you will have to do some grunt work and manually isolate (2 steps per variable/field to isolate) inside your Zap for each variable. The Zap for this particular exercise ended up having 49 steps :)

 

 

Would love some feedback on this workflow and if anyone has had similar challenges!

Hi @ClintEmsley 

 

I am running into a similar problem:

However in your explanation, I can see you seem to first extract the line item titles and then the line item values. Is that correction ?

 

this is my use case:

I have a list with information formatted as follows:

1:

size: 5

units: m²

2:

size: 6

units: m²

 

I need to extract the “size” values. 

 

Any suggestions ?

Userlevel 2

@sweepbright are you able to drop a screenshot of how your data is coming in and post it. Maybe we have some ideas for you...

Hi @ClintEmsley 

 

I’m following as you said but can’t seem to extract it correctly. This is how my data comes in:

Various attributes with attribute options

And I want to extract only the attribute used as variation (true) which in this case is id:1 (Talla).

However when I enter the IF function it correctly extracts only the first attribute to be the case but gives me ALL the attribute options as the output:

 

What am I doing wrong?

Reply