3 Replies Latest reply on Aug 17, 2013 9:05 AM by dnfmptn

    Related records goof, linking existing records

    dnfmptn

      Hi all;

       

      Database novice here, please don't flame me. I searched forums for these terms and didn't find relevant results.

       

      I have a database with a handful of tables for tracking contacts in a business context. Person and Company are two of the tables. It turns out in this industry people often work for multiple companies which I didn't realize setting up the db.

       

      So I deleted the companyID-personID relationship and set up a join table so multiple people can be from multiple companies.

       

      And then all the company relations to persons were gone.

       

      Experienced users probably saw that coming!

       

      Anyway, now I have a company portal on the person layout and if I enter a company name it creates a new company record.

       

      What I need to do and can't figure out is

      1) how to link existing or new person records to their existing company records, or

      2) how to recover the earlier relationships.

       

      Thanks!

      dave

        • 1. Re: Related records goof, linking existing records
          Mike_Mitchell

          Hello, Dave.

           

          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.

           

          HTH

           

          Mike

          • 2. Re: Related records goof, linking existing records
            dnfmptn

            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!

            • 3. Re: Related records goof, linking existing records
              dnfmptn

              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!

               

              Dave