FileDirector removes indexing metadata when ODBC lookup returns no result

Symptoms

Use case

A document is processed via OCR to extract several data field values. Although metadata values can be used in a file name, it would not be practical or sensible to attempt to embed all data from a document into the name of the associated file. A sensible strategy is to save the full data into a database using a unique key; the file is named using the unique key; during the loading of a document an ODBC lookup is used to populate the full document data.

Example scenario

1. Files with names in the format "<unique reference>_some other text.pdf" are loaded into FileDirector.
2. During load the filename is split on the "_" character and the first segment is populated into a <unique reference> metadata field for the document.
3. Also during the load operation an ODBC lookup uses <unique reference> to populate further fields on the document.
4. For some reason the <unique reference> is not [yet] available in the ODBC datasource.

In the above example the default behaviour for FileDirector is to populate the document fields with the results of the data lookup. If that lookup yields no result then NULL values for all fields are used, including the <unique reference> field.

Impact

If the <unique reference> value is set to NULL then subsequent data lookups (e.g. scheduled update for all documents) will fail. Only manual intervention can be used to properly populate the data fields.

Cause

The ODBC lookup returned no result; NULL data is populated into ALL metadata fields, including the reference column.

Resolution

Prevent re-occurence

Find the web.config file in the FileDirector Server program files directory; add in the following setting/key; recycle the FileDirector IIS application pool so that the new value is loaded.

<add key="FDServer.UseLegacyODBCResult" value="1" />

Fix existing records 

Full article to follow. In brief you must create a CSV file which uses the FileDirector document GUID in the first column and provides other data fields. Use a scheduled task to load the CSV file and reindex existing documents.