FORMAT() Not Working in Calculated Field

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
mohamed
Veteran Member
Posts: 80
Joined: 2020-04-19 16:18

FORMAT() Not Working in Calculated Field

Post by mohamed » 2021-08-29 15:47

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 ?
THANK YOU...
AppGini Pro 24.11 -
Calendar - Search Page Maker - Summary Reports - Mass Update - DataTalk -
bizzworxx AppGini Helper JabaScript Library - bizzworxx Inline Detail-View - bizzworxx Helper Pack - AppGini Helper Packaging Tool -
Udemy course

pfrumkin
Veteran Member
Posts: 157
Joined: 2020-02-18 17:58
Location: Albuquerque, New Mexico USA

Re: FORMAT() Not Working in Calculated Field

Post by pfrumkin » 2021-08-30 20:41

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
Veteran Member
Posts: 80
Joined: 2020-04-19 16:18

Re: FORMAT() Not Working in Calculated Field

Post by mohamed » 2021-08-31 16:56

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.
THANK YOU...
AppGini Pro 24.11 -
Calendar - Search Page Maker - Summary Reports - Mass Update - DataTalk -
bizzworxx AppGini Helper JabaScript Library - bizzworxx Inline Detail-View - bizzworxx Helper Pack - AppGini Helper Packaging Tool -
Udemy course

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: FORMAT() Not Working in Calculated Field

Post by jsetzer » 2021-08-31 17:36

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.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

mohamed
Veteran Member
Posts: 80
Joined: 2020-04-19 16:18

Re: FORMAT() Not Working in Calculated Field

Post by mohamed » 2021-08-31 18:01

Hi Jan,

Many Thanks for your tip - excellent idea ...
and your post clicked something :idea: 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..
THANK YOU...
AppGini Pro 24.11 -
Calendar - Search Page Maker - Summary Reports - Mass Update - DataTalk -
bizzworxx AppGini Helper JabaScript Library - bizzworxx Inline Detail-View - bizzworxx Helper Pack - AppGini Helper Packaging Tool -
Udemy course

Post Reply