Importing Related Data

[FMquestions]

Hi,

I am trying to import information from an excel file while maintaining the relationship. This excel file has 3 sheets, each corresponding to the 3 tables: People, Object, and Measurement. This is a one to many relationship with a person having multiple object and each object having multiple measurements. These objects are related by the primary key in the people table. Here is a simplified version of what the excel file would look like

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

What I have tried doing was to import the people sheet into the people table first. Then I go to the Object table and import the object information. This time, I update the existing records instead of adding new records. I make sure to line up all the fields correctly. I did the same for the measurement sheet. Even though all the primary keys are correctly imported to the right fields, the records are not related. In another layout where I display the related object in a portal for one of the person, no object is displayed. Does anyone have any suggestions? Thank you in advance.

[comment]

This is probably not related to the issue at hand, but I don't see why you need to "update the existing records instead of adding new records" when importing some of the data.

In any case, if no related records are showing where they should, then your data is not what you think it is. It could be as simple as a trailing space.

[LaRetta]

In another layout where I display the related object in a portal for one of the person, no object is displayed.

Does it display for any of the people? If not, you might also check the portal and make sure it is the correct table occurrence and that the fields within it are same table occurrence as the portal. Also, it seems you understand primary keys and how to use them so I'm probably fussin' for nothing but ...

"What I have tried doing was to import the people sheet into the people table first. Then I go to the Object table and import the object information."

You are naming your IDs like they are proper unique serials. Are they? I ask because, if you import the people, they will only THEN receive the PersonID. How will Objects get the newly assigned fk_personID? Or are all the IDs already assigned (and included in the import) and you are NOT performing auto-enter during your import?

[FMquestions]

At first, I was updating the records as opposed to importing new records because I thought it would help to maintain the relationship between the different records.

Yes, the IDs are unique serials. In the spreadsheet, the objects are already assigned the unique serials. When I import the object table, the fk_personID is also imported and I am not performing auto-enter during the import. When I look at the object table, I can see that the object has the correct IDs and it match the ID from the people table. However, FM is not recognizing that the records are related.

Is there another method you would suggest to import this to keep the relationship?

Thank you both in advance.

[LaRetta]

It will have to be something simple, such as space at the end; did you check one specific example (the PersonID in both Person and Object tables) and make sure there was not a space? Did you check the portal? Do all records break or just some?

You are not answering all of our questions and there are reasons we ask them. You did create a relationship in the graph, right, and relate Person::PersonID = Object::PersonID? You have some relationship because you can create a portal. If there are other table occurrences of these tables other than what you specified, you must make sure the layout is in the same table occurrence group (TOG) as the portal.

Are the data types of PersonID the same in both tables? Other than that, we will need to see the file. You can create an empty clone, zip it, and post it here if you wish. :-)

[FMquestions]

Thanks for the help! It was something as small as command instead of a period in the unique ID.