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
- Add a new Formatter step to your Zap, and choose Numbers as the action
- For the transform, choose Spreadsheet-Style Formula
- For the Formula, copy and paste this:
IF("{Title}"="Shipping","{Value}","")
- Replace {Title} with the line items that have the name of the data you want
- Instead of “Shipping”, type in the exact name that you want to match to. Make sure to leave the quotes!
- 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. :)
