EQuIS Format Definition Configuration

Introduction

EQuIS Enterprise EDP format registration consists of a set of records in the ST_CONFIG table for each Registered Format Name; see Add or Modify Enterprise EDP Formats for additional information. The registered format name is set in the OBJECT_TYPE field for all records in this set and must be exactly the same for all records in this set. The registered format name is matched to the format specified by the EDD file name. For example, the EDD file names 20160321-0832.SPRINGFIELD.EQEDD_FIELD.zip and 20160321-1445.SPRINGFIELD.EQEDD_LAB.zip specify the registered format names EQEDD_FIELD and EQEDD_LAB, respectively. While these registered format names may refer to the same EDP format, they would refer to two different configurations of that format.

Each format has associated records in the ST_CONFIG table that define the format (XSD or XSE file name), the commit type, and whether or not it accepts warnings. A format may also be set as the default format. An optional configuration setting is to define a column in the EDD where a FACILITY_CODE value will be used by the SplitByFacility File Monitor agent to split the file into a separate EDD for each facility. Another optional configuration enables users to set the culture for specific EDDs by adding an identifier to the EDD Name. In addition, "virtual" formats based on a registered EDP format can be configured to process files through EQuIS Link (see EQuIS Link Configuration section below).

EQuIS Version: v7+

Namespace: EarthSoft.EDP.

Products Affected: Enterprise

Required Modules: Enterprise Workflow Service

blankeddvalues

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	blankeddvalues	Required
OBJECT_TYPE	<registered format name>		Name must match the Regular Expression [a-zA-Z][a-zA-Z0-9_]*
OBJECT_VALUE	values	Required
STRING_VALUE	Blank ValidValues ParentValues Both	Blank = Create blank EDD ValidValues = will include drop-downs with reference values for columns with a lookup in the format. ParentValues = will include drop-downs for columns that have a foreign key relationship in the format Both = will show drop-downs for column that have either a lookup or a foreign key relationship in the format	Select One

blankeddvalues Remarks

The value determines if and how a blank EDD is populated when downloaded through the EDP Upload Widget.

xsdFileName

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	efd	Required
OBJECT_TYPE	<registered format name>	Required	Name must match the Regular Expression [a-zA-Z][a-zA-Z0-9_]*
OBJECT_VALUE	xsdFileName	Required
STRING_VALUE	<Path to Format Files>	Valid File System Path or File ID

xsdFileName Remarks

The STRING_VALUE must be a valid file system path for both the EQuIS Enterprise Web application and EQuIS Enterprise 7 Workflow windows service processes. Both processes must have read/execute permission on the file(s) that make up the EDP Format. If the format is packaged in a ZIP archive, then this is the path and filename of the ZIP archive. If the format is expanded into a folder, then this is the path to the .XSD or .XSE file in the format.

commitType

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	efd	Required
OBJECT_TYPE	<registered format name>		Name must match the Regular Expression [a-zA-Z_][a-zA-Z0-9_]*
OBJECT_VALUE	commitType	Required	Setting may alternately be identified as 'updateMode'
STRING_VALUE	None Merge MergeOnly Update UpdateOnly Replace ReplaceOnly Format Default	None = Insert Only Merge = Insert and Merge MergeOnly = Merge Only Update = Insert and Update UpdateOnly = Update Only Replace = Insert and Replace ReplaceOnly = Replace Only Format (or Default) = Format Specific (controlled by the custom handler)	Select One Note: These are case sensitive. For example, 'Merge' must be used for Insert and Merge, not 'merge' or 'MERGE'.

commitType Remarks

The OBJECT_VALUE may be either 'commitType' or 'updateMode'. If both records exist, the setting for 'updateMode' will take precedence.

acceptWarnings

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	efd	Required
OBJECT_TYPE	<registered format name>		Name must match the Regular Expression [a-zA-Z_][a-zA-Z0-9_]*
OBJECT_VALUE	acceptWarnings	Required
STRING_VALUE	True False		Choose One

