File Management in EQuIS Professional

The EQuIS database mainly stores files in the DT_FILE data table. Most DT_FILE fields contain file information or metadata; the files themselves are stored as Binary Large Objects (BLOBs) in the CONTENT field using the varbinary(max) data type. In EQuIS 7.22.3 and newer, the DT_FILE.CONTENT field is hidden to improve performance. A field that contains a BLOB shows the text “System.Byte[]”.

Before adding a file of a new file type, create a record for that file type in RT_FILE_TYPE. For more information on this reference table, see RT_FILE_TYPE – MIME Types.

Uploading and Downloading Files

Within EQuIS Professional, users can upload and download single files in the following ways:

•for files in DT_FILE, through the Docs & Photos Form

•for files in other locations, through the EQuIS Blobber Form

EQuIS features like tables, reports, and charts will output files if the user chooses an export option, such as Export to Excel or Save Parameters.

There are two ways to batch upload files into the DT_FILE table:

1.Doc & Photos Form – The Docs & Photos Form can upload an entire folder of files at one time. These files may include images that can then be associated with a location or location types, spreadsheets or templates for use in report output. The file type associations are stored in RT_FILE_TYPE. The form will populate DT_FILE, RT_GROUP, and RT_GROUP_MEMBER tables. These files will then be available in the Explorer widget.

2.EQEDD Format Files_v1 Section – This section of the EQEDD format file specifies the details for each non-data file (without needing to manually edit the DT_FILE table after the files are added to the database). For the non-data files to be processed by Enterprise EDP, they must be included in the EDD .zip file (ex: EB1608796a.FAIRVIEW.EQEDD.zip) and referenced in the Files_v1 section of the format file. Each non-data file within the zip file should have a row in the Files_v1 section to match with that file.

The EXTERNAL_URL field in the Files_v1 section of the EQEDD format can be used to load a record into the DT_FILE table that references an external file (i.e., a file that resides outside of EQuIS). The value in the EXTERNAL_URL field must be one of the following:

NULL = This field is left blank when physically loading the file into EQuIS. The file must exist in the EDD and will be loaded normally.

<url> = EDP will check to make sure the URL is valid and accessible. A record will be created in the DT_FILE table (during Commit). When the corresponding file is downloaded via the Explorer widget, Enterprise will redirect the user's browser to the original URL. The user must have appropriate credentials to access the original URL or the file will not download.

proxy.<url> = The URL will be checked and the record will be loaded into the DT_FILE table as described above. When the corresponding file is downloaded via the Explorer widget, the Enterprise application will make the request to the external URL, download it to a temporary folder on the web server, and then send it to the user's browser. The user will never see the external URL. The external URL must be accessible from the Enterprise web server.

Permissions

Enterprise permissions will restrict a non-admin user from accessing Tables\DT_FILE in Professional. The restriction details are:

1.A non-admin user without any Enterprise Viewer, Editor, or Owner permissions to the Files Object Type nor to individual Files/Documents of DT_FILE:

i.cannot see the files in Professional\Tables\DT_FILE, and

ii.cannot see the files in the open dialog for loading a template file in Crosstab Configurations.

2.A non-admin user with any of Enterprise Viewer, Editor, or Owner permission to all or some files of DT_FILE, whether associated with the connected facility or with DT_FILE.FACILITY_ID = null:

i.can see the files in Professional\Tables\DT_FILE, and

ii.can select the files in an open dialog for loading a template file in doing a crosstab config.

3.A non-admin user with a denied Viewer permission to all or some files of DT_FILE cannot see the files in Professional\Tables\DT_FILE.

Associate Image with Specific Location

1.In the DT_FILE.PLACE_TYPE field for an image file record, enter "sys_loc_code".

2.In the DT_FILE.PLACE_CODE field for that record, enter the sys_loc_code value (e.g., "B-30") of the location from DT_LOCATION.

Associate Image with Location Types

1.Upload an image file, such as through the Docs & Photos Form or EQuIS Enterprise Explorer Widget.

2.Open DT_FILE and locate the record for that image file.

3.Fill in the following fields for that record:

a.PLACE_TYPE = LOCATION_TYPE_CODE

b.PLACE_CODE = the name of the location type (e.g. "MONITORING WELL"). Find this information in DT_LOCATION in the LOC_TYPE field, as well as in RT_LOCATION_TYPE.

This image provides an icon in Google Earth for each point with that Location Type.

Show Associated Images in Google Earth

1.In the ribbon, click the Reports button.

2.Select the Google Earth: Locations Report, then Open.

3.Choose the appropriate parameters, and under "Description:", select "Embed Files".

4.Run the report.

Google Earth should open and navigate to the locations. In addition, designated icons for Location Type will be visible and/or any associated image(s) will be visible if a specific location is clicked on.

15695_associmg

File Storage Fields

Along with DT_FILE, file storage locations in the EQuIS database include the following:

•DT_ACTION_LEVEL.ASSEMBLY – for custom action level DLLs

•DT_CHAIN_OF_CUSTODY.SAMPLER_SIGNATURE

•RT_DQM_CHECK.ASSEMBLY

•Collect system tables

oCONTENT field in ST_COLLECT_DATA_ENTRY, ST_COLLECT_FIELD_FILES, and ST_COLLECT_FORMS

oST_COLLECT_FORMS.FORMS_IMAGE

•ST_FILE_REGISTRATION.CONTENT – loaded EDD files

•ST_MODULE.ASSEMBLY – published reports and forms that appear in this table

•ST_NOTICE_ATTACHMENT.CONTENT – notice attachments

•ST_REPORT.ASSEMBLY – configurations for published crosstab reports (both grid and non-grid) and published Alive survey reports

•ST_SPLA.CERTIFICATE – for Service Provider Licensing Administration (SPLA) certificates

Depending on license portfolio, some of the above tables may not exist in an organization’s EQuIS database. Non-admin users will have limited access to system tables.