Using Calculation to create unique ID - still able to maintain relationship?

[FMquestions]

Hi,

I have a database with let's say 3 tables: People, Object, and Measurement. I want to use calculation to create the unique ID for the Object and Measurement table. For instance, the object_pk would be "PeopleID_ObjectDate" and the Measurement_pk would be "PeopleID_ObjectDate_M_MeasurementNumber". Since these ID are calculation fields, I would also have to make the foreign key in the other table to be calculation field so that the ID numbers match. However, I would have to specify what the calculation is if I specify the field as a calculation field. Should I use a lookup value then?

The reason I am creating ID in this fashion is that sometimes the people, object, and measurement information are imported from a spreadsheet and sometimes manually entered. The IDs on the spreadsheet are created in such a fashion. Thus, I am trying to create similar IDs in the FM database.

Any help you can provide will be greatly appreciated. Thanks.

[comment]

sometimes the people, object, and measurement information are imported from a spreadsheet and sometimes manually entered

What is the exact format of the imported data? Is it a flat table, or are there keys to connect related data?

[FMquestions]

The imported data has keys to connect related data. Here is a simplified version.

People (sheet 1)

pk_personID

LastName

FirstName

Object (sheet 2)

fk_personID

pk_ObjectID

ObjectNumber

ObjectName

Measurement (sheet 3)

fk_personID

fk_ObjectID

pk_MeasurementID

MeasurementA

MeasurementB

[comment]

Please provide a bit more background here: how do the people providing the data generate those IDs and ensure their integrity? I am assuming that some of the imported people and/or objects may already exist in your solution, and you wouldn't want the import to produce duplicates.

[FMquestions]

Yes, you are correct in that I am trying to avoid duplicates. The people providing the data generated the ID in the fashion I described earlier. The pk in the People table is a unique 7-number series that has been generated for each person. A person can have multiple objects and each object can have multiple measurements. The pk in the object table is created by adding the a date to the 7-number series from the People table (i.e. "0039513_10/22/1989"). Then the pk in the measurement table would take this value and add a letter and a number (i.e. "0039513_10/22/1989_M1", "0039513_10/22/1989_M2", etc). The 1 and 2 refers to the measurement number. If the information is being imported, these values are already listed in the spreadsheet and as long as the fields are lined up correctly, the relationship between each table is maintained.

Now, if for instance, someone wants to go back and add some more measurement information to a person that is already in the database, I would want the database to generate an ID that follows the same format. If someone is going back to add the 3rd and 4th measurement, the ID would look something like "0039513_10/22/1989_M3" and "0039513_10/22/1989_M4". Does this make sense?

[comment]

I would want the database to generate an ID that follows the same format. If someone is going back to add the 3rd and 4th measurement, the ID would look something like "0039513_10/22/1989_M3" and "0039513_10/22/1989_M4".

I don't think you want to do that. Because if they add another measurement, it's going to be the third measurement for them - but you already have a third and a fourth one.

I'd suggest you use auto-entered serial numbers for your primary keys and disable auto-enters while importing. To eliminate the possibility of a collision between your serial numbers and their keys, use an arbitrary prefix for your serials - for example, number your people as P1, P2, P3, etc.

This is assuming "they" know what they are doing and will not trip you up by generating a duplicate ID in one of their tables.

[FMquestions]

Thank you for the comments. Actually, the database will automatically count the measurement number. If 2 measurements already exist, when the user enters the next measurement, it will automatically be marked as the 3rd measurement.

I was trying to have both the imported data and the manually entered data to have the same format for the IDs. I guess as long as they are unique, it will be able to maintain the relationship between the different tables, which is the most important thing.

[comment]

I was trying to have both the imported data and the manually entered data to have the same format for the IDs.

There is no need for that, I think. OTOH, producing a unique ID based on other records is not reliable; for example, a record could be deleted, and then a duplicate ID would be produced. It's much better to stick to meaningless serial numbers.

[FMquestions]

That's a good point. Thanks for the help.