This lesson requires Registrations.fp7.

Auto Entry Field Options, when used appropriately, can significantly reduce many data entry tasks and track such things as when a record was last modified and who modified it. Using computer-generated unique serial numbers can be particularly valuable when working with relational databases. Each record needs a unique identifier in both the master database and the related database to connect the information sets.

Often, we think of a person's SSN as a unique identifier but this is not always the case. Many international students or immigrants are assigned temporary SSNs when they first arrive in the United States but are later assigned a permanent one. If a data entry person attempts to change an SSN when the SSN field has been used as a match field in a related database, all of the related records based on that SSN will become "orphaned" and no longer connected to the original master file. Therefore, many database designers define a serialized number field as a unique identifier.

If this is done when the database is first created, using the Field Definition -> Options dialog, you simply check the Serial Number option and all records will be assigned a unique serial number. If you have an existing database and wish to convert it to serial number relationships, you can assign serial numbers to the existing records by selecting Records -> Show All Records, placing your cursor in a newly defined Serial Number field in the first record then selecting Replace -> Replace with Serial Numbers and indicating your desired start number with an increment of 1. Then you sort your records by Serial Number in descending order and select the first record to determine the highest serial number assigned, select File -> Define Fields and set the Serial Number field to be Auto-Enter with the first value the next highest number with an increment of 1.

Now, let's try it with the Registrations.fp7 sample file.

1. Select File, Open and select the Registrations.fp7 file. Select Records -> Show All Records. Navigate to the first record and place your cursor in the "Registration #" field.

2. Select Records -> Replace Field Contents and click on the Serial Number radio button. Enter "1" for the beginning number and "1" for the "increment by" value and click Replace.

3. Now, right click on the Registration # field and select Descending Order.

4. Navigate to the first record and note the assigned serial number.

5. Select File -> Define -> Database and select the "Registration #" field on the Fields tab. Click on Options button and the Auto-Enter tab. Check the Serial Number checkbox and enter the next highest number from your highest assigned serial number then enter "1" in the "increment by" value and click OK. All new records will henceforth be assigned unique serial numbers beginning with the value you specified.

Once you have defined unique numbers to a master file, you can open files related on another field and, after defining a serial number field in the related file, set the number field as a lookup based on the original match field to transfer the serial numbers to the related files. Then you can reset your relationships to be based on the new serial number match fields.

 

COE Techweb

COE Home

UO Home