<< Click to Display Table of Contents >> Navigation:  EDP > Troubleshooting > Culture Setting Conflicts in EDP

•Date Formatting

•Decimals and Digit Groupings

•Culture Setting Conflicts in EQuIS Collect

 

In EDP the cultural settings determine both the display language and the formatting for Date and Numeric datatypes. When an EDD is created or provided using a separate cultural setting from EDP, it can result in unexpected behavior or errors. These issues usually occur due to mismatched country/region codes, even when using the same language. EQuIS supports language settings for English, French, Spanish, Portuguese, and German.

 

In EQuIS Enterprise, EDDs submitted through the EDP EDD Upload Widget will be processed using the culture setting configured for the EQuIS Workflow. In cases where data submitters with separate culture setting may be required to submit EDDs through the Web or Email, an optional EQuIS Format Definition Configuration can be set up using regular expressions to have the EDD processed in the data submitters preferred culture setting.

 

Date Formatting

 

Professional EDP and EDP Standalone will use the Windows display language to determine which culture settings are used by default. The culture setting can also be explicitly set in the Config File AppSetting of the application configuration file in the root installation folder (EQuIS.exe.config for Professional EDP, and EDP.exe.config for EDP Standalone). The Date Format Options can also be configured per field by the loaded EQuIS Format, in which case it will enforce the date format specified by the Format.

 

Flat file EDDs (*.txt, *.csv, etc.) are plain-text and when a value is mapped to a Date field by EDP, the plain text is parsed and converted to a Date datatype. This can cause some issues, for example, if the EDD is saved in an American (en-US) date format (mm/dd/yyyy) and opened in EDP by a user in the UK (en-GB), it will parse the date from the plain text as dd/mm/yyyy instead of the intended mm/dd/yyyy.

 

Example 1 - In the United States “1/13/2023” is January 13th, 2023, but will be flagged as an invalid value in most European culture settings.

 

Example 2 - In the United States “1/2/2023” is January 2nd, 2023, but will be parsed as the 1st of February 2023 in most European culture settings. This will NOT flag it as an error as both are valid date values. When working with EDDs from different areas around the world, it is important to check the date formatting to maintain data integrity.

 

 

When processing an EDD containing date issues related to the mismatched cultures, an easy method to address the errors is to use Professional EDP with the same culture by passing the "Culture" setting to EQuIS Professional via Parameter on the command line or from a pre-configured shortcut. The EDD can then be saved using the “Save As EDD” option from the EDP File Menu/Backstage. The date in this new EDD will be converted to the Universal Date Format and interpreted as intended when reopened in your preferred culture setting.

 

Commonly Used Culture Settings and Associated Short Date Format

 

 

 

Decimals and Digit Groupings

 

Numeric values are formatted differently depending on the culture setting, with the main differences being which characters are used for Decimals and Digit Groupings. In a flat file EDD (*.txt, *.csv, etc.), the data is saved as plain-text and the datatype is evaluated when mapped to a field by EDP. If the datum contains a decimal or digit grouping character inconsistent with the culture setting, when parsed it will be evaluated as Text and be flagged as either an Invalid data type error or as a Numeric Value Not Consistent with Culture Separators error. In some cases, the placement of the decimal or digit grouping character results in an ambiguous interpretation of the value depending on culture setting.

 

Example 1 - If 120,00 is mapped to a Numeric field, it will be valuated as a valid value in France, but will be flagged as a Numeric Value Not Consistent with Culture Separators error in the United States.

 

Example 2 - In the United States (en-US), 100.000 will be interpreted as 100 whereas in Germany (de-DE) it would instead be parsed as 100000 due to the “.” being interpreted as the character for Digit Grouping instead of a Decimal. The same is true in reverse. The value 100,000 is interpreted as 100000 with the en-US culture setting but as 100 in the de-DE culture setting. Neither instance will show an error as both are considered valid numeric values.

 

When encountering numeric errors and inconsistencies related to a mismatched culture setting, a method for correcting these issues in Professional EDP is to use the Find and Replace tool to change the “,” and “.” from specific columns to match what is expected by your local culture setting.

 

 

Commonly Used Culture Settings and Associated Number Formatting with Example

 

 

 

Culture Setting Conflicts in EQuIS Collect

 

When Collect Mobile users collect data in a culture setting different than the culture setting configured for EQuIS Workflow, the conflicts outlined above can occur. These conflicts can be prevented in the data mappings of a Collect template by ensuring the data coming in from all Collect users match the culture setting configured fro EQuIS Workflow.

 

To account for these conflicts in Collect templates, the following can be implemented in a Collect template:

Dates: As dates in an EDD that are formatted with the ISO 8601 Universal Date Format yyyy/MM/dd are always accepted as valid values, the FORMATDATE formula can be applied to all date data mappings to change the dates being mapped to the ISO 8601 Universal Date Format (yyyy/MM/dd).

Example: FORMATDATE([DateField],'yyyy/MM/dd')

 

Decimal and Digit Grouping: For numeric fields where the decimal and digit grouping symbol needs to match the decimal or digit grouping symbol of EQuIS Workflow, the REPLACE function can be used to replace any mismatched decimal or digit grouping symbols being mapped to the resulting EDD.

Example: The following formula would replace the decimal separator (,) of a Collect user in the [fr-RF] culture setting to instead match [en-US] culture setting defined in the EQuIS Workflow's decimal separator (.):

REPLACE([DecimalField],',','.')