Is this a case for an IF statement?

[Philip_Barber]

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)

[Kamille_Parks]

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', ...
)

[Philip_Barber]

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

[Philip_Barber]

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

[Kamille_Parks]

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()?)

[Philip_Barber]

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

[Kamille_Parks]

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})

[Philip_Barber]

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

[Kamille_Parks]

Your MIN() function is missing the beginning parenthesis., and you have a random comma after the 5.

[Philip_Barber]

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