Technical documentation > Importing data

Finding an existing person in FastTrak

When you want to import data, it is obviously important to find the right person, so that the data will not end up in the wrong place. It is almost equally important to avoid adding the same person more than once. The mapping can be done with a couple of procedures in the FastTrak database:

EXEC MapPersonByNationalId '28113212333'

EXEC MapPersonByDobName '1932-11-28','John','Doe'

Both calls are guaranteed to return exactly 1 row of data.. This row contains the field PersonId. The value of PersonId will be 0 if there was no match, negative if there were multiple matches, and a positive integer for a single match. A positive integer is a valid PersonId. Please note as a general rule that the order of fields in a dataset are not guaranteed, so the data should be accessed by field name.

Adding a person

If no match was found by the two previous methods, you may add a new Person. There is no actual harm in adding a Person that already exists. However, the name will not be updated for an existing person (with the same NationalId). A call to AddPerson returns the PersonId for this person, regardless of this being a new person in the database or not.

EXEC AddPerson '1932-11-28','John',NULL,'Doe',1,'28113212333'

This is the definition of AddPerson, as extracted from the FastTrak database:

PROCEDURE AddPerson( @DOB DateTime,

@FstName VARCHAR(30),@MidName VARCHAR(30),@LstName VARCHAR(30),

@GenderId INT,@NationalId VARCHAR(16) = NULL )

Parameter	NULLABLE?	Comment
DOB	NOT NULL	Date of birth
FstName	NOT NULL	First name for person, can not be null, because FstName and LstName are in a unique constraint that allows no nulls.
MidName	NULLABLE	Middle name (avoid using this)
LstName	NOT NULL	Last name for person
GenderId	NOT NULL	0 = Unknown, 1 = Mae, 2 = Female
NationalId	NULLABLE	NationalId, up to 16 characters. Avoid spaces.

The final parameter can be NULL, and the one before that should be 1 for a male and 2 for a female.

What to choose

It is really simple to use AddPerson if you know a NationalId. However, if you don't, you should look for the person by date of birth and name, using MapPersonByDobName. If this also gets you no match, you should add the person with AddPerson.

Comments

The fields DOB, FstName and LstName are used in a unique index on the Person table. Theoretically, this could seem to be a bad move. There could be situations, notably in Denmark, Vietnam and Korea, where the exact same names and date of birth apply to more than one person. However, if date of birth, first name and last name are all the same, there will be a high risk of mixups. So a reasonable action to take is to add something to the name that will help system users to distinguish between these people.