Number formatting forCalculations

First of all, SSP is developed in .NET, that normally likes the English way of showing a numbe - Example: 12,000.12:

. (dot) for the decimals seperator
, (comma) for the thousands seperator

But in most of Europe, you like to show the number like this:

  12.000,12

If you use the last format (12.000,65), SSP will see it as 12,00065.

To show any entered number into a nice EU-format, use this formula in the field:

  FORMAT('{0:N2}', ToNumber('#REPLACE-FORMFIELD#',0))

But is better to store it in a datastore using the US format. You can convert it back to it using:

  REPLACE(REPLACE('#REPLACE-FORMFIELD#', '.',), ',', '.')

see also ticket: 1847 or sample form: http://dev.ssp7.smt-x.com/Forms/form.aspx?adminmode=1&id=179

If you don't expect numbers to contains dots, it is ok to transform , into . :

  REPLACE('#REPLACE-FORMFIELD-CalculationtestRepeatingSections||Quantity||#', ',','.')

This also gives issues when running queries on datastores. As a rule of thumb, do not store the thousands seperator in the datastore! So first, convert the entered field into a format without thousands seperator:

  FORMAT('{0:f2}', ToNumber('##',0))

If your users use ',' as a decimal seperator, replace it with a '.'. Users can always enter a thousand seperator, so you need to get rid of it.

This formula changes the users input in 1(amount_01) to (remove thousands seperator & convert commas to dots):

 REPLACE(FORMAT('{0:f2}', ToNumber('#REPLACE-FORMFIELD-Numbers example||Amount_01||#',0)),',','.')

You can do this before writing into the datastore, or adapt your query so it replaces commas by dots, like this :

  replace('field10',',','.')

To then show the number back to the user, convert it back, using the FORMAT as shown above.

--- the other way around---- IF your datastore gives you a number as it likes it (so no thousand seperators and dots for decimal seperator), you need to do it the other way around. First, do the replace, and set the format to something the user can read:

 FORMAT('{0:N2}', tonumber(REPLACE('#REPLACE-FORMFIELD-Numbers example||Amount05||#', '.',',')), ))

in the sample form, the field is NOT coming from a datastore, so the TONUMBER is not needed

 FORMAT('{0:N2}', REPLACE('#REPLACE-FORMFIELD-Numbers example||Amount05||#', '.',','))