rate selection by date

[pc302]

I hope the this is posted to the right forum

I would like to look at the customer master and select a rate based on a date I have four start and stop dates and four application rates defined.

I setup a calc. for the new record as follows.

Case

(Customers::Cust_AppDate1Start ≥ Oe_PlacementDate and Customers::Cust_AppDate1End ≤ Oe_PlacementDate;1 ; Customers::Cust_AppRate1;

Customers::Cust_AppDate2Start ≥ Oe_PlacementDate and Customers::Cust_AppDate2End ≤ Oe_PlacementDate;1 ; Customers::Cust_AppRate2;

Customers::Cust_AppDate3Start ≥ Oe_PlacementDate and Customers::Cust_AppDate3End ≤ Oe_PlacementDate;1 ; Customers::Cust_AppRate3;

Customers::Cust_AppDate4Start ≥ Oe_PlacementDate and Customers::Cust_AppDate4End ≤ Oe_PlacementDate;1; Customers::Cust_AppRate4

)

when I have only the first test it works.

Maybe Choose is the answer or revising the case statement or a script with get related record to look at the customer master for that customers application rate.

any advice would be appreciated.

[PamRotella]

[color:brown]The "Case" function should work for this type of formula, as long as you have the statements in the preferred order of results (should there be overlap). But have you looked at the logic of this calculation carefully? To me, it looks like you're comparing start/end and placement dates, with a result of changes in rates.

If I'm reading the naming conventions right, your formula seems to be saying that the start date is later than/equal to the placement date, and the end date is earlier than/equal to the placement date. Long story short, you may have your sign directions reversed.

An easy way of troubleshooting this would be with a few test records yielding simple, obvious formula results. Play with the formula until you get the obvious answers you'd expect, then figure out what went wrong in the original.

Again, I can't be sure of this -- I'm just guessing from the names of the fields. But it's worth another look.

[color:gray]Case

(Customers::Cust_AppDate1 Start ≥ Oe_PlacementDate and Customers::Cust_AppDate1E nd ≤ Oe_PlacementDate;1 ; Customers::Cust_AppRate1;

Customers::Cust_AppDate2S tart ≥ Oe_PlacementDate and Customers::Cust_AppDate2E nd ≤ Oe_PlacementDate;1 ; Customers::Cust_AppRate2;

Customers::Cust_AppDate3S tart ≥ Oe_PlacementDate and Customers::Cust_AppDate3E nd ≤ Oe_PlacementDate;1 ; Customers::Cust_AppRate3;

Customers::Cust_AppDate4S tart ≥ Oe_PlacementDate and Customers::Cust_AppDate4E nd ≤ Oe_PlacementDate;1; Customers::Cust_AppRate4

)

[pc302]

you were check thank you pam the final code looks like this:

Case

(Customers::Cust_AppDate1Start ≤ Oe_PlacementDate and Customers::Cust_AppDate1End ≥ Oe_PlacementDate;Customers::Cust_AppRate1;

Customers::Cust_AppDate2Start ≤ Oe_PlacementDate and Customers::Cust_AppDate2End ≥ Oe_PlacementDate; Customers::Cust_AppRate2;

Customers::Cust_AppDate3Start ≤ Oe_PlacementDate and Customers::Cust_AppDate3End ≥ Oe_PlacementDate; Customers::Cust_AppRate3;

Customers::Cust_AppDate4Start ≤ Oe_PlacementDate and Customers::Cust_AppDate4End ≥ Oe_PlacementDate; Customers::Cust_AppRate4)

Thanks once again