fmp3 to fmp8 nested case function broke

[hooty]

Case(GetAsDate (QuoteDate) ≥ Date(10;3;2005);

(Case(ExemptCertificate = "Taxable"; .08375;

ExemptCertificate="Westchester"; .07375;

ExemptCertificate="Suffolk";.08625; ExemptCertificate="Nassau"; .08625; 0));

Invoice::Date ≥ Date(10;3;2005); (Case(ExemptCertificate = "Taxable";

.08375; ExemptCertificate="Westchester"; .07375;

ExemptCertificate="Suffolk";.08625; ExemptCertificate="Nassau"; .08625; 0));

Case(GetAsDate (QuoteDate) ≥ Date(6;4;2003);

(Case(ExemptCertificate = "Taxable"; .08625;

ExemptCertificate="Westchester"; .07;

ExemptCertificate="Suffolk";.085; ExemptCertificate="Nassau"; .0875; 0));

Invoice::Date ≥ Date(6;4;2003); (Case(ExemptCertificate = "Taxable";

.08625; ExemptCertificate="Westchester"; .07;

ExemptCertificate="Suffolk";.085; ExemptCertificate="Nassau"; .0875; 0));

(Case(ExemptCertificate = "Taxable"; .0825; ExemptCertificate="Westchester";

.0675; ExemptCertificate="Suffolk";.0825; ExemptCertificate="Nassau"; .085;

ExemptCertificate = "On File";0; ExemptCertificate = "Other Exempt";0;

ExemptCertificate = "CCI";0; ExemptCertificate = "Texas";.0825;

ExemptCertificate = "Resale"; 0; ExemptCertificate = "Out of State"; 0;0))))

This is the original TaxRate Field calculation that worked to set seperate tax rates base on dates and exempt status. Seems to get stopped at the first case, marking all records as though they are 2005 and later.

I just let fmp8 do the import and can't seem to see what broke.

Help please? and thank you.

Edited January 16, 2006 by Guest

[Søren Dyhr]

Try to use the dafault values in this:

Case(ExemptCertificate = "Taxable"; .08375;

ExemptCertificate="Westchester"; .07375;

ExemptCertificate="Suffolk";.08625; ExemptCertificate="Nassau"; .08625; 0))

