Value(5) -> output: 50

[FranzG]

Hi there,

I do have a version history from Sharepoint and I am displaying it.

I am using

Value(ThisItem.Version)

This works fine as long as the browser is set to English. But once the browser gets set to German it shows 

10, 20, 30, 40, ... instead of the 1, 2, 3, 4, ...

I already realized the same problem with the Value function when comparing numbers. 

Value("5") <= 5 in English works fine and is 'true'. But once the browser is set to German it is 50 <= 5 which is false.

When I use Value("5", "de-DE") or Value("5", "en-EN") it works fine and I get the 5 instead of 50.

Why is that?

TL;DR: For some reason the Value() function without a language parameter does not work correctly when changing browser languages and makes a 1 into a 10.

Thanks.

[mmbr1606]

hey @FranzG 

The behavior you're observing with the Value() function in SharePoint or similar environments handling numerical conversions depends on localization settings, specifically how different locales interpret punctuation in numbers.

In English (en-US or en-EN), a period (.) is used as the decimal separator (e.g., 1.5 for one and a half), and a comma (,) is used as the thousands separator (e.g., 1,000 for one thousand).

However, in many European locales including German (de-DE), this is flipped: a comma is used as the decimal separator (e.g., 1,5 for one and a half), and a period as the thousands separator (e.g., 1.000 for one thousand).

When you use the Value() function without specifying a locale, it defaults to the locale of the browser or environment it is running in.

This means:

• In English, Value("1.0")would be interpreted as 1.0.
• In German, Value("1,0"]would be interpreted as 1.0, but Value("1.0") might be interpreted as 1000 (since it reads the period as a thousands separator).

When you explicitly specify the locale in the Value() function, like Value("5", "de-DE") or Value("5", "en-EN"), the function uses the specified locale rules to interpret the number, ensuring consistent behavior regardless of the browser's language settings.

To avoid such issues:

1. Always specify the locale in the Value() function when dealing with numbers, especially if your application might be used in different locales.
2. Consider setting or forcing a specific locale for your application if it is designed to operate primarily in one language, which can reduce confusion and errors related to number formatting.

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[FranzG]

So I have to always use the language setting for the backend because it doesn't realize that 4,0 in german is a 4,0 when someone uses sharepoint in german as the number 4.0 will get formated as 4,0 which will then be 40.

But worse: I have a table with numbers and it is related to another table and the displaying of the value works fine in english but not in german.

ThisItem.Status.Title

ThisItem.Status.ID

But in german it neither shows the item nor the ID. Does that mean as the lookup value is in english ("4.00000"), it can't make the connection to the other table even if it has the correct lookup ID?

[mmbr1606 · Accepted solution]

Yes, your analysis is correct. When working with cross-table relationships or lookup fields in a multilingual environment like SharePoint, the format of numerical values can significantly affect how data is matched or displayed across different tables, particularly if these values are used as keys or lookup references.

Possible Solutions:

Consistency in Data Formatting: Wherever possible, ensure that any data used for lookups or as keys in relationships is stored in a format that is not locale-dependent. This might mean storing numbers as strings and handling the conversion in your application logic or ensuring consistent numerical formatting across all locales.

Explicit Locale Handling in Queries: As you mentioned, when retrieving or manipulating data, explicitly specify the locale to ensure consistent interpretation of numbers, especially when dealing with lookups or keys. For example, always parse and format the lookup values using a standard locale (like English) both when writing and reading data.

Adjustment in SharePoint Settings: Depending on how SharePoint is configured, you might be able to set a default locale for number formatting or ensure that certain fields treat numeric values in a non-localized format. This can sometimes be controlled at the site or farm level in SharePoint's regional settings.

User Training and Documentation: If it's not feasible to change how data is formatted or handled technically, ensuring that all users are aware of how their locale settings might impact data display and functionality can mitigate some issues. Training and clear documentation can help users understand what to expect and how to adjust their settings if necessary.

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings