Can I bother you one more time for "Calc" help?

[Rangoon]

I currently have a Number field called

[CobaltSky]

If I understand you correctly, you want either type of discount to nevertheless cause a discount amount to display in the "PromotionDiscount" field. If that's the case, then I suggest you change the 'PromotionDiscount' calculation formula to something along the lines of:

If(Status(CurrentDate)<= PromoStopDate, Case(IsEmpty(LbDiscountCalc), CaseDiscount, LbDiscountCalc),"")

---

That way, so long as you are confident that the two fields 'LbDiscountCalc' and 'CaseDiscount' will never both hold a value at the same time, this should work for you.

If there is any possibility of user error which might result in data entry in both fields, you might like to consider preventing it by a calculation validation on the relevant data entry fields. To do this, you would enter the formula IsEmpty(LbDiscountCalc) as the validation formula for the 'CaseDiscount' field, and IsEmpty(CaseDiscount) as the validation formula for the 'LbDiscount' field.

[Rangoon]

Ray,

Thanks for getting back to me so quickly, but I do have a problem.

Per your post I changed the "PromotionDiscount" field to;

If(Status(CurrentDate) <= PromoStopDate, Case(IsEmpty(LbDiscountCalc),CaseDiscount,LbDiscountCalc),"")

If I enter .10 cents into the "LbDiscount" field (example: 12 lb. case * .10 cents), the "PromotionDiscount" field reads $1.20 as it should. But if I clear the "LbDiscount" field and enter $1.20 into the "CaseDiscount" field, it does not show in the "PromotionDiscount" field. It will only show what is entered into the "LbDiscount" field.

Have I done something wrong with the calc?

Thanks Ray...

[CobaltSky]

Hi,

The formula you're using looks ok, so I it would seem that the problem may result from either of two things.

If the calculation result is set to be stored, it may not be updating as quickly as you'd like. If you set it to 'unstored' (using the 'storage options button at the lower right of the calculation options dialog...) it should update as soon as you leave the field after making the change.

Failing that, the other possiblity is that the LbDiscountCalc formula is not producing a null result (this may be helped if you turn off the 'Do not evaluate if all referenced fields are empty option for that calc). If the problem is in this quarter, you should be able to work around it (if you can't readily correct it any other way, by reversing the syntax of the formula for the "PromotionDiscount", so that it runs:

If(Status(CurrentDate) <= PromoStopDate, Case(IsEmpty(CaseDiscount), LbDiscountCalc, CaseDiscount), "")

---

Give the above suggestions a try and hopefully you'll be in business...

[Rangoon]

Ray,

Your new calculation works perfectly. However, I sent you the wrong calculation, don't ask me how but I did. Here is the correct one.

If(Status(CurrentDate) <= PromoStopDate and Status(CurrentDate) >= PromoStartDate,Discount,"")

How do I apply the new calculation you just sent me to the above. I tried a couple ways and it didn't work so I know I'm messing up somewhere.

Thanks Ray

[CobaltSky]

Hi there,

This ought to do it for you:

If(Status(CurrentDate) <= PromoStopDate and Status(CurrentDate) >= PromoStartDate, Case(IsEmpty(CaseDiscount), LbDiscountCalc, CaseDiscount), "")

---

Cheers,

[Rangoon]

Thanks Ray, that calc is shear perfection and thanks for the heads up Re: "If there is any possibility of user error which might result in data entry in both fields". You know something I hadn't thought of yet and that is somebody is going to try and enter numbers into both case and pound fields. Thanks for taking the time to think through what I didn't know enough to ask for! I appreciate your doing that..

Gooday Mate...