3 Replies Latest reply on Jun 18, 2013 9:11 AM by philmodjunk

    Checking data when importing

    trent.fowler@hci.utah.edu

      Title

      Checking data when importing

      Post

           Hello FM Forum,

            

           My database has a list of patient records, I get an updated list of patients every month in an excel format from an outside source. I am wondering the best way to import these records in such a way that the data that I have entered will not be changed, but the updated data will be. Is there a way to compare the records as they are importing and have the system ask me which version it ought to import? Also, is there a way to have filemaker let me know when a record has been removed from the excel sheet (patient deceased)?

           Each patient has a unique identifier given by the external source. Any thoughts or suggestions would be very welcome.

        • 1. Re: Checking data when importing
          philmodjunk

               Sounds like you should import your data into an intermediary table so that A script and a relationship between it and your main file can be used to compare data on a layout where you can see the data in each record of each table. Then buttons/scripts can be used to move the data you want to be moved into your main table

               Hopefully, there is a column in the Excel data that uniquely identifies each patient. If so, then you can set up a relationship matching records in the two tables by this field.

               As to deceased patients. What data in the Excel file identifies a patient as "deceased"? There no longer is a row with that patient's ID? There's a column that has a value in it if the patient is deceased? Or ???

          • 2. Re: Checking data when importing
            trent.fowler@hci.utah.edu

                 Thank you! Worked perfectly. 

                 A deceased patient is simply removed as a total row from the spreadsheet. We have other fail-safes in place to catch this so its not a problem to leave this. 

                 Thanks again for your help.

            • 3. Re: Checking data when importing
              philmodjunk

                   Then a layout where the newly imported data is compared to your existing data can show which patients are deceased as there will be a record in your main table that no longer matches to a record in this intermediary table.