Manual: Settings-KeyTranslation

The definitions for which Database data fields are reference keys to other tables can be configured via Setting=KeyTranslation, in the general form:

datafield=>table(datafield{+datafield ...})

Certain processing functions (notably, Bulk Load and Bulk Update) on the site will use these definitions to translate a data value into a suitable Key within the Database to maintain data integrity. This mechanism permits the knowledgeable user that is preparing a file for Bulk Load or Bulk Update to utilize recognizable character strings on input instead of the database required Keys (which are less than human friendly).

A Simple Example

For an example, assume that Setting=KeyTranslation contains the following specification:

dmaField=>Field(dfdName))

When a file contain the dmaField column is presented to Bulk Load or Bulk Update, the value presented will be tested for containing a numeric value. If it is numeric, the value will be stored into the dmaField database field on Insert or Update.

If the data value present in the input file is NOT numeric, KeyTranslation logic will utilize the value presented to query the "Field" table's "dfdName" data field to locate a matching dfdName record key. The resulting key (dfdKey) becomes the actual value that will be stored into the resulting dmaField database field.

A More Complicated Example

Assume that Setting=KeyTranslation contains the following specification:

dmaField=>Field(dfdName),
dmaAway=>Teams(dtmName),
dmaHome=>Teams(dtmName),
dmaRef=>Users(dusFirstName+dusMiddleName+dusLastName),
dmaAR1=>Users(dusFirstName+dusMiddleName+dusLastName),
dmaAR2=>Users(dusFirstName+dusMiddleName+dusLastName),
dmaMtr=>Users(dusFirstName+dusMiddleName+dusLastName)

The dmaField value continues to be translated, exactly as "A Simple Example" describes. Additionally, both dmaHome and dmaAway are Key fields in the Matches table that will permit a translation on input by searching the dtmName field in the Teams table.

Finally, all 4 Referee Position fields in the Matches table can be translated to a Users table key by using a combination of dusFirstName, dusMiddleName, and dusLastName on input.

Notes

The usage of KeyTranslation can greatly simplify the usage and preparation of offline data files for input to the Bulk Load and Bulk Update mechanisms. The following Notes may be of interest to Users of these mechanisms:

1. Character data values are searched for by removing all embedded blanks and are NOT case sensitive. Thus, "Joe Smith","joesmith","j o e sM i th ", and "JOESMITH" are all considented as being identical and will result in a proper Translation.
2. Any attempted Translation that results in a "not found" condition will be considered a Bulk Load or Bulk Update error and will terminate processing during "Validate" action processing. If you would like to leave the data field as "unspecified" in the database, set a 0 (zero) into the data value.