Mass Updates of Database Managed Dropdown Source Data

The typical use case for database managed dropdown menus is to connect with a table maintained by an external application like an accounting or CRM system. However, there is nothing stopping the connection to any external or internal table. Whatever the connection, the stability of the external table is directly reflected on the documents stored in FileHold that are associated with that data.

FileHold does not access the data in the external table in real time. Instead it checks the external table on a predefined interval; normally once an hour. It compares the contents of the external table with a shadow copy of the table inside the FileHold database. If there are records in the external table that are not in the shadow table, FileHold adds them to the shadow table. If there are records in the shadow table that are not in the external table FileHold will delete them from the shadow table. FileHold determines if there are additions or deletions by looking at the Field Id (FileHold 12 or earlier) or Lookup By (FileHold 14) value. If a record has changed the Field Caption or Retrieve By value in the external table FileHold will update the value in the shadow table.

If a value from the external table has been associated with a document in FileHold, any changes will be immediately reflected on that document. For a deletion, this means the value will be immediately removed from any documents that may have been associated with the value. FileHold considers a deletion a permanent change in the document schema. The original value will no longer exist anywhere in FileHold.

This works well when the external table is managed by another application, but occasionally the external table is not associated with any application and instead it is updated en mass from a single file or ETL transformation. Special care must be taken in this scenario to ensure the integrity of the overall solution. The scheduled task, FH database lookup fields synchronization, that updates the shadow table must never run during the time a mass update is taking place to ensure the update process does not falsely assume the external table has deleted records. This can be done with typical Windows administration tools.

The following approach is recommended if you plan to do a mass update:

  1. Check and wait until the scheduled task is not running.
  2. Disable the scheduled task.
  3. Perform the mass update.
  4. Enable the scheduled task.
  5. Optionally run the scheduled task immediately.

These steps will ensure the scheduled task to update the shadow table is never running during the mass update. The following is an example Windows script that will do the appropriate setup and clean up before and after the mass update. You can insert your mass update code at the spot indicated. You will need to ensure that your Windows security is setup appropriately in order to be able to run the schtasks command.

@echo off
rem * Copyright (C)2013 FileHold Systems Inc.
rem * Execute a mass update safely
echo[ Performing mass update...
set TASK="FH database lookup fields synchronization"
call :gettaskstatus %TASK%
if [%TASK_STATUS%] == ["Running"] (echo %TASK% is running, please wait.) & call :waitfornotrunning
schtasks /change /tn %TASK% /disable
rem ***********************************
rem *** insert your mass update code here
rem ***********************************
schtasks /change /tn %TASK% /enable
schtasks /run /tn %TASK%
goto :EOF
rem * Checks every five seconds to see if the task has stopped running
waitfor /t 5 null 2>nul 
<nul (set/p z=.)
call :gettaskstatus %TASK%
if [%TASK_STATUS%] == ["Running"] goto :waitfornotrunningloop
goto :EOF
rem * Pass in the task name as a parameter
rem * Returns TASK_STATUS
rem echo **%1**
for /f "delims=, skip=1 tokens=3" %%A in ('schtasks /query /fo csv /tn %1') do set TASK_STATUS=%%A
rem echo =^> %TASK_STATUS%
rem set TASK_STATUS="Running"
goto :EOF

IMPORTANT: Support for this solution and script is not included in your FileCare agreement. FileHold Professional Services are available to assist in setting up solutions like this one.