FMquestions Posted November 15, 2011 Posted November 15, 2011 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 Posted November 15, 2011 Posted November 15, 2011 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 Posted November 16, 2011 Posted November 16, 2011 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 Posted November 16, 2011 Author Posted November 16, 2011 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 Posted November 16, 2011 Posted November 16, 2011 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 Posted November 17, 2011 Author Posted November 17, 2011 Thanks for the help! It was something as small as command instead of a period in the unique ID.
Recommended Posts
This topic is 4763 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now