2 Replies Latest reply on Nov 15, 2011 7:04 AM by lindeeg

    Best way to import/update/add records to distributed runtime?


      Question: What is the best way to import/update/add records to multiple tables in a distributed Filemaker runtime solution where users could have added new records themselves?



      A networking group of which I am a member sends out an updated Excel member roster via email once a week. After the second time the data was mis-sorted and everyone had new birthdays, I modified the Contacts template to suit our group and started providing run-time versions (Mac & Windows) for members—which I have to update weekly as well. (They must download the new runtime weekly - approximately 50mb each zipped.)


      Template modifications include additional related tables for addresses, phone numbers, and web connections. I have also added to the data from the spreadsheet: clickable links to members' email, Facebook profiles & pages, websites, and other social networking profiles along with a picture from their facebook page. (It is a requirement that we connect with every one in the group, currently about 115 members.)


      I can script an import/update/add easily enough that would add the new information and update any existing records. However, things get tricky if they've added any new records to any of the tables (other than notes), such as a contact, phone number, connection, or address. In that case, since I'm matching on ID, I could be updating the wrong records. If I automatically update serial number ids on import, then how will the related fields get connected properly?


      In reality, most members will not add any new contacts or other records (most are "technically challenged" to say the least!); they are simply using it to speed all this Friending and Liking. However, I have not blocked them from adding any new records and would prefer not to. My goal is to show them why a Filemaker solution is preferable to lists in Excel. I would like this to be representative of a "real" solution they might use in their business.


      It is likely that some members will add a phone number or note. With the current method of getting a new runtime weekly, it will wipe out anything they have previously added.


      I would like to just post a much smaller fp7 file that they can download and then run an update script to import/update/add that will work no matter how long it has been since they ran the last update. This will also let the couple of members who have Filemaker Go use this file on their iPads. Plus, I would like to avoid having to make 2 new runtimes each week and upload them. Additionally, the stand-alone file would be small enough that I could email to all members so they wouldn't need to download after the first time.


      Barring a hosted solution, what suggestions do you have for an import/update/add that won't hose up any data they may have added? We add 2-4 new members weekly and there is usually updated information on at least that many others. Also I don't imagine that every member will take the time to update every week, so just posting the changes is probably not the way to go.




        • 1. Re: Best way to import/update/add records to distributed runtime?

          Hi Lindee,


          Assuming there is a unique primary key for each record, you should be able to include an import script that will replace only the original records without affecting any new records added by the user, by configuring a scripted import to use the "Update matching records in found set" option, with the "Add remaining data as new records" option checked (to enable you to include extra records in your updates).


          Alternatively, you could use a field to track the creation account name, then configure your update script in the runtime to search for all records created by you mark them for deletion, import the new data, then - if the import completes successfully, locate the marked records and delete them (I'm assuming you wouldn't want to have the runtime delete the original data until it's confirmed that the import of updated data was successful).





          R J Cologon, Ph.D.

          FileMaker Certified Developer

          Author, FileMaker Pro 10 Bible

          NightWing Enterprises, Melbourne, Australia



          1 of 1 people found this helpful
          • 2. Re: Best way to import/update/add records to distributed runtime?

            Hi Ray,


            Thanks for your response. At the moment I have an import script that is doing as you describe in the first paragraph on each of 3 tables. However, as I read your reply, I was wondering if I could separate them - do an update matching without adding, and then run it again doing an update matching on  a "fleld" I'm using as a separator in the table and do the add with that import. I'm not sure exactly what that would buy me since the found set at the end doesn't have just the newly added records.


            Your second paragraph gives me a good idea. I do track create/mod account names & timestamps in each of the data tables so possibly keying on id (the unique primary key) & create account name will give me the solution I need. Also, since I'm not updating any of those fields during import, I can still find the new records based on that criteria and do some extra verification. i hadn't thought of that before. I'll give it a go and see what I can work out.


            Thanks again!




            PS I have enjoyed your many contributions to the Filemaker community through out the years and have learned "heaps" (as my Aussie friends say) from you!