AppGini 5.81 number format

[Moh Youba]

Hello
I am looking for help to get number format like this " 5 000" instead of this "5000"

How to custom these code

Code 1
SELECT `caisse`.`recettes` - `caisse`.`depenses`
FROM `caisse`
WHERE `caisse`.`idcaisse`='%ID%'

Code 2
SELECT
SUM(montant) depenses
FROM
depenses;

Thank you

[pböttcher]

Hi,

you can use

replace(format( VALUE ,0),',',' ')

like

SELECT replace(format(`caisse`.`recettes` - `caisse`.`depenses`,0),',',' ') FROM `caisse`
WHERE `caisse`.`idcaisse`='%ID%'

[jsetzer]

You can try to use MySql number format function with your locale as 3rd parameter:

http://www.mysqltutorial.org/mysql-format-function/

Available locales:
https://dev.mysql.com/doc/refman/5.7/en ... pport.html

[Moh Youba]

Hello

Thank you for your help

Regards

[Moh Youba]

Hello
I have try many type but still not working for me

SELECT replace(format(`membres`.`montant` * `membres`.`duree`,0),',','')
FROM `membres`
WHERE `membres`.`idmembre`='%ID%'

"montante is set to decimal and "duree" is set to integer (for reference)

Thank you for any help.

Best regards

[pböttcher]

Hi,

can you post the result you get from the native sql statement.

SELECT `membres`.`montant` * `membres`.`duree`
FROM `membres`
WHERE `membres`.`idmembre`='%ID%'

[Moh Youba]

Hi

Sorry, I am not sure to get your question, but this is what I get.

When I put 1000 I get 1000

I want to get this 1 000

Thank you

[pböttcher]

Hi,

if the montante is set to decimal, you should have a result with decimal precision. Can you check that.

[Moh Youba]

ok, I set the field "montant" as this
Decimal with precision 0

[pböttcher]

Hi,

can you try

SELECT replace(format(`membres`.`montant` * `membres`.`duree`,0,'en_EN'),',',' ')
FROM `membres`
WHERE `membres`.`idmembre`='%ID%'

watch the space at the end ',','SPACE')

[Moh Youba]

thank you, please give a moment to try and back to you

best regards,

[Moh Youba]

not working, maybe I miss some point

number_format-1.jpg (102.39 KiB) Viewed 9800 times

number_format-1.jpg (102.39 KiB) Viewed 9800 times

[pböttcher]

Hi,

the statement itself is only

Code: Select all

SELECT replace(format(`membres`.`montant` * `membres`.`duree`,0,'en_EN'),',',' ')
FROM `membres`
WHERE `membres`.`idmembre`='%ID%'

[Moh Youba]

thank you Olaf, and sorry for my poor knowledge. back to you once fixed.

regards,

[pböttcher]

Even so I'm not Olaf, hope it works now.

[Moh Youba]

my bad !
sorry

[Moh Youba]

ok, I correct the code but still not working for me unfortunately !!!

[pböttcher]

Hi,

I think you need to change the type for the field Montant TOTAL to varchar (10) or what suits you best.

[Moh Youba]

ok I will try all type until I find the correct.
Thank you

[Moh Youba]

yes it is working. When I change to varchar (10) it is working.
but the problem is I cannot use the option "show column sum" with varchar

[pböttcher]

Hi,

ok, so there was a missunderstanding. If you want to see the result in the table view with your format, you should change back the type to decimal, or integer (if you do not use decimals).

montant total
you can create in the hooks folder a file called membres-tv.js. Add the following to the file.

Code: Select all

$j(document).ready(function(){

$j('tr td.parent-credit').each(function(){var value=$j(this).text(); $j(this).text(Intl.NumberFormat('en-ZA').format(value))});
})

[Moh Youba]

Hello
thank sir for your help and support, I am going to check and let you know.

Best regards,