How to Count the Number of Line Items Without a Code Step

  • 7 February 2020
  • 15 replies
  • 6665 views

Userlevel 7
Badge +10

In my ongoing series of Expert Hacks/Tips (lol) I wanted to show how we use three simple formatter steps to "count" the number of line items in any situation. We can even use this same methodology to "count" the number of line items that match a specific value or condition.

Here's a video walk through with text explanation below.

Loom | Send a video. Not a thousand words.

Scenario:

So we're jumping in the middle of a zap where you already have a line item (say from a new invoice trigger in Quickbooks). And you want to count how many different products are on the invoice, or alternatively you want to count how many products of type=shoe are on the invoice.

To count type=shoe lines

Step 1: Formatter by Zapier: Utilities: Lookup table.

  • Use the "Type" as the input
  • Put "shoe" on the left of the lookup table and 1 on the right
  • Leave the Fallback Value empty

Step 2: Formatter by Zapier: Utilities: Line Item to Text

  • Use the output of the previous step

Step 3: Formatter by Zapier: Numbers: Spreadsheet-style formula

  • Use the output_text of the previous step in the following formula
  • sum({{step2_output}})

And now the output of Step 3 will be the total number of line items where type=shoe.


To count all lines

To count ALL line items it's the exact same three steps with only a change made to the lookup table. I hadn't ever thought of a use case for doing this with a lookup table until I was recording the above video and it dawned on me that both the fallback value AND the lookup table are optional. I had assumed only the fallback value was optional.

Step 1: Formatter by Zapier: Utilities: Lookup table.

  • Use any variable from the line item as the input. (id, description, quantity etc.)
  • Leave both the right and the left of the lookup table empty
  • Put 1 in the Fallback Value

Step 2: Formatter by Zapier: Utilities: Line Item to Text

  • Use the output of the previous step

Step 3: Formatter by Zapier: Numbers: Spreadsheet-style formula

  • Use the output_text of the previous step in the following formula
  • sum({{step2_output}})

And now the output of Step 3 will be the total number of line items.


As always, I'd love feedback on my methodology and if I'm missing something here or if this inspires you to develop cool new ways to use formatter. And is there anyway to do this with 1 or 2 steps instead of three? 😁



15 replies

Userlevel 7
Badge +10

I just wanted to add to this, that you can also count items using a code step if you feel confident with that. The main advantage here is that it would be a single step.

 

The ‘list’ input value should be line item list you want to count.

The output will be the number of items:

 

 

Here is the code to copy and paste:

var count = inputData.list.split(',').length;
output = {count: count};

 

Userlevel 1
Badge

Hi there! I have a Webhook GET step that returns a JSON array and Zapier plays nicely through the elements of the array. However, I don’t want my whole Zap to be played as many times as items in the array. I only want a few steps to be played for each element, but then I want to “break the loop” and play all remaining steps only ONCE.

I am not using a Code step as of now (and also am not intimate with programming). So I do not know how I can break the loop since I didn’t figure out how to determine the size of the array (it is variable)...Is there a way to “break the loop”?

Userlevel 7
Badge +10

@David@Zapwise 

Unfortunately not! I wonder if you could use Zapier Storage to store parts of the array each time it runs, then somehow trigger a followup zap on the contents of that.

Userlevel 1
Badge

@David@Zapwise

Unfortunately not! I wonder if you could use Zapier Storage to store parts of the array each time it runs, then somehow trigger a followup zap on the contents of that.


Hi Andrew…thanks for your reply! 🙏🏻🙏🏻

...hmm are you saying this can’t be done or this can’t be done WITHOUT a code step

From what I understand from this post...

...it’s is “common practice” to do this when you work with line items. Or am I misinterpreting this conversation? If so, I probably could turn my array into line items quite easily...Probably is a bit heavy on tasks but well...I’m just surprised there is no go-to solution with a Code step.

Userlevel 1
Badge

...thinking about it again: So does this mean, I don’t actually have access to the actual JSON array but Zapier processes it before it is passed to my Zap and only forwards one item at a time? I’m new to this, but noticed that in Postman I GET the whole array whereas in Zapier I only always GET one item. Is Zapier’s behaviour with arrays as input described anywhere?

Userlevel 1
Badge

And thinking a little more...I could probably store everything I need from the array in Zapier Storage and trigger a second Zap BEFORE the array is processed, have the second Zap WAIT for an arbitrarily fixed amount of time, and then run based on the assumption that by that time, the first Zap has completed with all iterations and that it can access the data from the array in the Zapier Storage. But, that looks to me very dirty and not cool.

Userlevel 1
Badge

So..thinking a bit more 🙂 ...could i not build an array/line item object via Zapier Storage and then use this in a second Zap, once this first one Zap has completed running through all the items of the JSON Array?

Userlevel 7
Badge +10

Hi @David@Zapwise 

Yes, I think that might work. You certainly don’t need a code step to do it that way.

Report back and let us know how it goes!

Userlevel 1

I just wanted to add to this, that you can also count items using a code step if you feel confident with that. The main advantage here is that it would be a single step.

 

The ‘list’ input value should be line item list you want to count.

The output will be the number of items:

 

 

Here is the code to copy and paste:

var count = inputData.list.split(',').length;
output = {count: count};

 

Thank you so much for sharing this. It’s proving very useful to me. Although I don’t suppose there is a version of the code that would only count lines containing a specified string of text? i.e. “Shipping”. That would be amazing!

Userlevel 7
Badge +9

@MGZ 

 

Here you go 

 

var count = inputData.list.split(',')
var shipping = count.filter(function (shipping) {
return shipping == "Shipping";
}).length;

output = {shipping};

 

Userlevel 1

@MGZ

 

Here you go 

 

var count = inputData.list.split(',')
var shipping = count.filter(function (shipping) {
return shipping == "Shipping";
}).length;

output = {shipping};

 

Thank you so much! I’ll give it a go :-)

I just wanted to add to this, that you can also count items using a code step if you feel confident with that. The main advantage here is that it would be a single step.

 

The ‘list’ input value should be line item list you want to count.

The output will be the number of items:

 

 

Here is the code to copy and paste:

var count = inputData.list.split(',').length;
output = {count: count};

 

You just saved my day! I was looking for exactly this for hours, until I found this topic and got it working in less than 10 seconds!

@MGZ

 

Here you go 

 

var count = inputData.list.split(',')
var shipping = count.filter(function (shipping) {
return shipping == "Shipping";
}).length;

output = {shipping};

 

This is awesome! Question: Can this be modified so that it looks for 4 different words and return the individual count of those 4 words?

Userlevel 1

@PaulKortman, thank you for this, I’ve got it working great, except when no line items are available. My zap fails because there is nothing to sum.

In my case I have two ticket types and I am trying to sum them but either of them could be zero and therefore in some cases there are no line items available to be summed.

I have tried IF(ISBLANK but that doesn’t work because the SUM function is still evaluated.

I need both ticket type quantities for another system for billing purposes.

Any idea how I can handle this scenario?

Hi, this was really helpful in a project where I need to count a set of inbound line items. I had some issues with the SUM( ) step so I made a tweak that adds a (now FREE 🤩) Formatter step and should be able to handle empty and single-value lists as well.

After the “Line Item to Text” bit where you have text output like (1,1,1,1,1), use Formatter → Text → Replace. Find , and leave the Replace field blank so that it will remove all the commas from your output, leaving you with 11111 etc.

Then you can use Formatter → Text → Length on that output to get the total count of line-items.

Reply