Calculating Route Prices from Tally Form and Determining Recommendations in Make

[Gianluca_Cimitile]

What is your goal?

Hi everyone,

I’m building a scenario in Make where:

Users submit a Tally form selecting multiple travel routes.

I have a Google Sheets database containing the prices for all possible routes.

I want to calculate the total price of the routes selected by the user.

Based on the number of travel days and the total price, I want to determine if a recommendation is “Yes” or “No” using these rules:

If days < 7 and total price > 50,000, then recommended = Yes, otherwise No.

If days ≥ 7 and ≤ 14 and total price > 80,000, then recommended = Yes, otherwise No.

If days ≥ 15 and total price > 100,000, then recommended = Yes, otherwise No.

I’m looking for guidance on the best way to implement this in Make, including:

Calculating the total price from multiple selections.

Handling conditional logic for the recommendation based on days and total price.

Thanks in advance for any examples or tips!

What is the problem?

Hi everyone,

I’m building a scenario in Make where:

Users submit a Tally form selecting multiple travel routes.

I have a Google Sheets database containing the prices for all possible routes.

I want to calculate the total price of the routes selected by the user.

Based on the number of travel days and the total price, I want to determine if a recommendation is “Yes” or “No” using these rules:

If days < 7 and total price > 50,000, then recommended = Yes, otherwise No.

If days ≥ 7 and ≤ 14 and total price > 80,000, then recommended = Yes, otherwise No.

If days ≥ 15 and total price > 100,000, then recommended = Yes, otherwise No.

I’m looking for guidance on the best way to implement this in Make, including:

Calculating the total price from multiple selections.

Handling conditional logic for the recommendation based on days and total price.

Thanks in advance for any examples or tips!

What have you tried so far?

I tried quite a few things but running out of credits and not working. aggregators do not aggregate and keep re ruinning

[Stoyan_Vatov]

Hey Gianluca,

so you are dealing with a variation of the traveling salesman problem basically.

Can you show what the incoming data looks like and what’s coming from the data store?

I assume Tally is giving you an array of items and so does the data store. Using map() you can get a subarray of the days and then sum it to get the total and check the value. Then you can get the price from the second array for the items in the first array (again using map()), sum it again and get the total price. Then do a nested if() function that checks if it matches one of the 3 criteria and if it does, tag it YES and if it doesn’t match any of them, tag it NO.

Also aggregators don’t aggregate on their own. You have to give it a source module that produces an iterated array as an output.

[Gianluca_Cimitile]

Hello Stoyan
Thanks for the explanation! That makes sense — I understand that I need to use map() to extract the arrays from both the Tally form and the Google Sheets data, sum the days and prices, and then use a nested if() to check the criteria.

Just to clarify:

1. For the Tally form, the incoming data is an array of selected routes and number of days.

2. For the Google Sheets data, each route has a price and a all the routes.

My main questions now are:

• Can you give an example of how to map the price from the Sheets array based on the routes selected in Tally?

• And an example of the nested if() to check the 3 different day/price conditions in Make?

Basically, I’d love to see a small formula or step-by-step example so I can implement it correctly.

Thanks again!

[Stoyan_Vatov]

Use a Search module so it would return all of the entries in the sheet. Follow it with a filter to check if the route is contained in the initial array coming from tally. Then follow that with a Numeric aggregator that would sum the Price column to get the total price.

And the formula is something like:

{{if(1.days < 7 & 2.total > 50000; “YES”; if(1.days >= 7 & 2.total > 80000; “YES”; if(1.days >= 15 & 2.total > 10000; “YES”; “NO”)))}}

[Gianluca_Cimitile]

So start with tally, then search module of google sheets, then filter to check if the route is contained in the tally form (initial array). Then numeric aggregator that sum which price? of the search module? And where would I put that formula you typed?
Thank you!

[Gianluca_Cimitile]

Like this?

[Gianluca_Cimitile]

And sorry again, but this is what is happening now

[Stoyan_Vatov]

No need for the sum() function in the Value field. And don’t forget the filter in between the two modules.

[Stoyan_Vatov]

Leave the options inside the module empty so it returns all values. Then do the check in a filter after the module.

Put the array on the top and use the array function Contains to do the check and put the value of the A column on the bottom.

[Gianluca_Cimitile]

Ok I think I am getting somewhere finally. From here I have to set up the formula you wrote right? The end of the scenario is updating the flodesk subscriber and added to a segment and they will receive an email based on their imput and if it is recommended to get the JR pass or not

[Stoyan_Vatov]

I dont see how the days come in from tally, but you should be able to use sum() on the array directly to get that. If its a complex array, then you can use map() to get a primitive one of only the days and them sum it.

[Gianluca_Cimitile]

Cause Tally has a field where the user has to put how many days he/she will stay in Japan

[Stoyan_Vatov]

Ok, so you can use that one directly I suppose.

[Gianluca_Cimitile]

So better use it as a router with 4 different scenario ( 3 yes and one default no) or with a formula?

[Gianluca_Cimitile]

I am trying to do that but for some reason is failing. Even though I am pulling the right field, the output is empty and the scenario fails

[Stoyan_Vatov]

Oh no, thats a different module. Get Variable is used to retrieve the value of a variable created in a Set Variable module.

You can map the number of days directly in the formula I gave you in place of 1.days and then map the result of the sum module in place of the 2.total.

You can do this inside a set variable module and it will give you a Yes or a No. Or you can place the formula directly in wherever you need the Yes/No value.

[Gianluca_Cimitile]

I did that. Pasted the formula inside variable value, but it gives error

[Stoyan_Vatov]

Yeah don’t just paste the formula you need to change the variables inside to the ones you have.

[Gianluca_Cimitile]

The biggest problem is that when I try to fetch only the number of days, it gives me error. But when I fetch all the fields, then it shows. Do you maybe know why?
By the way thank you for your patience!!

[Stoyan_Vatov]

Can you show a screenshot of the Tally module output and how that looks like?