# Is this a case for an IF statement?

Topic Labels: Formulas
Solved
3097 19
cancel
Showing results for
Did you mean:
7 - App Architect

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

1 Solution

Accepted Solutions
7 - App Architect

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

19 Replies 19
16 - Uranus

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

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.

Phil

7 - App Architect

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

16 - Uranus

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

7 - App Architect

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

16 - Uranus

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

7 - App Architect

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

16 - Uranus

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

7 - App Architect

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