How to apply a data format in the calculated field

[afayez]

Dear,
Good day,

I have a calculated field (Quantity * Price). i tried to make a numbers format 12,345.67 bit it gave an erroe message:

Error checking:
Warning: Skipping calculations for field 'qtn.total': This field can't be a calculated field.
Reason: Fields that have a data format specified can't be calculculated fields (you can apply a data format in the calculation instead)
Finished error checking.
PHP application generation process cancelled by user.

Support please how can apply a data format in the calculation instead.

[pbottcher]

Hi,

try

FORMAT(FIELDNAME or CALCULATION,2,'YOURLANGUAGENEED')

for what you show it should be standard and you could use

FORMAT(FIELDNAME or CALCULATION,2)

[afayez]

Thanks

i tried to put {FORMAT( `all_invoices` ,2)} at the end of the formula but it didn't work, can you support

SELECT
SUM(`invoices`.`sum`)
FROM
`projects` LEFT JOIN
`invoices` ON `projects`.`id` = `invoices`.`project_id`
WHERE
`projects`.`id` = '7'

FORMAT( `all_invoices` ,2)

[pbottcher]

Hi,

can you try

Code: Select all

SELECT
FORMAT(SUM(`invoices`.`sum`),2)
FROM
`projects` LEFT JOIN
`invoices` ON `projects`.`id` = `invoices`.`project_id`
WHERE
`projects`.`id` = '7'

[jsetzer]

Additional notes:

Code: Select all

... format(sum(...), 2) ...

https://www.w3resource.com/mysql/mathem ... unded%20up.

Please note that sum(...) returns a numeric value but format(sum(...), 2) returns a string. If you are facing problems, check the data type of that field.

Also, when using the calculated field for subsequent calculations, remember that this is a string now and not a number. Especially when format returns strings containing comma "," or point "." due to locale settings this could lead to follow up miscalculations.

[afayez]

Thanks alot
It worked very well.
but the couloumn sum is not working well. as in the attached photo.