How to: Use Surrogate key, Surrogate Foreing key, Surrogate key replacement fields and Alternate key

A surrogate key is a system-generated value that acts as the primary key of a table. A surrogate key is typically used in place of a set of fields, called the natural key, which would normally act as the primary key of a table.

Have you ever had need to relate two tables using RecId but you have to show a human-readable field instead? (RecId is not one!)

Well, for this we have to use surrogate keys and I will show you step-by-step how to do it using a simple example.

1. Create the table HeaderTable with the following properties

Properties	Value
Name	HeaderTable
Replacement Key	NameIdx (Must Create Index 1st)

Fields:

Field	Extended Data Type
Description	Description
Name	Name
AccountNum	AccountNum

Indexes:

Properties	Value
Name	NameIdx
AllowDuplicates	No
AlternateKey	Yes

Properties	Value
Name	AccountIdx
AllowDuplicates	No
AlternateKey	No

Table Structure should look like this:

3. Now, lets create the Form:

Properties	Value
Name	LineTable

4. Drag and drop the LineTable on form to use it as data source.

5. Create a new Grid on Design and then drag and drop the fields Account.
Important: It should create automatically a Reference Group Control for AccountNum, if you are creating an alternate key for an existing table you have to delete the form field and create a Reference group Control manually.

Form Structure:

6. Inserting some information on HeaderTable:

7. Inserting information on LineTable through Form:

Notice that only the name is shown to user.

8. Let’s check the Line Table

As you can see the difference, on LineTable form it’s shown to user to select name instead of RecId!
Alternate key is the option to go when you need to use RecId but you have to show a “human-readable” field instead.