Lookup Table Basics

A Lookup table is a data entry tool that allows you to select from a list of valid entries. Any values that are entered via the keyboard can also be validated against the codes defined in the table.

When you click the ellipsis (...) button to the right of a field that has a Lookup table attached, a pick-list is displayed:

Lookup tables are a good way of ensuring consistent data is entered in a file. Many Lookup tables can be used in the same file and the same Lookup table can be used in many files. There is no restriction on the number of Lookup tables you can create.

The files to which Lookup tables are attached can be locked. This means that the Lookup tables cannot be detached and the structure of the file cannot be changed. By distributing locked files with Lookup tables attached, you can ensure that field data entry staff can only use the correct sets of codes and cannot change file structures. Locked files can also be password protected.

Lookup files have the type LOOKUP and are given the file extension LDT (e.g. LITHOLOGY.LDT). Once you have entered codes and corresponding descriptions in the lookup file, you must then compile it into a Lookup table. The Lookup table can then be attached to a field in a file and will be displayed as a menu with sub-menus.

Lookup tables serve two purposes.

Firstly, they are a data entry tool, minimizing the amount of typing required. Instead of typing codes, you choose them from a list. When used with the File Editor’s data entry shortcuts, (like
Ctrl+A, Ctrl+R, and Ctrl+T), lookup tables make it easy to enter large amounts of data with a few keystrokes. This system works well with touch or pen devices, which may not have a keyboard.

More importantly, lookup tables are a validation tool. Keystrokes are validated as they occur, which ensures that your data is correct from the start. There are numerous settings that control the way lookup tables perform this validation task.

Types of Lookup Table

You can create two kinds of lookup table: A flat lookup table, which consists of a single list of codes and descriptions, or a hierarchical lookup table, which consists of nested lists of code/description pairs. Each kind of lookup table is suited to a specific type of data.

Flat lookup tables

Flat lookup tables are suited to simple lists of codes and descriptions, like the logging geologists’ initials or simple geological codes:

Hierarchical lookup tables

Hierarchical lookup tables are suited to data with a distinct hierarchy in the codes. Government geological survey codes are one example; they typically use nested levels for age, primary rock type, and lithology. Other examples include dates, with nested levels for year, month, and day; and mineral property IDs, with levels for type, mineral field, and number:

Creating a Lookup Table

Creating a lookup table is a two-stage process. First, you create the lookup table as an ordinary Micromine file with an LDT extension. Once the codes and descriptions are entered or imported, you then compile the lookup table into a binary file with an LBN extension. The binary file can’t be viewed or edited by a user and is much more secure than the editable version.

Creating a lookup table can be broken down into 4 simple steps: creating the file, compiling the file, attaching it to the appropriate data file, and setting the validation behaviour.

Create the Micromine file

1. Select File | Data Files | New | New Lookup Table... 

or File | Lookup Table | Create

2. Fill out the dialog as shown: 

You will then need to complete this table.
In the first column labelled Code 1, you should put your unique codes, and in the second column labelled Desc 1- a description of each code, e.g. like this:

Compile the binary file

The Micromine file version of the lookup table is what you edit and maintain as administrator of the project. However, it’s not the file that the geologists in the field will use. The next step is to compile it into a binary LBN file.
To compile the lookup table:

1. Select File | Lookup Table | Complite from the menu

2. Enable the Enable validation and Case sensitive options, leaving the remaining options disabled

3. Click Compile to create the binary file. 

Attach the lookup table to a field in the data file

The next stage in this process is to attach the lookup table to the corresponding field of the Dh_Lith file. You can use it once this is done.

1. From the menu, select File | Open and open your table to which you want to connect the lookup table.
2. Right-click anywhere within the LITH field and select Lookup Tables | Attach from the pop-up menu. Or,
select Format | Lookup Tables | Attach from the menu.

3. Choose Lith_Lookup from the list and click Open. Micromine adds the ellipsis buttons to the LITH field
displayed in the File Editor. You could attach a lookup table to every field in a file. You might also attach the same lookup table to more than one field. 
4. Click an ellipsis […] button to open the lookup table and select the code you want.

5. Type a code into one of the LITH cells. Even though the lookup table is attached, you can still use the
keyboard in the normal way.
6. Type an incorrect code and move the cursor to a different cell in the file. The invalid code is accepted! You must complete the final step before the lookup table is fully set up as a validation tool.

Set the validation behaviour

In addition to enabling validation when you create a lookup table, you must also inform the File Editor that it must support validation. You control this behaviour by selecting Tools | Options | Editor from the menu.

1. From the Project Backstage Area, select Options | System | Editor Options and switch to the Lookup Table tab.
2. Enable Validate lookup fields to activate the remaining prompts.
3. In the Validation Options group, ensure that the Valid input compulsory option is selected. Your dialog
should look like this:

Now if you try to enter a value other than the one in the code field, Micromine will warn you that the value is incorrect.

Because of the chosen validation options, you can’t proceed any further without correcting the offending code. If you prefer to take a more flexible approach you can choose to flag incorrect codes for later correction instead. Simply choose the desired action in the Validation Options group.

Creating Hierarchical lookup tables is generally similar to the workflow described above for creating flat lookup tables. The only difference is that when creating a table, select more than one level.

You can also use lookup tables for wireframes. Simply connect the compiled table to the wireframe attribute table, and you can select the required code in the wireframe properties window.

Want to learn more?

Online Help Manuals - Click here for the latest version

Learning Management System - Click here to login or here to request access