2 Replies Latest reply on Oct 20, 2015 3:00 AM by bradarnett

    Is there a way to automate related field selection?

    bradarnett

      I have created a new FileMaker solution and imported separate 'Client' and 'Job' tables. Due to the way they were created on the old solution the names in the client field in the 'Job' table match the clients in the 'Client' table, but they are not actually linked through the relationship. I can go into a 'Job' record and select the matching client from a drop-down field, which correctly assigns it, but I was wondering if anyone knows of a way I can automate this process. There's around 7500 job records and going through each manually assigning the relevant client will take me an age.

       

      Thanks for your time,

      Brad

        • 1. Re: Is there a way to automate related field selection?
          Mike_Mitchell

          Hello, Brad. I'm assuming, since you say the client names appear on the jobs, that what you're actually updating is the client ID fields. If that's the case, here's what you can do:

           

          1) Create a relationship (temporarily) between client and job based on the name. It can be an additional TO on your Graph (that's what I'd recommend) or you can just change the default relationship (again, temporarily).

           

          2) Use Replace Field Contents to insert the ID, using a calculation of ClientByName::ClientID. This will automatically populate the client ID field in all the records with the appropriate value wherever there's a match based on name.

           

          3) Do a Find for any that still have empty IDs. Fix them manually.

           

          There's a caution here: Make sure you don't have any duplicate names, otherwise you might end up with the wrong ID being inserted.

           

          HTH

           

          Mike

          • 2. Re: Is there a way to automate related field selection?
            bradarnett

            Thanks Mike, that worked perfectly.

             

            Brad