Set Reference by Name

TMG Utility is the essential TMG companion tool. If you use it, please make a donation.


On this page: Description, Step by Step, File Format


Set Reference by Name sets the Reference field to a code derived from the surname and given name. Two tables--one for surname, and one for given name--are used to convert names to codes, and the two codes are concatenated to form a new Reference field value.

The basic idea is similar to Soundex. Through the tables, names are converted to codes that (typically) ignore differences due to spelling variations. Unlike Soundex, which uses an algorithm, the user has to create tables to derive the code. This "brute force" approach is more work, but also gives the user more control over the process.

The average user will probably not need this feature. It was written in response to a request from a specific user.

Step by Step

  1. Before using this feature, you need to create the surname and given name tables. See the File Format description below.
  2. Choose Set Reference by Name from the function tree.
  3. Click the first [Choose File] button to choose the surname table.
  4. Click the second [Choose File] button to choose the given name table.
  5. Key a character in the Fill Character text box. This character is used to construct the code when surnames or given names are missing from their respective tables.
  6. Set the Flag Filter, if desired. The Reference field will only be set for people that pass the flag filter.
  7. Click the [Set Reference by Name] button.

The log includes summary information only; it does not include an entry for every person whose Reference field is set. TMG's Picklist is a good way to validate that the proper codes have been assigned.

File Format

As noted above, this feature requires two data files, one for surnames and one for given names. The files have the same format.

Each record in the file should contain two data items separated by a tab character.

The items, in order, are:


This is the code value used when the person's surname or given name matches the Key. If the codes are not the same length, the program will pad the short codes with the Fill Character to match the length of the longest code.


This is the surname or given name that must match in order for the Code to be assigned to the Reference field. The program ignores differences due to upper- and lower-case letters.

The program ignores middle names, so the given name table should contain the first name only. The surname table should contain the complete surname.

Here's a short surname file:


In the examples above and below, takes the place of a real tab character. Tabs are not visible in web browsers, so I have replaced them with in this documentation.

Here's a short given name file:


When a surname or given name does not exist in the table, the program constructs a code by using the first character of the surname or given name and adding enough instances of the Fill Character to make the code the proper length.

The table below shows some example names and the codes that would be assigned to the Reference field given the tables shown above. Assume that the Fill Character has been set to "_" (an underscore).

Name Reference
Cardinal, John047jo
Cardinal, Jon047jo
Fehr, Donald048dd
Fehr, Don048dd
Fehr, Nothing2048N_
Smith, John049jo
Smyth, Jon049jo
Hoffman, DonaldH__dd
Lincoln, AbeL__A_