Calculation based on 3 fields

[Koonce]

I have a calculation based on three field.

I have a state field that lists the states.

I have a Type record field that list different type records.

I have a numeric field that show the amount of money.

Sample

Case ( State = "Texas" and Type = "RI" and Tax > 0; "01-03811";"";

State = "New Mexico" and Type = "RI" and Tax > 0; "02-03812";"";

State = "Oklahoma" and Type = "RI" and Tax > 0; "03-03813";"";

State = "Louisiana" and Type = "RI" and Tax > 0; "04-03814";"";

State = "Arkansas" and Type = "RI" and Tax > 0; "05-03815";"";

State = "Alaska" and Type = "RI" and Tax > 0; "09-03819";"";

State = "Kansas" and Type = "RI" and Tax > 0; "06-03816";"";

State = "Colorado" and Type = "RI" and Tax > 0; "07-03817";"";

State = "Missisippi" and Type = "RI" and Tax > 0; "08-03818";"";

State = "Texas" and Type = "WI" and Tax > 0; "01-05011";"";

State = "New Mexico" and Type = "WI" and Tax > 0; "02-05012";"";

State = "Oklahoma" and Type = "WI" and Tax > 0; "02-05013";"";

State = "Louisiana" and Type = "WI" and Tax > 0; "04-05014";""

)

This is my calc it looks good but it only list a few of the calculations and does not list others.

Can someone help

thanks in advance.

[Raybaudi]

The example above can be stripped with nested Case, like this:

Case( Tax > 0;

Case(

State = "Texas" and Type = "RI" ; "01-03811";

State = "New Mexico" and Type = "RI" ; "02-03812";

State = "Oklahoma" and Type = "RI" ; "03-03813";

State = "Louisiana" and Type = "RI" ; "04-03814";

State = "Arkansas" and Type = "RI" ; "05-03815";

State = "Alaska" and Type = "RI" ; "09-03819";

State = "Kansas" and Type = "RI" ; "06-03816";

State = "Colorado" and Type = "RI"; "07-03817";

State = "Missisippi" and Type = "RI"; "08-03818";

State = "Texas" and Type = "WI" ; "01-05011";

State = "New Mexico" and Type = "WI"; "02-05012";

State = "Oklahoma" and Type = "WI" ; "02-05013";

State = "Louisiana" and Type = "WI"; "04-05014"

);

""

)

More nested Case() or If() function can be used, for example for Type ="RI"

[Razumovsky]

Sounds like this data would be better stored as individual records in a separate StateType table.

Not really sure what the 'others' are, but your existing calc could be simplified:

Case(

    Tax > 0;

    Case(

        Type = "RI";

        Case(

            State = "Texas";

            "01-03811";

            State = "New Mexico";

            "02-03812";

            State = NextRIState;

            NextResult...

        );

        Type = "WI";

        Case(

            State = "Texas";

            "01-05011";

            State = NextWIState;

            NextWIResult...

        )

    )

)

-Raz

*Guess I had opened this one an hour ago. sorry Daniele, did not see your reply.

Edited June 30, 2006 by Guest
put in code box

[Fitch]

Another option (the dots are just for readability; remove them from the actual calc)

Let( it = State & " " & Type ;

Case( Tax > 0;

. Case(

. . it = "Texas RI" ; "01-03811" ;

. . it = "New Mexico RI" ; "02-03812";

. . it = "Oklahoma RI" ; "03-03813";

. . it = "Louisiana RI" ; "04-03814";

. . it = "Arkansas RI" ; "05-03815";

. . it = "Alaska RI" ; "09-03819";

. . it = "Kansas RI" ; "06-03816";

. . it = "Colorado RI" ; "07-03817";

. . it = "Missisippi RI" ; "08-03818" ;

. . it = "Texas WI" ; "01-05011";

. . it = "New Mexico WI" ; "02-05012";

. . it = "Oklahoma WI" ; "02-05013";

. . it = "Louisiana WI" ; "04-05014" )

. )

)

[Razumovsky]

All together now...!

Nice use of dots Fitch, it would be great if there was a way to keep the formating of calcs in posts.

[Razumovsky]

And another (not as fast, but it is nice to have options...)

let(list=

"Texas RI¶

01-03811¶

New Mexico RI¶

02-03812¶

Oklahoma RI¶

03-03813...";

pos=position("¶"&list&"¶"; "¶"&State & " " & Type&"¶");

val=ValueCount( Left( list; pos))];

case(val>0; getValue(list; val+1))

)

Edited June 30, 2006 by Guest
fixed calc typo

[Lee Smith]

it would be great if there was a way to keep the formating of calcs in posts.

You can, use the Code button.

Edited June 30, 2006 by Guest

[Razumovsky]

Thanks Lee!

[Fitch]

I don't like the way the Code double-spaces the lines, though.

[Lee Smith]

I don't liked the way the Code Feature/Button handles the length of the text either.

I'm hoping that the upgrade that Stephen said was coming, will change this function to allow regular spaces or tabs for indent, or allow the use the [color:blue]Option + Space to indent, like we use to be able to do, prior to the change to the current Web provider.

Lee

[mdpres]

Koonce,

Are you working on a Road & Fuel solution, or something similar, If you are, contact me I have a complete Road & Fuel Solution.

Dean