Override help

[KLA]

I have a field with this calculation:

Case(IsEmpty(Time Billing::Monthly Commission),"",Case(IsEmpty(Override), Round(Payment Amount - Time Billing::Monthly Commission, 2),Override=0,TextToNum(""),Round(Override,2)))

It works great when i put "0" in Override but when i put in another value it shows both the new value and the Monthly Commission value on top of each other. When i use it, i want the Override field to actually override the Monthly Commission so i only see the override value - even when i type in a value other than "0".

Any suggestions?

[andygaunt]

Hi,

first you don't need to put case( ) around each option.

A case statement is different to if, in that you can have multiple scnarios for any given field.

Case(

IsEmpty(monthly commission),"",

IsEmpty(override), Round(payment amount - monthly commission, 2),

override=0,TextToNum(""),Round(override,2))

I did this in a test, and when override was empty it displayed the monthly commission.

If I entered a figure in override, it substituted the calc with this figure.

If I put 0 in the override field it set the calc to nothing.

Where exactly is the problem with this?

[Fitch]

Your calc has to test to see if there's an Override before it tests the other conditions, otherwise it's not an override!

[andygaunt]

OK,

You have three fields.

1 related monthly commission.

1 for override.

1 for the calc.

Place the monthly commission field on the bottom. Turn off allow entry to this field.

Place the override field on top of this. This has allow entry.

Then place your calc field on top of this, with a white background. This has allow entry turned off.

So when you have nothing in the override it displays payment amount - commission)

If you have 0 in the override, it displays nothing.

If you have any other figure in override it displays this only.

Is this what you wanted.

[KLA]

No, i don't want the related "Monthly Commission" there at all. It's just used for the calculation. And yes, theoretically it seems like it should show the Override Amount since it displays "" for zero but it doesn't.

[andygaunt]

ok,

we don't seem to be getting any closer here.

send me the file off list and I can then see what you are working with.

[KLA]

I can't do that. But thanks anyway.

[Fitch]

Sorry, I misread your formula a bit. Andy's got you on the right track. Take out that extra Case() statement, but I just tested it too, and it seems to work with or without it. What I meant by put the override first was that you have it at the end of your formula, whereas I'd probably have put it at the beginning, like this:

Case(

Override >= 0, Override,

Time Billing::Monthly Commission, Round(Payment Amount - Time Billing::Monthly Commission, 2)

)

If you set the Number Format to not display number if 0, this works just like your formula, but it's easier on the brain. Also, I took out the Round(Override), since I would think the point of the override is to enter the number you want, so why does it need rounding?

[KLA]

Thanks for the response -

Andy - i took out the second "Case" but it didn't seem to matter. I don't know how you set up your test but I have both fields overlaying each other and the override only moves to the front if I type in zero. If i put in another value, both that value and the original Monthly Commission appear on top of each other.

And Fitch - i don't get what you are saying. I've revised this several times with the Forum's help and no one has had me test for an override. How do i do this?

[KLA]

Thanks Fitch -

I have to have the IsEmpty,"", part because even if Monthly Commission is empty the calcuation still calculates and gives me a negative number. I want it to only calculate if BOTH fields have numbers. But since this doesn't happen, the "" works just fine.

So, I'll do:

Case(

Override>=0,Override,IsEmpty(TB::Monthly Commission),"",IsEmpty(Override),Round(Payment Amount - TB::Monthly Commission,2),Override=0,TextToNum("")

)

?? Is this right?

[Kurt Knippel]

code:

---

Case(

Override >= 0, Override,

IsEmpty(TB::MonthlyCommission), "",

IsEmpty(Override),

Round(Payment Amount - TB::Monthly Commission,2),

Override=0, TextToNum("")

)

Dear GAWD this is nasty. First of all you have way to many differing conditions going on here. SIMPLIFY!

code:

---

Case(

Override >= 0, Override, cMonthlyCommissionTotal

)

This cleans up the override calc much better. This is ultimately what you will display to the end user. This will then show either one field or the other. Set this field to not allow user entry as they cannot change it anyway.

Then setup another field called cMonthlyCommissionTotal as below:

code:

---

Round(Payment Amount - TB::Monthly Commission,2)

The above ONLY needs to calculate the value of the commission, the other calc handles all of the display.

[Fitch]

CaptK -- simplify? Your formula is exactly the same as mine, except you added another field! The only difference is that your formula doesn't do what KLA asked, which is to return nothing when TB::Monthly Commission is empty. KLA, my formula DOES check this -- if you look at it closely, the second test of the Case statement will return "true" when there is a value in TB::Monthly Commission. Your formula adds a bunch of redundant tests.