Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 04, 2022 06:14 AM
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
Solved! Go to Solution.
Dec 04, 2022 12:16 PM
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
)
)
Dec 04, 2022 08:44 AM
@Solyhave you an idea ?
Dec 04, 2022 12:16 PM
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
)
)
Dec 04, 2022 10:49 PM
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
Dec 05, 2022 10:39 PM
Have you some idea @Andrey_Kovalev or @kuovonne ?
Dec 05, 2022 10:48 PM
@alternazI can see an error in your script which shows differently called fields {Typc CR} and {Type of report?} while addressing the same field.
Dec 05, 2022 11:44 PM
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
Dec 06, 2022 12:24 AM
@alternazsorry, I do not see any errors, it works for me. Maybe, you should check the spelling...
Dec 06, 2022 01:34 AM
I really don't understand why it doesn't works.
Dec 06, 2022 01:46 AM
@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.