acceptWarnings Remarks

When set to False, EDP Warnings are treated as errors and will result in the EDD being rejected by Enterprise Workflow with an error log returned to the data submitter.

isCacheable

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	efd	Required
OBJECT_TYPE	<registered format name>		Name must match the Regular Expression [a-zA-Z_][a-zA-Z0-9_]*
OBJECT_VALUE	isCacheable	Optional
STRING_VALUE	True False		Choose One

isCacheable Remarks

This record is optional and defaults to False. When set to True, the EFD will be cached in Enterprise.

isDefault

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	efd	Required
OBJECT_TYPE	<registered format name>		Name must match the Regular Expression [a-zA-Z_][a-zA-Z0-9_]*
OBJECT_VALUE	isDefault	Optional
STRING_VALUE	True False		Choose One

isDefault Remarks

This record is optional and defaults to False. When present, there should be only one Registered Format Name with this setting equal to True. In the event that an EDD is submitted without specifying a Registered Format Name, this entry specifies the format configuration that will be used.

Example

The following example configures the EQEDD format for use by Field personnel and Labs. Field EDDs are limited to Insert Only operations while accepting warnings. Lab EDDs are set to Insert and Merge with warnings treated as errors.

CONFIG_SECTION	CONFIG_KEY	OBJECT_TYPE	OBJECT_VALUE	STRING_VALUE
efdManagementConfiguration	efd	EQEDD_LAB	xsdFileName	C:\EQuIS\Formats\EQEDD.zip
efdManagementConfiguration	efd	EQEDD_LAB	commitType	Merge
efdManagementConfiguration	efd	EQEDD_LAB	acceptWarnings	False
efdManagementConfiguration	efd	EQEDD_LAB	isCacheable	True
efdManagementConfiguration	efd	EQEDD_LAB	isDefault	True
efdManagementConfiguration	efd	EQEDD_FIELD	xsdFileName	C:\EQuIS\Formats\EQEDD.zip
efdManagementConfiguration	efd	EQEDD_FIELD	commitType	None
efdManagementConfiguration	efd	EQEDD_FIELD	acceptWarnings	True
efdManagementConfiguration	efd	EQEDD_FIELD	isCacheable	True

SplitByFacility

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	efd	Required
OBJECT_TYPE	<registered format name>		Name must match the Regular Expression [a-zA-Z_][a-zA-Z0-9_]*
OBJECT_VALUE	SplitByFacility	Optional
STRING_VALUE	<field name>	Name of a field (column) in the EDD where a FACILITY_CODE value is required

SplitByFacility Remarks

This property is only used by SplitByFacility monitor agent. The record should not exist if the format does not contain a FACILITY_CODE column in any section.

Cultural Settings (EDDCultureRegex)

When this setting is defined, users are able to set the culture for specific EDDs by adding an identifier to the EDD Name rather than using the defined Workflow Service Culture Setting. This identifier is set as a regular expression. By default, the STRING_VALUE is set to this regular expression "(?<=\[).+?(?=\])" that looks for a culture identifier in the EDD name separated by square brackets. For example: ExampleEDD[de-de].Springfield.EQEDD.zip contains German culture settings. Workflow will process this EDD in the German culture so that the numerical values and date values do not cause errors when attempting to load. If this ST_CONFIG is defined, but no culture is specified in the EDD zip name, the EDD will be processed in the default Workflow Service Culture Setting.

ST_CONFIG	VALUE	REMARK
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	EDDCultureRegex	Required
STRING_VALUE	(?<=\[).+?(?=\])	Regular expression

Cultural Settings Remarks

The regular expression in the STRING_VALUE can be customized to look for the cultural setting value between a character other than square brackets, such as parentheses. In this instance, an example file name would be EDDName(es-es).Facility.Format.zip instead of EDDName[es-es].Facility.Format.zip

