Mass updates or changes to 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. Regardless of the connection, it is susceptible to changes at synchronization time that leave it in an unstable state. Administrators should take care to pause synchronization whenever they plan changes to the source data or source data connection.

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 may delete them from the shadow table. FileHold determines if there are additions or deletions by looking at the Lookup By value on the administration page. 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. When the When deleted at source option has been configured with "Delete values", 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.

If you are planning changes to the source data, pointing to a new server, new tables, new files, etc., you should protect your data from accidental deletion of values and the trickle down effects to documents. If this is a one time change, the simplest protection you can take is to change the When deleted at source option to "Do not change values". When you have completed the change and confirmed synchronization is working correctly, you can switch back to your normal setting. Adding, deleting, or changing dropdown menu values are logged in the Library Audit Log.

There is no substitute for a proper backup of your system. Making changes like this are best done after hours and immediately following a database backup to simplify a restore scenario.

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 masse 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 Command shell 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.

Although this example uses the Windows Command shell, the same could be done with Powershell or similar tool.

@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

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. You can contact them at [email protected].