Rather than answer the question you asked, esentially 'how do we keep using a spreadsheet as a database and use it to update the REAL database', let me encourage you to choose a different approach.
If you are indeed using FMP so that you can use your data more effectively, then enter the data and update the data directly in FMP instead of XL.
Post the FMP Dbase to your common storage every morning instead of the spreadsheet and have the updates typed right in the database.
Will there be resistance because it "looks different"?...probably. Will there be resistance because "we've always done it this way"?...probably.
But you want to use the data more effectively...perhaps you might want to enter and modify the data more effectively as well, no?
Just a thought...
I tend to agree, if you have FMP now I would use it completely. You can always spit out a report in Excel format for those that want one to look at. You can, however, display, manage, and manipulate your data much easier and more efficiently with FMP alone.
I would do that but what I am trying to cover is the interim period. There are a total of 4 users but they are spread all over the country and using Dropbox to sync the data. A lot to bite off in one go to have the database go live first. Once the sync was working and all the layouts finalised the plan was to switch to Pro then.
You will have to have a maintenance time when all users are unable to use the spread sheets while you go live also, although it should only take a few minutes.
There would actually be more work involved trying to update the excel sheets with something else if it could be done. What I am wondering is how you intend to share the database without FMP Server? Do you plan on using the IWP and hosting it somewhere at one of the locations?
I don't think you'll want to use Drop Box to update your FileMaker database once you get it up and running. Instead, you'll either have your users use FileMaker Pro and Open Remote... to link to your hosted file or you'll need to web publish the data base (IWP) so that they can access it with a Web Browser.
In the meantime, you can do what you describe with FileMaker, but there are key details not found in your posts that I'd need to know in order to spell out all the details. How can you identify which records have been duplicated when you visually inspect your data?
In general, you can import the files in to a separate table that is related your main table by the field or fields that uniquely identify your records. A script can then check each imported record for a matching record in your main table and update it if one exists. The records that don't have a matching record can then be listed for your inspection so that you can correct any that failed to match due to any data-entry errors. Then you can run the process again, but this time records that don't match a record in the main table can be imported into the main table.
If it's only the transition period you are concerned with, and you have your hands firmly on the initial import, why not import into a record-free clone each time?
I wouldn't want to do this for an extended period of time, but if it's just for a short term until you get everyone sold on the database concept this should work well for you.
Thanks for the help.
In reply to Phil first. Phil I cant tell easily. Once the records have been added in Excel they dont usually change but they can which is why I was wondering what import rules are available. I can live with only updating records which dont exist on import and that might be the best at the start. The transition period may be a few weeks while we try to complete the move im not thinking about downtime to switch over as such.
Having considered everyones comments if on import I cant easily identify records that have changed we will wive with just new records on input until we switch away from the excel sheets. That can be done cant it?
It might be simpler to follow Ninja's idea.
If you are modifying data and need to put it back out to the other users, dump your entire database out to spreadsheet via export records and then don't touch your records in the database. At appropriate intervals, import all the data back in from the spread sheet into a clone of your database (One with no records) or just that particular table may be empty while you maintain related data not found in the spreadsheet in others. If you can disallow edits in one format (Spread sheet or FMP database) while making edits in the other--this will greatly simplify the process of synchronizing your data.