EQuIS Link Configuration

The Enterprise workflow service can process files that do not conform to a specific EDP format by running the files through EQuIS Link, which will generate valid EDDs for that EDP format. To achieve this functionality, a new set of format configuration parameters must be set in the ST_CONFIG table specifically for this type of job and an EQuIS Link mapping project must exist that maps the data to the specified EDP format. Note that these Link processing formats are "virtual" formats in the sense that although they have their own format name (OBJECT_TYPE), they actually refer to an underlying EDP format with a different name and have two additional parameters to facilitate processing by Link to generate data using that format.

Notes:

•An EQuIS Link license is needed to create the mapping project file required for the LinkProjectFileID property.

•See Configuring EQuIS Link for Enterprise EDP on using Link with a format in Enterprise.

•If configured to use a Link reference data file, the Link reference file must be packaged in a separate *.zip file and uploaded to the DT_FILE table. The LinkReferenceFileID and LinkReferenceFileType must be set up in the ST_CONFIG table, in addition to the LinkProjectFileID and LinkSourceFileType settings.

•The .zip file can only contain one file of the given type.

•If enabled, detailed logs are available for diagnosing configuration issues.

Project and Source File Configuration

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	efd	Required
OBJECT_TYPE	<registered format name>		Name must match the Regular Expression [a-zA-Z_][a-zA-Z0-9_]*
OBJECT_VALUE	LinkProjectFileID	Defines ID of Link project file; must be in the DT_FILE table.
STRING_VALUE	<file ID>	File ID

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	efd	Required
OBJECT_TYPE	<registered format name>		Name must match the Regular Expression [a-zA-Z_][a-zA-Z0-9_]*
OBJECT_VALUE	LinkSourceFileType	Defines type of source data to be used.
STRING_VALUE	<file type>	Choose one of valid values.	EXCEL – Microsoft Excel file CSV – Set of comma-delimited text files TXT – Set of text files EQUOTE – XML eQuote file eCOC – XML eCOC file XML – Generic XML file AGS – Set of AGS files AB_CPT – Geotech (.cpt, .dpt) file HT_CPT – Hogentogler sounding (.cpt) and dissipation (.dis) files UU_UC – TRX file

Reference Data File Configuration (Optional)

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	efd	Required
OBJECT_TYPE	<registered format name>		Name must match the Regular Expression [a-zA-Z_][a-zA-Z0-9_]*
OBJECT_VALUE	LinkReferenceFileID	Defines ID of Link reference file; must be in the DT_FILE table.
STRING_VALUE	<file ID>	File ID

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	efd	Required
OBJECT_TYPE	<registered format name>		Name must match the Regular Expression [a-zA-Z_][a-zA-Z0-9_]*
OBJECT_VALUE	LinkReferenceFileType	Defines type of reference source data to be used.
STRING_VALUE	<file type>	Choose one of valid values.	EXCEL – Microsoft Excel file CSV – Set of comma-delimited text files TXT – Set of text files EQUOTE – XML eQuote file eCOC – XML eCOC file XML – Generic XML file AGS – Set of AGS files AB_CPT – Geotech (.cpt, .dpt) file HT_CPT – Hogentogler sounding (.cpt) and dissipation (.dis) files UU_UC – TRX file

Warnings:

•If a Link reference file is configured, the LinkReferenceFileID and the LinkReferenceFileType must both be configured. Configuring only one or the other will result in errors.

•Do not insert the two new rows for reference data with NULL/empty values, which will cause errors if the files are missing or settings are configured incorrectly.

Remark

The new format must be based on an existing, registered EDP format but new records must be created for all object values.

Warning: Do not just add records for the LinkProjectFileID and LinkSourceFileType properties to an existing registered EDP format (e.g., EQEDD, EZEDD) unless the intention is for all files submitted using that format to be processed through EQuIS Link.

Example

