Define a field based on another field

[Matt Leach]

Im having a difficult time with a calculation on a field.

I have one field that can have 3 possible results based on another field. For example, lets say i have two fields, A and B.

B is going to need a calculation that goes something like:

If A = result1, set field B to whatever

If A = result2, set field B to whatever

If A = result3, set field B to whatever

Ive tried creating a calculation based on the Case() function but im not sure that is going to do what i need. Here is an example of my script:

Case (

TaxType = "No Tax";

ExportTax = "0.00000";



TaxType = "State Sales Tax"

ExportTax = CombinedSalesTax;



TaxType = "State Use Tax"

ExportTax = CombinedUseTax

)

This calculation sets the ExportTax field on all records to 0.00000

Is there such a function that will set the ExportTax field correctly based on the TaxType field?

Thanks

[Søren Dyhr]

You are probably just making a typecast, the comparison should be text/text and not as I assume text/num :

--sd

[Matt Leach]

Even if i remove the first line and perform strictly text to text it still does not function properly:

Case (

TaxType = "State Sales Tax"

ExportTax = CombinedSalesTax;



TaxType = "State Use Tax"

ExportTax = CombinedUseTax

)

It leaves the exporttax field empty for those records whose taxtype is NoTax, all other records have 0.00000

[comment]

Try:

Case (

TaxType = "No Tax";

"0.00000";



TaxType = "State Sales Tax" ;

CombinedSalesTax ;



TaxType = "State Use Tax" ; 

CombinedUseTax

)

Note: a calculation field returns the result of a calculation - it does not "set" a field.

[Matt Leach]

Would there be a way to take the result and set a field with that result?

[Søren Dyhr]

Several - script, appelscript or autoenter!

--sd

[comment]

Would there be a way to take the result and set a field with that result?

Yes, but what would be the purpose of that?

[Matt Leach]

here is the scenario of why i would need it to do so.

When it comes to sales tax, there are 2 types of tax for each zip code 1. State Sales Tax, 2. State Use Tax. There are also states we are not required to charge tax in.

I have a field in this database for tax type. There are 3 types to choose from: state sales, state use or no tax.

State sales and state use are calculated base on city, county and state taxes. So i have a field that calculates all of the state sales taxes and all of the state use taxes. Those fields are CombinedStateSales and CombinedState Use.

To bring it all together, i have a field called ExportTax. This field defines which tax rate we need to use for that particular zipcode.

So i need the exporttax field to be dependent on the taxtype field.

If taxtype = no tax, i need the exporttax field to be 0

If taxtype = State Sales Tax, i need the exporttax field to reflect the amount that is in the CombineSalesTax field

If taxtype = State Use tax, i need the exporttax field to reflect the amount that is in the CombinedUseTax field.

So far through trial and error i cannot come up with a script or calculation to get this working in this manner.

[comment]

A calculation field should be quite sufficient for this - I see no need to set a field by script or another method.

I am not sure why the calculation isn't working for you. Make sure the exporttax field is defined as a Calculation field, NOT as a Text field with auto-entered calculated result.

[Matthew F]

A calculation field should be quite sufficient for this - I see no need to set a field by script or another method.

A calculation has the advantage of not requiring user intervention.

On the other hand tax rules can be quite complicated and change in unpredictable ways over time. It might be more difficult to create and maintain a complicated formula than a script. A script might allow a more modular and algorithmic approach, e.g. with subscripts and comments. I doubt that 'TurboTax' tries to calculate Income Tax based on one giant math formula.

[comment]

tax rules can be quite complicated

Maybe so, but such concerns would apply to the State Sales Tax and State Use Tax fields, not to the ExportTax field.

In any case, I firmly believe that a solution should never depend on a script running successfully for having correct and up-to-date data - and script triggers have not changed anything in this aspect.

[Søren Dyhr]

I firmly believe that a solution should never depend on a script running successfully for having correct and up-to-date data

So do I ... where were you, when this happened:

http://fmforums.com/forum/showpost.php?post/344766/

--sd

[Matt Leach]

Ill try to play around with the calculation today to try and get this to work correctly.

Am i on track with attempting to use the Case() function or would you recommend another function??

Thanks for all of you help thus far.

[bruceR]

Ill try to play around with the calculation today to try and get this to work correctly.

Am i on track with attempting to use the Case() function or would you recommend another function??

Thanks for all of you help thus far.

You are correct in using the case function but incorrect in your understanding of how it works and where the calculation is used. A calculation doesn't set anything; it only returns a result. You need to select your field in field definitions; set it to be a calc field; and use the calc as written by Comment.