EarthSoft.EDP.UseNumberFormatting

The UseNumberFormatting setting in Professional EDP can be configured to specify a default setting for the entire format, default settings for specific section(s), and/or default settings for individual columns in a section. When importing data from a Microsoft Excel file, EDP will do the following based on the setting:

•If set to “True”, then if the cell has number formatting applied in Microsoft Excel, EDP will import the formatted value.

•If set to “False”, then EDP will import the value without formatting, regardless if the cell has number formatting applied in Microsoft Excel.

Settings configured in this way will take precedence over the Use_Number_Formatting option in the Backstage.

Note: This feature is intended for the manipulation of decimal values. Excel has additional formatting options for the Number field type that should not be selected when importing data through EDP:

•When using parentheses to denote negative numbers in Excel, EDP will retain the parentheses on import. However, an “Input string was not in a correct format” error will be thrown on the Create step. Thus, this option is not recommended.

•When using red lettering to denote negative numbers in Excel, EDP will ignore color formatting and all negative numbers will be read-in as positive. This option is therefore not recommended.

•The “Use 1000 Separator” option can be selected but will not impact number formatting. Values will be read into EDP without comma separators regardless of this setting in the Excel workbook.

EQuIS Version: 7

Namespace: EarthSoft.EDP.UseNumberFormatting

Products Affected: Professional and Enterprise EDP

Required Modules: Professional

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	EarthSoft.EDP.UseNumberFormatting	Required
CONFIG_KEY	<format name>	Required	The name of the format to configure.
OBJECT_TYPE
OBJECT_VALUE
STRING_VALUE	<JSON Configuration String>	Required	A JSON string (not case-sensitive).

JSON String Structure:

The JSON string should contain the following values for the UseNumberFormatting option:

Format

•Use the “Format” tag to set a default value for the entire format.

•This will be overridden by any Section or Column specific setting.

•This is not required.

Specific Section

•Specify values for a specific EDD Section of the Format by adding the Section name followed by the Section setting. See example below for formatting.

•To set a Section default value, use the “Section” tag. This is not required.

Specific Column in Section

•Within the Section settings above, specify the value for a specific column by adding the column name followed by the Column setting. See example below for formatting.

Example:

CONFIG_SECTION	CONFIG_KEY	OBJECT_TYPE	OBJECT_VALUE	STRING_VALUE
EarthSoft.EDP.UseNumberFormatting	EQEDD			{“format”: “true”, “TestResults_v1”: {“section”: “false”, “result_value”: “true”}, “TestResultsQC_v1”: {“section”: “false”}}

This would configure the EQEDD Format to import Microsoft Excel files using the following settings for UseNumberFormatting:

•EQEDD Format Default: True

oTestResults_v1 Section Default: False

▪Result_value Column Default: True

oTestResultsQC_v1 Section Default: False

Note: When creating or editing a UseNumberFormatting record in the ST_CONFIG table, a restart of Professional is required for the change to take effect. Failure to restart Professional will prevent the new setting from affecting imported EDDs.