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.