Not to worry; we're very newbie-friendly here. No flames.
Okay, I've worked up a brief example file (see attached) that should help illustrate the situation (assuming I've understood it correctly). You need to move the companyID from the Person record onto a corresponding join table record. Take a look at the Person Entry layout. This shows the difference between the companyID field stored on the Person record and the portal to the join table.
Now, to fix the old relationships, take a look at the Join layout. I've included the person::companyID field on that layout. In order to create join records, here's what you can do:
1) Do a Find on a layout pointing to the Person table. Locate all the records you need to create new join records for. (It may be all the Person records, in which case, just do a Find All.)
2) Go to the Join layout. Do an Import Records, importing the key field (personID) from Person to Join. (Just that one field.)
3) When you finish step 2, you'll have a found set of Join records with all the needed Person IDs, but empty Company IDs. Do a Replace Field Contents (Ctrl / Cmd - =) and replace the companyID in the Join table with the person::companyID value. This will establish the join between the two tables.
JoinExample.fmp12.zip 17.0 K
Mike, thanks a bunch!
As a newb I'm almost with you on that description. You're using terms very precisely and I don't use them every day so I have to hesitate and ponder.
I do already have a join table as shown in your example db relationships graph, and also as described in the online FM help section. I have to really look your steps one to three. I don't have a 'join layout' other than a person-based layout with a portal to phones, emails, companies and contacts. It's that companies portal I need to populate with the pre-existing relationships, sounds like what you're calling person::companyID values.
I saved a copy of the previous db before I inserted the join table and removed the one-to-many company-to-people relationship.
Anyway, I have about 2 dozen business cards to respond to here urgently from a trade show and then get back to this. I had hoped to enter the cards and work from the db but it's good old pen and card for now with catch up data entry later. Will be back on the topic tonight/tomorrow. Anyone else feel free to elaborate if you can keep up with Mike!
Hi Mike, I'm back on task here and have spent about an hour without results. DB is small enough that I will rephrase this question in a new discussion, hope you have a chance to check it out, I'll call it "Best way to show multiple company records for a person".
I got stuck at your step 2. I tried to go File > Import Records > From File... and the import option pane on the right, the destination table, did not have any choices, ie the tables listed were all grayed out.
Anyway, I think I have the idea that instead of using companyID from the Company table in my layout, I should be using the companyID from the join table.
Thanks for you efforts and the example, I need to find a solution soon. Complicating things, I now have a cold so thinking process is a bit off too!