Suffolk and Nassua shares percentage, and then are you repeating chunks of code that ought to be gathered in Let( statements .... by doin it will the calc become much cleaner to debug.

One thing that strikes me though is that you mix entities, why mix "Taxable" and countynames?

--sd

[hooty]

this isn't my original calculation. I'm a moderate FMP user and tend to avoid the calcs unless spelled out for me.

I'm simply upgrading an existing database for a company and it broke.

[hooty]

Taxable, sulfolk and nassau are choices in the records for how to figure the tax. If it's just taxable then it's standard NYC tax. If it's the other counties then the tax rate changes.

On top of that, the first case statement in each nest is suppose to narrow down the dates as well so that all the records after a certain date will be marked with the different tax rates.

the company has been simply adding more case statements in FMP3 to change current a forward issues.

HEY! Does this mean the top case statement is the current and the fields were set not to update if full of data and the import just screwed that up? So all are set to the most recent?

Thanks for the input.

[Raybaudi]

Hi

try this... it seems to work !

Case(GetAsDate (QuoteDate) ≥ Date(10;3;2005);

(Case(ExemptCertificate = "Taxable"; "0.08375";

ExemptCertificate="Westchester"; "0.07375";

ExemptCertificate="Suffolk";"0.08625"; ExemptCertificate="Nassau"; "0.08625"; 0));

invoice::date ≥ Date(10;3;2005); (Case(ExemptCertificate = "Taxable";

"0.08375"; ExemptCertificate="Westchester"; "0.07375";

ExemptCertificate="Suffolk";"0.08625"; ExemptCertificate="Nassau"; "0.08625"; 0));

Case(GetAsDate (QuoteDate) ≥ Date(6;4;2003);

(Case(ExemptCertificate = "Taxable"; "0.08625";

ExemptCertificate="Westchester"; "0.07";

ExemptCertificate="Suffolk";"0.085"; ExemptCertificate="Nassau"; "0.0875"; 0));

invoice::date ≥ Date(6;4;2003); (Case(ExemptCertificate = "Taxable";

"0.08625"; ExemptCertificate="Westchester"; "0.07";

ExemptCertificate="Suffolk";"0.085"; ExemptCertificate="Nassau"; "0.0875"; 0));

(Case(ExemptCertificate = "Taxable"; "0.0825"; ExemptCertificate="Westchester";

"0.0675"; ExemptCertificate="Suffolk";"0.0825"; ExemptCertificate="Nassau"; "0.085";

ExemptCertificate = "On File";0; ExemptCertificate = "Other Exempt";0;

ExemptCertificate = "CCI";0; ExemptCertificate = "Texas";"0.0825";

ExemptCertificate = "Resale"; 0; ExemptCertificate = "Out of State"; 0;0))))

[Raybaudi]

And this is the same, but in more readable format:

Case(

GetAsDate (QuoteDate) or Invoice::Date ≥ Date(10;3;2005);

Case(

ExemptCertificate = "Taxable"; "0.08375";

ExemptCertificate="Westchester"; "0.07375";

ExemptCertificate="Suffolk";"0.08625";

ExemptCertificate="Nassau"; "0.08625"; 0

);

Case(

GetAsDate (QuoteDate) or Invoice::Date ≥ Date(6;4;2003);

Case(

ExemptCertificate = "Taxable"; "0.08625";

ExemptCertificate="Westchester"; "0.07";

ExemptCertificate="Suffolk";"0.085";

ExemptCertificate="Nassau"; "0.0875"; 0

);

Case(

ExemptCertificate = "Taxable"; "0.0825";

ExemptCertificate="Westchester";"0.0675";

ExemptCertificate="Suffolk";"0.0825";

ExemptCertificate="Nassau"; "0.085";

ExemptCertificate = "On File";0;

ExemptCertificate = "Other Exempt";0;

ExemptCertificate = "CCI";0;

ExemptCertificate = "Texas";"0.0825";

ExemptCertificate = "Resale"; 0;

ExemptCertificate = "Out of State"; 0;0

)

)

)

[comment]

Why don't you post the original calc formula as well? It's hard to tell what's "not working", when we don't know what "working" means.

[Raybaudi]

Hi comment

I think that the lonely problem is the format of the number choosen by msmac...

so that I changed them from .0085 to "0.0085" (for example)

[hooty]

Thanks so much for the help. It still isn't working though. It seems to ignore the dates and simply sets everything based on the top case statements. Taxable is 8.375 on current and past records.

It is the original calc formula. It will not set the taxrate as 8.25 for records older than 6-3-2004.

It will not set the taxrate at 8.63 for records between 6-4-2004 and 10-2-2005.

It will only set all taxrates as 8.37. (Under the "taxable" status. same behavior for the counties as well.)

[Raybaudi]

Ok, I maded an error...

try this one:

Case(

GetAsDate (QuoteDate) ≥ Date(10;3;2005) or Invoice::Date ≥ Date(10;3;2005);

Case(

ExemptCertificate = "Taxable"; "0.08375";

ExemptCertificate="Westchester"; "0.07375";

ExemptCertificate="Suffolk";"0.08625";

ExemptCertificate="Nassau"; "0.08625"; 0

);

GetAsDate (QuoteDate) ≥ Date(6;4;2003) or Invoice::Date ≥ Date(6;4;2003);

Case(

ExemptCertificate = "Taxable"; "0.08625";

ExemptCertificate="Westchester"; "0.07";

ExemptCertificate="Suffolk";"0.085";

ExemptCertificate="Nassau"; "0.0875"; 0

);

ExemptCertificate = "Taxable"; "0.0825";

ExemptCertificate="Westchester";"0.0675";

ExemptCertificate="Suffolk";"0.0825";

ExemptCertificate="Nassau"; "0.085";

ExemptCertificate = "On File";0;

ExemptCertificate = "Other Exempt";0;

ExemptCertificate = "CCI";0;

ExemptCertificate = "Texas";"0.0825";

ExemptCertificate = "Resale"; 0;

ExemptCertificate = "Out of State"; 0;0

)

[hooty]

thanks for all the help : I figured it out I believe. For some reason the Invoice date was messing the whole thing up. I took out that part (which I could see in your clear setup) and the fields are updating. THANKS!!!