Best answer

Lookup Table Range

  • 25 July 2022
  • 6 replies
  • 185 views

Hi,

We have a client running a calculator system. That calculator gives a specific result and depending on the result, the user will get an automated e-mail with a specific Google Drive folder link.

What we want, is to capture that result with Zapier, and depending on the threshold, a specific TAG will be sent to Active Campaign, and therefore, the specific automated e-mail will be sent to the client. The thing is, we can’t add these thresholds manually, because we are talking about 750k excel rows.

Example:
From 110000 to 189999  - TAG will be 01_1900000
From 190000 to 199999  - TAG will be 01_2000000

Basically what we need ist: if the result is between 110000 to 189999, tag field will be 01_190000
Does anyone know a way of doing this? We tried everything on Zapier but couldn’t find a solution, apart from adding these 750k numbers manually, which is herculean.


Thanks in advance for all feedback and help.

Regards,

Joel

icon

Best answer by Troy Tessalone 26 July 2022, 01:42

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

6 replies

Userlevel 6
Badge +8

Hey there, @Cheetah ,

Using a Code by Zapier step, if you’re comfortable with Python or Javascript, would be the most straightforward solution.

Other than that, can I ask how many tags you’re using? You mention 750k rows, but considering the 90,000 integers included in the range 110000 to 189999 all map to a single tag, I imagine the number of tags you’re using is actually more on the order of a dozen or so?

Hi Shalgrim

Thank you for your reply, yes, it would be more or less 10 Tags (one per each range threshold).

Userlevel 6
Badge +8

Hey there Joel,

In that case, while Code by Zapier would still be the best solution if you’re familiar with Python or Javascript, another way to go about it would be to create ten Zaps, one for each tag, where each Zap has two Filter steps that only proceeds if the number is greater than or less than the number on either side of its range.

Does that make sense? 

Let me know if you need more detail!

Userlevel 7
Badge +14

@Cheetah 

Try this JavaScript Code.

 

let X = inputData.X; // result

let Tag = "";

if (X >= 110000 && X <= 189999) {Tag = "01_1900000";}
if (X >= 190000 && X <= 199999) {Tag = "01_2000000";}

output = [{X, Tag}];

 

CONFIG

 

OUTPUT

 

Hey there Joel,

In that case, while Code by Zapier would still be the best solution if you’re familiar with Python or Javascript, another way to go about it would be to create ten Zaps, one for each tag, where each Zap has two Filter steps that only proceeds if the number is greater than or less than the number on either side of its range.

Does that make sense? 

Let me know if you need more detail!


Thank you Shalgrim for your help. I tested Troy’s solution and it worked like a charm.

@Cheetah

Try this JavaScript Code.

 

let X = inputData.X; // result

let Tag = "";

if (X >= 110000 && X <= 189999) {Tag = "01_1900000";}
if (X >= 190000 && X <= 199999) {Tag = "01_2000000";}

output = [{X, Tag}];

 

CONFIG

 

OUTPUT

 

Thank you, Troy. It worked.