5 Replies Latest reply on Nov 16, 2012 9:20 AM by philmodjunk

    add match field to records retroactively?

    AEH

      Title

      add match field to records retroactively?

      Post

           In FM Pro 10, the match field we use to connect most tables is the "PersonID".  One table was created without the PersonID field, and connected via another field (ApplicationID).  After many records were entered, we realized that in order to make the information accessible through other tables, it needed to be connected via the PersonID.  But after this connection is made,  the PersonID field is empty for the records which were entered previously.  Is there a way to make the PersonID field retroactively populate with the correct matching ID number for these records?

            

        • 1. Re: add match field to records retroactively?
          philmodjunk

               There's a nice tool for making "batch" updates of large groups of records in FileMaker. It can't be undone and could modify a vary large number of records, so make a back up copy before you try this.

               Perform a find for all records where the match field is empty. You can enter a lone = into the field to find all records where the field is empty.

               Then use Replace Field contents to add in the missing data into the match field. You can probably use the calculation option and your original relationship based on ApplicationID to access the correct value from a related table.

          • 2. Re: add match field to records retroactively?
            AEH

                 "You can probably use the calculation option and your original relationship based on ApplicationID to access the correct value from a related table."
                  

                 Can you advise how to make this calculation?  The rest of the advice makes sense.

                  

            • 3. Re: add match field to records retroactively?
              philmodjunk

                   I can't, in detail, without knowing what relationships you have in place and how you intend to change them once you have the PeopleID field updated for your existing records.

                   Presumably, there is a related table that contains the PeopleID value you want when you refer to a related table via a relationship based on your original ApplicationID match field. IF so, you can specify that field from that related table as the sole term in your relationship. If this is not possible, then you will need to explore other ways to get the correct value into this field via Replace Field Contents.

              • 4. Re: add match field to records retroactively?
                AEH

                     Thanks for the reply.  We are exploring another way of fixing the problem; if that doesn't work, we may have to come back for more explanation!

                      

                • 5. Re: add match field to records retroactively?
                  philmodjunk

                       I see a major "brain short circuit" in my last post.

                       

                            If so, you can specify that field from that related table as the sole term in your relationship. If this is not possible, then you will need to explore other ways to get the correct value into this field via Replace Field Contents.

                       should be:

                       

                            If so, you can specify that field from that related table as the sole term in your calculation. If this is not possible, then you will need to explore other ways to get the correct value into this field via Replace Field Contents.