Help

Is this a case for an IF statement?

Topic Labels: Formulas
Solved
Jump to Solution
3097 19
cancel
Showing results for 
Search instead for 
Did you mean: 
Philip_Barber
7 - App Architect
7 - App Architect

Screenshot 2021-03-06 at 15.11.14

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

Screenshot 2021-03-06 at 15.11.55 (upload://pdKo1JReaKkSalwdO6FRbe3o0Lb.png)
(upload://hc9LcneLXO1dfccFOyXw0GxLXND.png)Screenshot 2021-03-06 at 15.12.22 Screenshot 2021-03-06 at 15.11.14

1 Solution

Accepted Solutions
Philip_Barber
7 - App Architect
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.
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).

See Solution in Thread

19 Replies 19

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

Thank you so much for what would appear to be a logical solution (that I would never have found) unfortunately it will not save.
Screenshot 2021-03-07 at 09.34.13

(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.Screenshot 2021-03-07 at 09.35.38
(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

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

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
7 - App Architect
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
Would I add you but I don’t have your email

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

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

Screenshot 2021-03-08 at 22.04.29

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.

Screenshot 2021-03-08 at 22.14.57

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

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

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.
Screenshot 2021-03-09 at 06.10.50

Do you have any other ideas I could try?

Many Many thanks for all your help.

Phil