3 Replies Latest reply on Mar 5, 2014 4:54 PM by user19627

    Need script to import .csv data each month without losing existing data

    user19627

      I'm new to FM and trying create a script that will import data we receive from our parent organization each month. They keep a master membership list with contact info and send us (regional division) an updated list each month that contains all the current member data, while adding new members and dropping those who have resigned.

       

      The initial import is simple enough and setting up a recurring import is simple. However, I don't want to replace all of the data after the initial import. I need to match existing records and update those, while at the same time adding new records that don't yet exist in our database and remove records that have been removed in the .csv source file each month.

       

      The member number is set by the organization and is the ID for each record.

       

      Is there a way to do this in FM? I've tried importing two months as separate tables and then using a layout based on table 1 with a portal from table 2 to do a find or sort that identifies records that don't exist in one of the two tables. Can I script this somehow and automate the process so that I can import the monthly update and then run a script that does the adding, deleting, and updating of the records in a master table?

       

      I'd appreciate any suggestions!

       

      Thanks,

       

      Jason

        • 1. Re: Need script to import .csv data each month without losing existing data
          Stephen Huston

          FileMaker supports a choice in the Import options to "Update matching records in found set" and another option that can be checked to "Add remaining data as new records."

           

          Using those two together can allow you to import from a data set, matching on the membership number to update existing records, while adding new records for the new membership numbers to the database.

           

          You just want to be sure you have done a "Show All records" before starting the import, so that all exsiting records are part of the found set for updating.

          • 2. Re: Need script to import .csv data each month without losing existing data
            wimdecorte

            Yes you can script this.

            Your fundamental approach is sound:

            - import the new data into a "temp" table

            - then go through that data evaulate whether it is: new, removed, modified or unchanged

             

            These last 4 decisions are not generic, they depend on your data and business logic.  Part of that decision is: is the new data sent down in the master list is truly the master and can always overwrite any local data changes you have made?

            • 3. Re: Need script to import .csv data each month without losing existing data
              user19627

              I think it's that last decision making process that has me stymied.  Sometimes our contact data for a member at the regional level is more current than the parent organization's data that they send out in the monthly update files.  I don't want to lose the most current data just because there's a delay between when we send the update to the parent organization and when it gets processed and sent back down the chain. 

               

              I was thinking of using a auto calculated modified date field that I can cue off of in order to determine which records should be overwritten and which should not.  Would that approach work?