1 2 Previous Next 18 Replies Latest reply on Mar 24, 2015 1:48 AM by PeterWindle

    Updating a separated solution


      I have recently split a reasonably mature application into data and interface files - it was developed for the charity that I work for, and it's now being used by another organisation (on a non-profit basis), and my motivation for splitting it was that updates would be easier. So far (and we're only a few weeks in), I've only had to change the interface file, and it has, indeed, been very easy just to swap in a new version.


      However, I've recently had to change the data file - a couple of new tables, and a few new fields in existing tables. And I've obviously not grasped how that should be done. Here's what I did:


      1. On the development system, made the required changes to the data and interface files.

      2. Made the same (data) changes to the data files on the server, i.e. added the new tables, fields, etc. (a few days ago, in anticipation of the upgrade).

      3. Made a few more changes to the interface file in the development system here (during the past few days).

      4. Put the new interface file on the server (i.e. yesterday).


      The new interface file, once on the server, for some reason, didn't "see" the data file correctly - a lot of the relationships were messed up, field names missing, etc. It's OK now (I think), because I've amended the interface file in situ, but at first the new features didn't work, because relationships were broken, incorrect fields were being used in script steps (e.g. the "time modified" field was used instead of the key field), etc.


      As I said, this is clearly my mistake, but I really need to know where I've gone wrong, as it throws into question the whole protocol for applying upgrades. This time it wasn't a major deal, as there were only a few changes, but it could have been a lot worse.


      If you can point me to a description of "best practice" for making changes, so that I can understand where I've gone wrong and (obviously) get it right in future, I would be extremely gratetful.


      Thanks and regards,



        • 1. Re: Updating a separated solution

          Hi Dave


          I do almost all my development using separation, and what I have found is that when you are adding a new table, or even new fields, what you have described happens when the fields and tables are not added in the same order on the server as on the test file.  FileMaker assigns an internal ID and that is how things are linked up.  So if I am adding more than a couple of fields to the data file, I do it this way and then problems are rare:


          If adding a new table, I initially add that table to the Interface file.  Make all the layouts, relationships, and scripts using that file.  Test and install so the client can also use the new tables and make sure we have the schematic the way we want it.  Then I import the new table into the data file, and repoint the relationship graph to the new table.


          If adding fields, I ALWAYS add those fields to the data file first, then download a backup, clone or otherwise, to make the changes to the interface file AFTER I have changed the data file. 


          Hope that helps!



          1 of 1 people found this helpful
          • 2. Re: Updating a separated solution

            Dave -


            To answer your basic question - why did it break? - Karen is right. FileMaker tracks every object (table, field, button, script, script step - everything) by an internal ID. If you add it in one file and then attempt to add it in another file, it might not keep track of it as you expect, because the new object in file A probably won't have the same internal ID as the new object in file B. (Using the Import functionality will avoid glitches because FileMaker will resolve the internal IDs during that process, as Karen suggests.)


            That said, my usual practice is to do all my development offline, then simply replace the file(s) that need replacing in production while importing the data via script. I do this for a couple of reasons:


            1) I have a lousy memory, and trying to remember all the changes is usually a losing proposition.


            2) I have had several bad experiences with modifying files in production (including badly corrupted schema). Hence, unless absolutely necessary, I avoid modifying files while they're being used in production. Other folks will disagree, but I'm paranoid.


            3) It allows me to test thoroughly to make sure I don't have any regression faults before I put it into production. Doesn't mean I always catch everything, but it does help. I can also show it to the customer ahead of time so they can tell me the inevitable, "Oh, that's not what I meant!" before it's out there.


            Anyway, that's me. There are plenty of other folks smarter than I who do it differently. YMMV.



            1 of 1 people found this helpful
            • 3. Re: Updating a separated solution



              Thanks a lot for this helpful response.  That's a bit of a drawback, isn't it?  Still, I suppose once aware of the pitfall, one can avoid it.


              One thing I'm not sure about from your "If adding fields" paragraph - if there are multiple data files (one for each client), presumably I have to take great care to make the changes in the same order in each of the data files, before cloning one of them.  And that's the one that I use for implementation of changes in the interface file.  Or have I misunderstood?


              Thanks again for your help,



              • 4. Re: Updating a separated solution

                It is a drawback, and why separation works best when the files are pretty mature.  I find that most of my requests for changes in those kinds of files are for reports or minor adjustments - in that case, its darn convenient, and definitely worth the trade off for me (As Mike said, YMMV)


                ALL of my clients are custom databases - and there are a lot of them.  I rarely go onsite - I access all the databases remotely.  Most of them cannot do without their databases during normal working hours, which is when I must do my work, so I do most of my work offline so that I can carefully test before making changes to their "live" systems.  Makes us both happy.


                I too have a lousy memory, so I often have both the client database and the offline database open at the same time (I set the Window title to OFFLINE_ and the file name for the local copy in the opening script, so I know which one is which!).  If I am only adding a few fields, regardless of the number of tables or files, I can add them to the client database, then copy and paste them into mine.  I have never had a problem with modifying databases in production - but as Mike said, it is a risk, so I ALWAYS run a backup, then modify, then run another backup, just in case.  And I usually make the schema changes during off hours. 


                If its a big change - I do it Mikes way - offline with an import script that checks the key fields (serials) and updates it automatically in every table.


                Again - I have probably 3 dozen active databases, and many more that I occasionally do updates on.  Changing the schema in any way ( new fields, new relationships ) happens less than once a year for most clients.  Half a dozen or so are in active development, but about half of those are not usually in production yet.  So there are only a few that require this kind of extra care.  You will have to determine what works best for you and your circumstances!


                warm regards,



                Oops, just realized I did not clearly answer your question.  If you have ONE interface file and MULTIPLE data files, ANY data files that are impacted by field changes will have to be used for development.  So if you change fields in 3 data files, you need all three of those to work with your interface.  If you only change one file, that is all you need to swap out.


                My systems usually have ONE interface and ONE or TWO data files.  Usually one data file is all I need containing all the tables is all I need.  Sounds like maybe your system was originally built in an earlier version of FileMaker.  How many data files you have is a choice - with the way that I work, less is more.


                Message was edited by: karendweaver

                • 5. Re: Updating a separated solution

                  One way to approach the problem of updating a separated solution is to import all of your data from the old data file to then new data file each time you do a data file upgrade.


                  In the past, this would of required scripting an import routine, which depending on the complexity of your solution, could be quite time consuming.  But Goya, http://www.goya.com.au/refreshfm, has this really cool product that automates building an upgrader tool. I would highly recommend using this product.  I have used it for 3-4 clients and it works fabulously and saves hours of time. 


                  Michael Sloper

                  Pre1 Software

                  • 6. Re: Updating a separated solution

                    I have to agree with iamsloper on this one. I hav eto do this regularly, so I have created some scripts that I can kick off via the server to do my imports when I need to do a migration. It was a hassle writing them but I broke them up into groups so that I have main table imports, join table imports, log table imports et al, done separately so as to manage the amount of data getting pushed around.


                    I've tried Goya's refreshfm demo and it seems to work pretty well. So you might want to go that route if the systems are sizable enough that writing out your import scripts might be too time consuming.

                    • 7. Re: Updating a separated solution



                      Re. the multiple data files, sorry, but I didn't make myself clear.  There's only one data file in the solution (plus the interface file), but there are several iterations of that data file, i.e. one for each client.   So, what I've learned is that, when updates are needed (only) in the interface, it will be easy to make those changes in the DEV interface file, and copy it to each of the client folders.   But that when changes to the data file are needed, I should go for the offline import approach, which seems much better, if longer.


                      Thanks again for your help - I really do appreciate it.



                      • 8. Re: Updating a separated solution

                        Thanks Mike, that's really useful advice. 



                        • 9. Re: Updating a separated solution

                          Thanks for the tip about refreshfm.  I had heard of this, and it does look really good, but I simply don't have the budget for it at this stage.




                          • 10. Re: Updating a separated solution

                            I kind of sidetracking here - sorry about that - but I am curious how you handle seperate datafiles for each client.

                            I am planning to do it in the External Data Source, where I have to make a interface file with the client ID in the name of the datasource for each client.

                            That way I have a developer Interface file, and when I distribute the updates I copy the file with different datasource relations.

                            Do you have a better way to handle this problem? 


                            Thanks for any input on the subject.



                            • 11. Re: Updating a separated solution

                              Hi Dave


                              Oh, yeah.  I would not attempt to do anything but offline import in that situation!



                              • 12. Re: Updating a separated solution

                                Hello Niels,


                                That's what I do, i.e. for each client there is a set of 2 files, Focus_INTERFACE_[xxx].fp7 and Focus_DATA_[xxx].fp7, where "[xxx]" is the client identifier.  When there's a new version of the interface file, I copy it for each client and change the file name in Manage External Data Sources to add the client identifier, leaving the data source name itself as "Focus_DATA".


                                Looking ahead, when I have a lot of clients, this will be a pain, and I guess there's a simple way to automate it, but it works for now (only 2 clients!).



                                • 13. Re: Updating a separated solution

                                  Perhaps part of the problem is having different names for the files for various clients. For instance, I can make a menu file that opens File A and if I put them both in the same folder, I can move that folder to another location on the same computer or move it to another computer or Go and the menu file will open File A.


                                  But if I change the name of File A to File B, the menu file will not open it but say it can't find File A.


                                  Some of your scripts may point to File A and if you import them into another GUI that uses File B, they won't work.


                                  If you use the same name for the files for all accounts, you might avoid a lot of problems, maybe not. But changing the file names now might introduce more problems.


                                  I wonder how many of your problems would disappear if the file names were similar.

                                  • 14. Re: Updating a separated solution

                                    One simple solution is to use your master GUI and DATA files only. Don't try to update these other files.


                                    Use an import routine to copy the data from these other files into your modified tables when you modify them by adding or deleting field. I would rename the old files to File OLD as part of the import.


                                    Thus you only need to write or add to one import script. Simpler for you.


                                    Rather old fashioned but the simplest method. If you weren't modifying the tables, then the two files is a great tool.


                                    Since you would be doing the work at night, running the full import at night is far easier on you.


                                    Deleting a field is a pain since that might produce a bad import.


                                    I think you will find doing an import, even if it is contrary to the concept, to be easier on you and perhaps more reliable since none of your layouts and scripts will be effected/affected.

                                    1 2 Previous Next