Overview

The Datgel Lookup List Check Tool scans the current project's fields to see if the field requires a lookup, compares the values of the fields with the values in the lookup source, and then writes any problems in a text file. This tool is useful when checking if imported data is compatible or compliant with the lookup lists in the current database/library, and is also useful for checking the integrity and validity of the existing data in the database. 

Using the Tool

The Tool is available from INPUT | Add-Ins > Datgel Administrator Tools > Lookup List Check Tool.

There are three parameters to set before executing the Lookup List Check Tool.

• Report File – specifies the full path to the file where you want the report file to be saved to. By default, the file name will be set to <<project file name>> - <<library file name>> <<date time>>.txt but can be changed to anything else as required. Click the … Box to browse for a file path.
• Project Select Options (Only for Enterprise Databases) – The Project Select options allow you to select which projects you wish to check lookups in. The Current Project Only option checks the current project, All Projects checks every project, and Selected Project(s) allows you to select multiple projects, by clicking Browse and then selecting the projects you wish to use.
• Search Options – the search options determine which lookups the tool will search and check. The options will refer to the Must use Lookup and Allow additions in Input check boxes that can be seen in field properties or DATA DESIGN | Project Database.
  
  • Exclude Field/Table Filter items are defined in library table DG_ADMIN_LOOKUP_LIST_CHECK_FIELD_FILTER.  It allows you to not report problems for a list of table.field names.  You can set the definition in multiple ways, explicitly such as <<DEFECTS.Infilling_Material>> or with wild characters such as *.Tested_By.  <<>> are optional.
  • Must use Lookup Only option will only search for and check fields that only have Must use Lookup checked in the field properties.
  • Must use Lookup and Allow Additions in Input option will only search for and check fields that only have Must use Lookup and Allow additions in Input checked in the field properties.
  • All Lookups option in the Lookup List Check Tool will search for any field that has a value in the Lookup property in the field properties, regardless of any check boxes ticked or not.
  • Check if Lookup Source Exists Only (Don't Check Values) option is useful when designing a database to help you identity for lookup configurations with broken or missing references.

When these parameters have been set, click Execute to begin the search process. A progress bar and status text on the bottom left will show you how much of the process has been completed. Note that with large databases (>50MB), the execution process may take some time to complete (5+ minutes).

When the process has been completed, a report file will be generated at the specified path and opened automatically for viewing. The report header and footer contains general info, such as time commenced and total time taken, project and library files and path, the search option chosen. The body of the report contains a list of all the fields that had an error with finding the lookup value. The error will also specify exactly what the erroneous value is, as well as exactly where, by listing the key fields and values of the record where the value exists. The results are sorted in alphabetical order by table name, then by field name