4 Replies Latest reply on Jun 6, 2012 12:07 AM by PeterThorton

    Updating file on a FileMaker server

    PeterThorton

      Title

      Updating file on a FileMaker server

      Your post

      Hello,

      I have a question regarding deployment and maintenance of a database file on a FileMaker server. I'll try to describe my situation:

      I'm fairly new to FileMaker, (and indeed to the company I work for at the moment). A couple of weeks ago I started developing a solution (single file) in FileMaker Pro Advanced 11, basically learning by doing. I've been working on a file stored locally on my computer. The solution is nowhere near finished, but some integral parts of the solution are working, and the users need to start using them.

      This obviously calls for two versions of the file - a live version and a development version. (I've been saying this from the beginning, but they've already managed to input a lot of live data into my development file before I managed to persuade them).

      It turns out the company has a FileMaker server, hosted on a machine within the company's LAN. My boss took my file today and deployed it on the server. The file is accessible throughout the network, if Ichoose File -> Open Remote in FileMaker, and type in the address like this:

      fmnet/192.168..../MyDatabaseFile/pf7

      FileMaker opens the file. So that's working fine. But there's still a problem of updating the file on the server.

      Let's say I update the solution about once a week. That means the users have been using the file for a week, and the data on my developer version is out of date.

      Since FileMaker bundles data layer, application logic and user interface into one unseparable package, the only way of updating the file I can think of is replacing the old file with the new one. This would mean replacing live data with my week-old copy, so obviously I first need to update my development version with the data from the live version.

      Is there an easy way of doing this? I can access the file that's on the server, so I can obviously choose File -> Import, and import the new data. But as far as I know, I can only do this table by table, right? So far I have some 35 tables, so I'd have to do it 35 times, every time I deploy a new version. And the number of tables is likely to grow in the near future.

      Is there an easier way of doing this? Like, for example, updating all the data in all the tables in one go? Or some way of updating the user interface portion of the file, without touching the data?

      At this point it seems almost easier for me to actually work with the live file that's on the server. Some of you may suggest it, but this is out of question. For me to be changing layouts and scripts while the users are actually using them, is a recipe for disaster. I also need to work with a lot of testing data - creating fake orders, moving non-existent stock from warehouse to warehouse, and so on. If the testing data gets mixed with the live data, and nobody knows which one is which, we may as well shut down the servers and go back to pen and paper. I also need some tolerance for errors. If I test a script and it accidentally rewrites all the orders with useless data, it's no biggie if it's on a development file. But if it's on live database, I'm dead.

       

        • 1. Re: Updating file on a FileMaker server
          philmodjunk

          Since FileMaker bundles data layer, application logic and user interface into one unseparable package

          It's not inseparable. FileMaker solutions can be deployed with separate interface and data files--which can simplify the update process when deploying an updated copy of your file. See this link: Convert to Seperation Model

          To update your data with a single file solution or when you need to update the data file, you can write a script that imports data from each table in turn and that also updates any auto-entered serial number fields' next serial value settings. This means you can close the file on the server (You need to get access to the server admin application so that you can manage your own project.) Open a copy from the server in FileMaker and click a button to import all the data into a clone (copy that's empty of all records) of your new file. Then you can upload your new version to the server. You shut accesss to the current copy when updating so that your users can't keep modifying data on you while you are importing data into your new copy.

          • 2. Re: Updating file on a FileMaker server
            PeterThorton

            Thanks, that's just what I needed.

            Just a follow-up question:

            If I choose "Update matching records in found set", match them on ID (an auto-increment serial number) and check "Add remaining data as new records", it will take care of both adding new records and updating existing records, right?

            But what about the serial numbers? In my experience, FileMaker can be a bit irresponsible when it comes to IDs, I've had situations where records were added, but the seed for the autoincrement serial number was not increased. So the next time I tried to add a record, I'd get an error message telling me that there is already a record with that ID value (and, surprisingly, asked me if I want to insert duplicate value anyway)

            I'll give you an example:

            Let's say I have 10 records in my database, they have IDs 1 to 10. The "next value" setting of the autoincerement field will be 11. Let's say the users create 5 more records - with IDs 11, 12, 13, 14, 15.

            I import this table into my own, matching the IDs, updating any changes to existing records, and adding the 5 new records. My table now has records with ID values from 1 to 15. But will the "next value" increase to as well, or will it stay the same?  When I try to add another record, will it add the ID of 16, or will it try to add 11, and cause an error?

            Another scenario:

            the users have added 5 records, so the IDs go up to 15, but I've added 2 to my file as part of testing. I've deleted them since, but my seed for IDs has increased to 13. When I import the new records, will they have IDs 11 to 15, or will it be 13 to 17? That would be a problem, as the values in foreign key tables would no longer match.

            • 3. Re: Updating file on a FileMaker server
              philmodjunk

              Your import script can update serial number settings as well as import records. Check out the Set Next Serial Value script step. After importing, you can sort your records so that the largest ID number is first or last, then go to that record, then use at least that record's ID + 1 to update the next serial value setting.

              If you are using FileMaker 12, there's a whole new way to set up primary keys. If you use an auto-entered Get (UUID), you won't have any next serial value to update.

              • 4. Re: Updating file on a FileMaker server
                PeterThorton

                Thanks. I've already decided that I'll have the script delete all the records from the tables and then just add them anew, to remove any junk (no offense intended) that I might have left there during testing. But the Set Next Serial Value script will come in handy just the same. I'm using FileMaker 11.