The ABC Laboratory is not able to export EDDs from their Laboratory Information Management System (LIMS) that match a specific EQuIS format. An EQuIS Link project has been developed that appropriately maps the CSV files from the LIMS export to the EQEDD format. This EQuIS Link data mapping project file has been uploaded to the DT_FILE table, where FILE_ID = 66034. Enterprise EDP can automatically process these files by adding the following records to the ST_CONFIG table to create the new format ABCLab_Link.

CONFIG_SECTION	CONFIG_KEY	OBJECT_TYPE	OBJECT_VALUE	STRING_VALUE
efdManagementConfiguration	efd	ABCLab_Link	xsdFileName	C:\EQuIS\Formats\EQEDD.zip
efdManagementConfiguration	efd	ABCLab_Link	commitType	Merge
efdManagementConfiguration	efd	ABCLab_Link	acceptWarnings	False
efdManagementConfiguration	efd	ABCLab_Link	LinkProjectFileID	66034
efdManagementConfiguration	efd	ABCLab_Link	LinkSourceFileType	CSV
efdManagementConfiguration	efd	ABCLab_Link	LinkReferenceFileID	66035
efdManagementConfiguration	efd	ABCLab_Link	LinkReferenceFileType	EXCEL

In the example table above, the italicized records are optional, but both must be included if a Link reference file is being configured.

When the data provider submits a zip file that contains the CVS files exported from the ABC Laboratory to Enterprise EDP using the ABC_Link format, EQuIS Link will appropriately map the data and repackage as an EQEDD EDD that has the data provider user certificate. Enterprise EDP will then process the file using the EQEDD format.

EQuIS Live – EFD Configuration

The Enterprise workflow service can process files using the Live File Processor Agent. The Live File Processor Widget can first be used to add and/or update the Live File Processor settings in the ST_CONFIG table for the new instance. The Workflow Widget may then be used to add and/or update an EDP Format Definition for the Live File Processor Agent instance.

When a new instance of the Live File Processor is configured as an EDP format, four standard settings are added to the ST_CONFIG table (commitType, xsdFileName, isDefault, acceptWarnings). The settings for xsdFileName and commitType are unique to the Live File Processor instance configuration.

xsdFileName (EQuIS Live)

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	efd	Required
OBJECT_TYPE	<Live File Processor Agent instance name>	Required	Name must match the Regular Expression [a-zA-Z_][a-zA-Z0-9_]*
OBJECT_VALUE	xsdFileName
STRING_VALUE	<Live.FileProcessor.CONFIG_KEY.OBJECT_TYPE>	References the CONFIG_SECTION "Live.FileProcessor" for the corresponding CONFIG_KEY.OBJECT_TYPE settings that that the file will be processed with.

commitType (EQuIS Live)

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	efdManagementConfiguration	Required
CONFIG_KEY	efd	Required
OBJECT_TYPE	<Live File Processor Agent instance name>	Required	Name must match the Regular Expression [a-zA-Z_][a-zA-Z0-9_]*
OBJECT_VALUE	commitType	Choose one of valid values.
STRING_VALUE	None Replace Insert and Replace Default	None = Insert Only Replace = Insert and Replace Default (set by Custom Handler; otherwise Insert and Update) = Inserts new datum values, and replaces existing datum values if the Live File Processor updateExistingValues setting is true.	Select one

Example

The below example instance references the CONFIG_KEY = "Agent" and OBJECT_TYPE = "HOBO". The complete Live.FileProcessor settings for the HOBO template can be viewed here.

CONFIG_SECTION	CONFIG_KEY	OBJECT_TYPE	OBJECT_VALUE	STRING_VALUE
efdManagementConfiguration	efd	HOBO	xsdFileName	Live.FileProcessor.Agent.HOBO
efdManagementConfiguration	efd	HOBO	acceptWarnings	False
efdManagementConfiguration	efd	HOBO	isDefault	False
efdManagementConfiguration	efd	HOBO	commitType	Default