Help With A Case Function 1

[ehope1]

I have a sales tax field that is currently calculated:

Case (ShipState="WA" or ShipState="WASHINGTON" ;Round(( SubTotal+Discount+Shipping) * .084;2); BillState="WA" or BillState="WASHINGTON" ; Round (( SubTotal+Discount+Shipping) * .084;2);0)

On Sept 1 the business moved to another county and the tax rate has changed to .086. I want to have the formala calculate based on .084 if before 9/1 and .086 if 9/1 or after.

I've tried this calculation but it isn't working:
Case (ShipState="WA" or ShipState="WASHINGTON" & InvoiceDate ? 8/31/2006;Round(( SubTotal+Discount+Shipping) * .084;2); ShipState="WA" or ShipState="WASHINGTON" & InvoiceDate ? 9/1/2006 ;Round(( SubTotal+Discount+Shipping) * .086;2); BillState="WA" or BillState="WASHINGTON" & InvoiceDate ? 8/31/2006; Round (( SubTotal+Discount+Shipping) * .084;2); BillState="WA" or BillState="WASHINGTON" & InvoiceDate ? 9/1/2006; Round (( SubTotal+Discount+Shipping) * .086;2); 0)

Can anyone help me make this work?

Thank you!

 

[JeanW]

1. Which version of FM do you use ?

2. "&" is a text operator inside a calculation to concatenate, not to separate cinditions.
Use AND instead.

 

[ehope1]

JeanW thank you for your response. I am using FMP 7.0 and I have tried this with AND instead of & and it still doesn't work.

 

[JeanW]

I always try to avoid unnecessary combination in calculation.
Since you're on 7, lets look at your coding first.
You hardcode several times ShipState with a value and BillState with a value.
These values seem to be the same for both fields.
Try to have 1 value for those fields in your calc, with other words, make the decision outside the calc, with one field, something like:
State = Left(yourField;2).
This will give you 1 steady value.

Next you hardcode SubTotal+Discount+Shipping.
Use the power of FM 7 here, by using the Let function in your calc

Let( amount = SubTotal+Discount+Shipping;
Case(
State = “WA” and InvoiceDate <= GetAsDate(“8/31/2006”);
Round(( amount) * .084;2);
etc….
0
)

 

[JeanW]

Also, your new will be default for the new location and taxvalue.
No need to repeat the whole story in your calc.

Make only a difference if the invoicedate is before 9/1.
All the rest will be default value.

 

[ehope1]

Jean thanks so much for your help. I didn't understand what you were trying to tell me in the last message. This is what I have so far:

Let(amount=SubTotal+Discount+Shipping;
Let(State=Left(ShipState;2);
Case (State="WA" and InvoiceDate <=GetAsDate(" 8/31/2006");Round((amount) * .084;2);
State="WA" and InvoiceDate >= GetAsDate(" 9/1/2006"); Round (( amount) * .086;2); 0)))

This works if the Ship State is WA but I also need it to work if the Bill State is WA ...and they aren't always the same state. I've tried a lot of different combinations in the Let statement to make it an either or situation...but not successfully....

 

[JeanW]

Hmmm, my mind was faster than my fingers....sorry..

...your new RECORDS will be default for the new location and taxvalue.

The 'ship' and 'bill' state calc has to be outside your tax calc.
For this you need a new calc field where you will determine what the value is for the ship and billstate.

If I read your requirements well, you need the taxvalue of .086 (I do not consider the date now)if the shipstate is WA OR the billstate is WA.

Your condition is when one of those two fields is WA, you need .086.

Make a calc field state_ct, where you determine the value, something along these lines:

Case(
Left(billState;2) = "WA";"WA";
Left(shipState;2) = "WA";"WA"
)

This will set the field 'state_ct' to WA if ship = WA (or WASHINGTON) or bill = WA (or WASHINGTON)or both = WA (or WASHINGTON).

Use now the field state_ct in your tax calc, something along these lines:

Let(amount=SubTotal+Discount+Shipping;

Case (
state_ct="WA" and InvoiceDate <=GetAsDate(" 8/31/2006");
Round((amount) * .084;2);
state_ct ="WA" and InvoiceDate >= GetAsDate(" 9/1/2006"); Round (( amount) * .086;2); 0)

 

[ehope1]

Jean -- this worked like a charm. Thank you so much!!!