7 Replies Latest reply on Jan 19, 2012 5:28 AM by bobatkinson

    Looking up field data from a different FMP file ... Help !!

    bobatkinson

      Hello;

       

      This may seem simple, but I'm a relative beginner and stumped. I have two different FMP 10 files which have the same fields. One of them (A) has missing data in several fields across all the records, The other file (B) has all the data for those fields. I can use my AccountNumber field to match records between the two files and I want to do a one-time Lookup to grab the data in several fields from file B into the empty fields in matching records (by AccountNumber) in A.

       

      HOW DO I DO IT????

       

      Thanks.

        • 1. Re: Looking up field data from a different FMP file ... Help !!
          LyndsayHowarth

          This picture shows the settings to use for this kind of IMPORT ACTION

          Note that I have a Patient ID where you will have the Account number

           

          Picture 5.png

           

          You may also wish to check the "Add remaining data as new records"

           

          HTH

          - Lyndsay

          • 2. Re: Looking up field data from a different FMP file ... Help !!
            bobatkinson

            Hi;

             

            Thanks for your reply.   I think the trouble I'm having is that the place I want to get the data from is in a completely different FMP 10 file (with a common field to match with), not a table in the same FMP file.  How can I hook that up?

             

            - Bob Atkinson

            • 3. Re: Looking up field data from a different FMP file ... Help !!
              PSI

              Bob,

               

               

               

              I read these posts as emails and I haven’t been following this thread that closely. As very few seem to quote the previous comments it makes it hard to know what came before. So if this is redundant…

               

               

               

              You can just put the table from file B on the graph of file A. Then create a relationship from the table in file A to this table. You will need a field in table A to hold the primary key of table B, usually called a foreign key. Create the relationship from A to B using the Foreign key(A) to the primary key(B).

               

               

               

              Hope this helps

               

              John Morina

               

              Pueblo System, Inc.

               

              john@pueblo-systems.com

              • 4. Re: Looking up field data from a different FMP file ... Help !!
                LyndsayHowarth

                In my example, the source B.fp7 was a separate file.

                 

                If you are on the layout for the correct Table in file A and you choose File>import Records> from a file... you get a dialog asking for file B which you choose.

                THEN, you get to choose which table from File B you want to import into the File A table...

                 

                What John is suggesting is a more permanent relationship between A & B which you may or may not want. Doing it as an import creates no permanent relationship.... but can be scripted for re-doing at another time.

                 

                - Lyndsay

                 

                PS Sorry John about the lack of quoted text... but when you use the community online it is unnecessary and annoying as it is just duplication of what's above.

                • 5. Re: Looking up field data from a different FMP file ... Help !!
                  MarcDolley

                  Another alternative to Lyndsay's approach, only because I've never trusted the import and update process to not screw up other data, is to do a calculated replace.

                   

                  Assuming FileA is the main file and FileB is the one containing the data you wish to add, create a table occurrence in the graph to FileB and link the Account Number field between the two TO's. Then show all records in your table in FileA. Click in the first field to be updated and select Replace from the Records menu. Select Calculated Result, choose FileB from the drop down menu and select the field containing the data you wish to bring in. Click Replace and wait.

                   

                  Repeat this process for each field you need to update. When you're done, go back into the relationship graph and delete the FileB table occurrence.

                   

                  Possibly a little more complicated than a simple import, but you have more control because you're updating on field at a time.

                   

                  Regards

                  Marc

                  • 6. Re: Looking up field data from a different FMP file ... Help !!
                    LyndsayHowarth

                    And... if using something like Marc's approach... which is similar in part to what John suggested... you can build in calculations to check the data you are importing:

                     

                    eg... the two tables could have a last modified date so the calc could choose for the A-master to only take the data if this date in B is greater that the date stored in A

                     

                    - Lyndsay

                    • 7. Re: Looking up field data from a different FMP file ... Help !!
                      bobatkinson

                      Hi Lyndsay, Marc ad John;

                       

                      Thanks for your relp -- I think the underlying problem is that I think flat-file rather than linked/relational, which limits what I can do.

                       

                      Let me work on it a bit and see what I can do ...

                       

                      - Bob Atkinson