Invalid number value when editing a currency field
When adding a currency field to a list using any currency format with a ' as group separator (e.g. $ 123'456.00), we can easily add a new entry just by entering 5000, which correctly transforms to $5'000.00 on save.
However, when we edit the same record using the default SharePoint form, the currency field is populated with 5'000, which results in a 'Invalid number value' validation error on save.
The only way around this is by removing the group separator by hand, which is more than just inconvenient.
Is this by design, a known bug or did I miss something?
SharePoint
-
Echo Du_MSFT 17,116 Reputation points
2020-09-22T11:22:54.337+00:00 This is a quick note to let you know that we are performing research on this issue.
Thanks,
Echo Du -
markusgoehner 1 Reputation point
2020-09-23T15:21:41.113+00:00 Invalid number value when editing a number field
We have that problem with number fields (without currency). When I adding or using number fields to any of our existing or new SharePoint lists at "LPT Teams" or on my personal "Playground", I can easily add a new entry just by entering -5001 and 3333, which correctly transforms to -5'000 and 3'333 on save.
When I edit the same record using the default SharePoint form, the number fields is populated with -5'001 and 3'333, which results in a 'Invalid number value' validation error on save. The only way around this is by removing the group separator by hand, which is more than just inconvenient.
We have such problems only in "modern experience", with the "standard (old) experience" everything works fine. Our workaround since ~2 years (!!!) is using only the "standard experience", without all the new features in "modern experience". This is very bad!
-
markusgoehner 1 Reputation point
2020-09-23T15:25:53.513+00:00 Screenshot
-
Echo Du_MSFT 17,116 Reputation points
2020-09-28T09:45:04.207+00:00 According to international standards, use comma (
,
) to represent the thousands separator (add a comma for every three digits).
You could refer to this article Decimal and Thousands Separators to view currency format.So, when you enter single quotes(
'
) in the currency field, SharePoint will display"Invalid number value"
error.Thanks,
Echo Du
If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. -
markusgoehner 1 Reputation point
2020-09-28T11:49:45.03+00:00 @Echo Du_MSFT
We do not enter any character! If we save 5000, -5001 or 3333 sharepoint online display's it as 5'000, -5'001 or 3'333 after safe and if we edit it's displayed as 5'000, -5'001 or 3'333 and then we have the described problem. Sorry, but there are no "international standards" for the whole world! Thera are different countries with different rules, for example German (Switzerland) ;-)
Excel has no problem with that:
And this are the Regional Settings for Switzerland:
-
Thomas Sommer 1 Reputation point
2020-10-23T10:14:03.44+00:00 We have the same Problem as @Sebastian Zolg and @markusgoehner on simple number fields. We enter numbers without apostrophs, then apostrophs are added when the number is displayed, and when the entry is edited the next time, the above mentioned error is thrown - very frustrating!
-
markusgoehner 1 Reputation point
2020-10-23T14:33:55.373+00:00 Hi @Thomas Sommer , are you also located in switzerland with the regional settings german (switzerland)?
-
Thomas Sommer 1 Reputation point
2020-10-26T13:42:21.08+00:00 Yes we are. That regional setting in sharepoint seems to be the problem...
-
markusgoehner 1 Reputation point
2020-10-27T10:57:39.147+00:00 Hi @Thomas Sommer , for us we have found a acceptable workaround. We change the "Landes-/Regionaleinstellungen" on the Sharepoint Online Site from "Deutsch (Schweiz)" to "Deutsch (Deutschland)". The only "drop of bitterness" (Wermutstropfen) is, that we have to accept the new presentation of values:
You can change the settings as follows:
- klick to START on your Sharepoint Site, then he gear wheel
- go to: Websiteinformationen, Alle Websiteeinstellung anzeigen, Landes-/Regionaleinstellungen
- Gebietsschema auf "Deutsch (Deutschland)" wechseln
-
markusgoehner 1 Reputation point
2020-10-27T11:01:08.687+00:00 Landes-/Regionaleinstellungen: Deutsch (Schweiz)
-
markusgoehner 1 Reputation point
2020-10-27T11:03:28.23+00:00 Change the Landes-/Regionaleinstellungen
-
markusgoehner 1 Reputation point
2020-10-27T11:04:14.073+00:00 Landes-/Regionaleinstellungen: Deutsch (Deutschland)
Sign in to comment