FORMAT() Not Working in Calculated Field

[mohamed]

I am trying to use the following FORMAT() function to format the number to have a 'Thousands' comma (e.g., 20000 --> 20,000)
but for some reason it does not do anything when used in Calculated Field (field name REMAINING), i.e., the number calculated or result is not formatted !!

For instance, if I enter 30000 in Estimated_Cost and 10000 I get the total in 20000 (not 20,000)

While the same command runs successfully in PHPMyAdmin


SELECT FORMAT( ( COALESCE(Estimated_Cost, 0) - COALESCE(Actual_Cost, 0) ), 0)
FROM Network_2022
WHERE Network_2022.id = '%ID%'


Any Clue ?

[pfrumkin]

Hi Mohamed,

I do not understand from your description what you are actually doing. My best guess, this is a display issue. Have you tried to select the best format (like Thousands separator, no decimal) on the Data Format tab for your calculated field in AppGini?

~Paul

[mohamed]

Hi Paul

I cannot use Data Format because it changes the Field to Varchar or Text, while I need the Calculated Field to be Integer, to be able to use
Show Column Sum option and other calculation processes.

[jsetzer]

What is the datatype? If you convert a number to a string by using SQL format functions this can not be stored in a numeric field. So you have to decide if you need the numeric value or a language dependent string presentation of the value. You may consider adding a second field: keep the numeric field for calculations and filtering, sorting etc. and add a new field with calculated string presentation of the numeric value. Just as an idea.

[mohamed]

Hi Jan,

Many Thanks for your tip - excellent idea ...
and your post clicked something that the FORMAT() changes the datatype, that I did not realize when I was reading about !!


Sorry PAUL, I did not get it from your first post. <-- Thank you too..