Calculation error despite valid formula

[richs]

I'm building a calculator where people can figure out how much in taxes they would own on their home in Connecticut under a new tax rate. I'm usisng the submission form datapage wizard for this.

I have a dropdown for selecting a town,  where I used a lookup table where the display is the town name and the value is the mill rate, which is  in an adjacent column in the table.  I tested this with a calculated field to just [@field:town] and in the preview it outputs as a the correct mill rate.

The next part of the form is a text field where the user enters their home value for example $300,000.

Then I have another calculated field which is [@field:homevalue]*[@field:town]/1000

Caspio tells me this is a valid formula, however, when I go to preview i get a "calculation error" message. 

[sandy159]

Hello @richs,

I believe you may have this problem because [@field:town] is the text value. You may get the value of the Mill_rate from your lookup table with the following formula so it will be the Number type:
SELECT [@field:Mill_rate] FROM Town_lookup WHERE Town=target.[@field:Town]
And then you can have another calculated value which is [@field:House_value]*[@field:Mill_rate]/1000
Just change the names of the fields to yours. 

Hope this helps!

[richs]

Thanks so much! That solved my issue. 

[PotatoMato]

Hi, just to update this post. You may also check this how-to article regarding the causes of 'error in formula' message: https://howto.caspio.com/troubleshooting/errors-and-messages/

[cheonsa]

Just to add, you can also check this function reference article: https://howto.caspio.com/function-reference/