Simple solution when table formula returns 0 instead of a decimal.

[Jodie]

Hi

So this had me baffled yesterday and it took IT support to help me out. Once they responded and explained the problem I was able to find posts in the Caspio Community with SQL calculations returning 0 instead of decimal value, but up till then I just thought I was writing the maths wrong.

I'm just adding the issue and solution again for formula tables so maybe its easier for the next person to find (or myself when I forget ) 

When using all integers, Caspio will return an integer unless a field includes decimal value. In my example, I was trying to calculate the percentage of a plan the user had completed. Completed items had the value of 1 so my formula summed all them and divided by 7. Instead of returning a decimal every value was 0. All I had to do was change 7 to 7.00.  Other options to get a decimal easy could be to multiply a value by 1.00.  I could have also changed my 1s to 1.00 but I didn't realise that maths on integers didn't quite work the same as Excel! 

 

[Kronos]

Hi @Jodie,

Thank you for that information!

I also have a workflow where I use a formula to pull values from my integer fields to calculate and put them into a number field. What I did was to use the Cast/ Convert SQL functions to automatically convert them to a Float data type that is compatible with the Number DataType of Caspio.

 

If anyone needs it, here are the two functions you can use:

CAST([@field:] as FLOAT)

CONVERT(FLOAT, [@field:])

[Kronos]

To add, they also have an article that has a list of functions that can be used in manipulating data values:

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

[cheonsa]

Hello!

In addition to the previous responses, you can also add .0 to the constant value.

For example, if you are going to divide the SUM of records into 10, you can use:

(SELECT SUM(FIELD) FROM TABLE) / 10.0