Converting virtual fields to numeric values for use in calcualtions

[CMorris]

I have a datapage that utilizes virtual fields to collect user input values that I don't want to store in tables, but do want to use for some generally simple calculations.  However, because virtual fields don't store the input as numeric, I keep getting calculation errors.  Here is a simple example of one calculation that I can't get to work:

 

VirtualField1 is a text box

VirtualField2 is a dropdown with the options "in" and "mm"

VirtualField3 is a calculated field with the following formula:

CASE

WHEN [@cbParamVirtual2] = 'mm' THEN [@cbParamVirtual1]/25.4

ELSE [@cbParamVirtual1]

END

 

No matter what tweaks I have tried, I either get a "calculation error" message or just nothing appears in the VirtualField3.  Any suggestions would be greatly appreciated.  Thanks!

[Vitalikssssss]

Hi @CMorris,

Values from virtual fields do not have a datatype.

You would need to wrap the fields reference with single quotes to make them string type and convert the string to float type.

You can use T-SQL function CAST or CONVERT for this.

So, your expression will look like this:

CASE

WHEN '[@cbParamVirtual2]' = 'mm' THEN CAST('[@cbParamVirtual1]' AS FLOAT)/25.4

ELSE CAST('[@cbParamVirtual1]' AS FLOAT)

END

Regards,

vitalikssssss

[Jodie]

Thank you for explaining that virtual fields do not have a datatype and that to cast it as a string you have to enclose it in single quotes. I was going crazy trying to work out why my calculated value kept saying calculation error.

[cheonsa]

Hi @Jodie,

You may also check this article for the different functions that you can use in the formula:

https://howto.caspio.com/function-reference/

 

 

[Queso]

Hello, you might refer to the following Forum post for another use-case that requires single quotes in a Calculated Field: