Mar 06, 2021 07:20 AM
My database has run well for 3 months but now I need to change the discount structure. I have added a Column and called it Qtr and each record will be either 1,2,3,or 4 the idea being that the discount offered for that Qtr could be different.
So far I have two different discount formula but how do I add the qtr to the formula
![Screenshot 2021-03-06 at 15.11.14|700x210]
Formula
![Screenshot 2021-03-06 at 15.12.22|700x165]
or
(upload://pdKo1JReaKkSalwdO6FRbe3o0Lb.png)
(upload://hc9LcneLXO1dfccFOyXw0GxLXND.png)
Solved! Go to Solution.
Mar 09, 2021 11:41 AM
I know I had to be spoon fed but I can’t tell you how much I appreciate the time and effort you have put in with me.
Naturally your last answer has saved correctly and even calculated correctly.
You will not be surprised but I am certainly relieved.
I will read your last comments again tomorrow and try to follow the logic and fully understand what you wrote but for now I want a glass of wine and to close my eyes.
Thank you and good night. I might even be able to sleep tonight knowing the problem is solved(well at least on the test system).
Mar 06, 2021 02:35 PM
Since you’ll be comparing the same value to four known values, this seems like you want to use a SWITCH()
uses.
SWITCH(
{Qtr},
'1', {Extrachildren} * MIN(({Weeks FM/Zoom} + {WrrksSR}), 10) * 5,
'2', {Extrachildren} * MIN(({Weeks FM/Zoom} + {WrrksSR}), 10) * 3,
'3', ...,
'4', ...
)
Mar 07, 2021 01:52 AM
Thank you so much for what would appear to be a logical solution (that I would never have found) unfortunately it will not save.
(I reduced the Qtr options just to so that I could screen shot it.)
You had made a minor typo on weeks and I had sent a version when I had changed the labels of FM & SR but correcting these and it still will not save but I cannot see why.
Here is a copy of part of the live database as of this morning.
(you are welcome to have full access to it if it helps)
Any guidance would be greatly appreciated as I have no idea what I am doing wrong.
Many thanks in advance for your reply.
Phil
Mar 08, 2021 08:46 AM
Hi Kamille,
I have tried marginally altering your suggestion a number of ways but I still can’t get it to save even when I tried it as just the 1 option.
Can you please tell me what I am doing wrong as I just can’t get it to work.
I fully expect the answer to be a simple one but I have no idea as to what needs to change.
Many thanks
Phil
Mar 08, 2021 09:17 AM
I don’t see anything wrong with the syntax. Try wrapping the calculation bits in parenthesis.
({Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 10) * 5)
Otherwise test out the formula including only portions of that^ calculation in to see if it works. (Does it work if you just have {Extrachildren}
? Does it work if you just have the MIN()
?)
Mar 08, 2021 11:37 AM
This is the first thing that I have done that has saved SWITCH( {Qtr},({Extrachildren} ))
Added 1 but it didn’t save. SWITCH( {Qtr}1,({Extrachildren} ))
Saved SWITCH( {Qtr},1,({Extrachildren} ))
Failed SWITCH( {Qtr},1,({Extrachildren}* MIN ))
Saved SWITCH( {Qtr},1,({Extrachildren} *MIN))
Failed SWITCH( {Qtr},1,({Extrachildren} *MIN(({Weeks FM}+{WeeksSR}), 10) * 5,))
))
Failed SWITCH( {Qtr},1,({Extrachildren} *MIN(({Weeks FM}+{WeeksSR}), 10) * 5,))
Failed SWITCH( {Qtr},1,({Extrachildren} *MIN))(({Weeks FM}+{WeeksSR}), 10) * 5,)))
Failed SWITCH( {Qtr},1,({Extrachildren} *MIN(({Weeks FM}+{WeeksSR}), 10) * 5
Failed SWITCH( {Qtr},1,({Extrachildren} *MIN(({WeeksFM}+{WeeksSR}), 10) * 5)
On the positive 2 things have saved.
Jumping at straws I know but my commas are not as pronounced as yours, could this have anything to do with it?
Very happy to give you access to this database once I have deleted emails and phone numbers
Really do appreciate your help with this.
Phil
Would I add you but I don’t have your email
Mar 08, 2021 01:17 PM
Looking at the alignment of the data in your columns, it looks like {Weeks FM}
does not report as a number (noticed how it aligns to the left link string, not to the right like a number).
I believe you’re getting errors because you are trying to perform math on something that is technically not a “number”. Try VALUE({Weeks FM}) + VALUE({WeeksSR})
Mar 08, 2021 02:30 PM
You are correct as the Weeks FM has a formula to take numbers from a text field.(for a reason that I can’t even remember, but it has been working.)
I have assumed your instructions of adding the word VALUE was to the Syntax that you sent me, so I tried it but it didn’t save.
It is likely that I have a bracket in the wrong place but I regret to being too old to stay up through the night trying different combinations.
I will try in the morning but if you see anything obvious that I have done wrong I would appreciate your guidance.
Thank you once again for your input.
Phil
Mar 08, 2021 02:37 PM
Your MIN()
function is missing the beginning parenthesis., and you have a random comma after the 5.
Mar 08, 2021 10:21 PM
Thanks so much for your continued help and I am sorry for my ignorance.
Comma has gone.
I have put the ( in front of MIN and tried it without the ) and then putting the ) in every position I can think of but it is still not saving.
Do you have any other ideas I could try?
Many Many thanks for all your help.
Phil
Mar 08, 2021 11:49 PM
In that screenshot, you still are missing the opening parenthesis for MIN(). You wrote the word MIN and then added a space and then started another function.
Mar 09, 2021 12:37 AM
Will I ever get this correct?
As you can see I have tried a number of minor changes but all are wrong and will not save.
I keep reading your note but I am afraid it is not clear to me as clearly I am not understanding something that I guess is obvious to you.
Would you mind typing the complete change I should make.
Once again thank you for helping me.
Phil
Mar 09, 2021 07:30 AM
Now mid afternoon and I have the following formula that does save.
SWITCH( {Qtr},1,({Extrachildren}* MIN(({Weeks FM}+{WeeksSR}), 10) * 5))
Unfortunately that is all it does.
The column is completely blank.
In theory I would have thought that as I am comparing the test system current Qtr with the live Current Qtr, then the results would be the same but as I said there is nothing now not even a £0
There is no point testing other qtrs until at least this one gives the same results.
Mar 09, 2021 07:34 AM
Unfortunately and very frustratingly, the formula box in Airtable only allows straight quotation marks, not stylized quotation marks (i.e. curly quotation marks).
Note that your quotation marks are stylized instead of being straight.
Mar 09, 2021 07:42 AM
Thanks Scott. On my last effort I had missed the quotation marks completely so your suggestion was both timely and correct.
Mar 09, 2021 08:16 AM
At last the first of the SWITCH calculations is working.
I have now tried to enter the second of the discount structures but it doesn’t save so yet again I am doing something wrong but I have no idea as to what.
Is there anybody who can point out the error of my ways!!
Mar 09, 2021 08:28 AM
You need to pay closer attention to your parentheses. Once again, you’re missing an opening parentheses. Since there are two closing parentheses after the * 3
, then there should be one opening parentheses in front of the beginning of that argument, which is {Extrachildren}
.
Parentheses have two purposes in formulas
TODAY()
. All other functions, including MIN()
require you to enter some sort of argument between the parantheses (i.e. what are you trying to find the minimum between?) and you ALWAYS must have an opening and closing parantheses for them in the style of NAME_OF_FUNCTION()
. This applies to no-argument functions like TODAY()
.The reason I haven’t written out the full correct formula for you is because I’m trying to force you to work through the basics. If there is something specific about formula syntax that isn’t clicking, please state it so I can explain.
Mar 09, 2021 09:32 AM
I do really appreciate how you are trying to teach me. I am just aware how much of your time I am taking when it is unlikely at 73 I will ever do another database, I certainly will not say I can help my niece again!!! Thats not strictly true but I am getting frustrated.
After getting the Qtr 1 to work I had two closing parenthesis.
Onto Qtr2.
Looking at your previous examples it appeared I needed to do away with those closing parenthesis and put in a coma as the formula was to move onto the Qtr 2. I have now tried adding a parenthesis before Extrachildren (as instructed) so my logic says that I need to add 3 closing parenthesis (2 from the 1st qtr and one from the one I added before Extrachildren. Clearly I am wrong as it doesn’t save.
But why?
And why does the parenthesis become before Extrachildren and not before ‘2’
Mar 09, 2021 10:13 AM
Here is the pattern that all SWITCH()
functions follow.
SWITCH(
[the value you're trying to test],
[the 1st possible value], [what to insert if the 1st value is true],
[the 2nd possible value], [what to insert if the 2nd value is true],
[the 3rd possible value], [what to insert if the 3rd value is true],
...
[optional, one last 'what to insert' if none of the other values are true]
)
To answer your’ question, ‘2’ is your “the 2nd possible value”, and {Extrachildren}… is the “what to insert if the 2nd value is true”. If you put the parenthesis comes before ‘2’, you are grouping both “value” and “what to insert”. Those two need to be separate arguments for this function.
So, walking through your use case, here’s the structure:
SWITCH(
{Qtr},
"1", (insert x1),
"2", (insert x2)
)
x1 == {Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 10) * 5
x2 == {Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 11) * 3
Again, you don’t have to wrap “x1” or “x2” within parenthesis, but if you did want to, your formula should be:
SWITCH(
{Qtr},
"1", ({Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 10) * 5),
"2", ({Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 11) * 3)
)
If you didn’t want the wrapped parenthesis, it would be:
SWITCH(
{Qtr},
"1", {Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 10) * 5,
"2", {Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 11) * 3
)
Mar 09, 2021 11:41 AM
I know I had to be spoon fed but I can’t tell you how much I appreciate the time and effort you have put in with me.
Naturally your last answer has saved correctly and even calculated correctly.
You will not be surprised but I am certainly relieved.
I will read your last comments again tomorrow and try to follow the logic and fully understand what you wrote but for now I want a glass of wine and to close my eyes.
Thank you and good night. I might even be able to sleep tonight knowing the problem is solved(well at least on the test system).