4 types of services and 2 types of packages

[alternaz]

Hello, I am a pro writer for companies and I am looking to generate quotes automatically.

Depending on type of report (4 types) selected by the customer AND the time of writing (full day or half day) the price will vary.

I want to convert this example to formulas :

If half day and very synthetic = price 1
If day and very synthetic = price 2

If half day and abbreviated summary = price 3
If day and abbreviated summary = price 4

If half day and standard synthesis = price 5
If day and standard synthesis = price 6

If half day and exhaustive = price 7
If day and exhaustive = price 8

I have already created in my table, 2 columns:

- one indicating the type of report (very synthetic, abbreviated summary, standard synthesis, exhaustive)
- the second indicating if it's half or full day

I am therefore trying to create a formula that will check in my columns "full day or half day?" AND "Type of report ?" in order to show a price.

Do you have the possibility to help me please?

Have a good day

[alternaz]

@Solyhave you an idea ?

[kuovonne · Accepted solution]

How about using nested SWITCH() functions? My numbering is slightly different from yours, but hopefully you can get the idea. Sorry for the lousy formatting. The new forum software makes it impossible for me to format a formula decently on my phone. I can also see only two lines of the formula at a time, making it even harder to write the formula when I cannot see the whole thing at once so there might be some typos.

SWITCH( {full day or half day?},
"half day", SWITCH( {Type of report ?},

"very synthetic", 1,

"abbreviated summary", 2,

"standard synthesis", 3,

"exhaustive", 4
),

"day", SWITCH( {Type of report ?},

"very synthetic", 5,

"abbreviated summary", 6,

"standard synthesis", 7,

"exhaustive", 8

)

)

[alternaz]

Hello Kuovonne,

Thanks for this answer, I think we are close to the goal!

The formula is accepted by Airtable but does not work, I wonder what the problem is.

SWITCH( {full day or half day?},
"half day", SWITCH( {Type CR},
"very synthetic", "10 €",
"abbreviated summary", "20 €",
"standard synthesis", "30 €",
"exhaustive", "40 €"
),
"full day", SWITCH( {Type of report ?},
"very synthetic", "50 €",
"abbreviated summary", "60 €",
"standard synthesis", "70 €",
"exhaustive", "80 €"
)
)

My tab 1 is showing : exhaustive
My tab 2 is showing : half day

Have you some idea ?

Cheers

[alternaz]

Have you some idea @Andrey_Kovalev or @kuovonne ?

[Andrey_Kovalev]

@alternazI can see an error in your script which shows differently called fields {Typc CR} and {Type of report?} while addressing the same field.

[alternaz]

Hello @Andrey_Kovalev,

Thanks for your help.

I think I made a mistake in my message during the translation of fields names.

This is what my formula looks like at the moment (with no errors this time):

SWITCH( {Type of package},
"half day", SWITCH( {Type of report},
"very synthetic", "10 €",
"abbreviated summary", "20 €",
"standard synthesis", "30 €",
"exhaustive", "40 €"
),
"full day", SWITCH( {Type of report},
"very synthetic", "50 €",
"abbreviated summary", "60 €",
"standard synthesis", "70 €",
"exhaustive", "80 €"
)
)

I add a classic price table to give the maximum amount of informations:

x	Half day price	Full day price
very synthetic	10 €	50 €
abbreviated summary	20 €	60 €
standard synthesis	30 €	70 €
exhaustive	40 €	80€

Have you some idea ?

Cheers

[Andrey_Kovalev]

@alternazsorry, I do not see any errors, it works for me. Maybe, you should check the spelling...

[alternaz]

I really don't understand why it doesn't works.

[Andrey_Kovalev]

@alternazYour table says "Half day price", but in the script "half day". Are you sure they match each other? Also, try removing white spaces temporarily to investigate the problem.