5 Replies Latest reply on May 27, 2011 3:38 PM by philmodjunk

    Looking for multi device suggestion.



      Looking for multi device suggestion.


      Here is the situation.  I am designing a job / financial management database program for my Dads very small construction company. I have two questions (right now that is);

      1.) I have the core of the program built.  What is best way once he is using the core to add new items/features (payroll inventory etc.) to what he already has without disturbing what he has already done on the core (entered customers, jobs and contracts etc)?

      2.) He is running this construction program on his Mac, iPad and iPhone.  He uses them randomly depending on what he is doing at the time (iPhone to create job material invoices, iPad to sign contracts, Mac to manage financials). What is the best way to keep everything current or in sync on the go? All of these things can happen on the fly. From my research here are some of the problems that I have found.  We live in a spotty at best cell area.  When we do have cell service it is at best the Edge network. Lots of the work that he does has no service at all.  The iPad isnt the 3G model so any sync to or from the iPad that takes place has to take place in a wifi area.

           I do realize that I can sync through itunes, but as he may use all three products in different locations throughout the day this option really isnt feasible.

      Any thoughts on how I can achieve these tasks would be greatly apreciated.  I look forward to the discussion.


        • 1. Re: Looking for multi device suggestion.

          1) is the easiest to answer. 2) has opened a major can of worms for you and your dad.

          1) There are two methods you can use in combination to manage ongoing changes to your database system.

          Method 1 is to use the data separation model. You put all your data tables into one file and your interface (layouts, scripts, value lists, etc.) in the other file which uses external data source references to link to the data in the data file. With this method, changes to scripts, layouts, etc. can be deployed by swapping out the older copy of the interface copy with the new and your data all stays put in the data file.

          Method 2 is still needed even when you use the data separatioin model as you still need to add/change tables from time to time and this requires modifying your data file. Create an update script that uses Import Records to import all records from all tables of the older file into the new. A portion of this system is a script you put in the original file that does a show all records for every table so that all records can be imported by the import script into the new. This script also needs to reset next serial values for all auto-entered serial number fields to keep duplicate values from being entered the first time a new record is created in the updated file.

          2) The best solution isn't one that's available to you from your description. The best is to host the file from a server which at least the iOS devices would link to via WiFi or 3G. (You'd host from your computer or host from a third party source and make your computer a third client). That way all data mods are stored in one common file.

          Since you don't have that option, you will need to analyze the structure of your database and how your Dad uses each device so that they can all be successfully "synched" by a script you create. (iTunes, by the way, doesn't "synch" anything. It just copies the file to an from your iOS devices.)

          Here are some of the issues you'll need to figure out how to resolve:

          What if the same record is modified on all three devices since the last "synch"? Each may have been modified in a different way and you will need a method that merges the changes made by each and possibly throws up some discrepancies on a screen where a human can decide. You can use a "most recent change wins" strategy to simplify the synch, but then changes made on Tuesday on the iPhone might disappear if the same record was edited in a different way on the iPad on Wednesday of the same week.

          You may even find it useful to establish an Audit Tracking system on your database that logs each change made to each field and record of your database so that a synch script can use this info to effectively merge each change back into a synchronized whole.

          • 2. Re: Looking for multi device suggestion.

            Would it work to have 3 data tables on the "HomeBase" computer file?  A MasterData table, an IPad table and an IPhone table.  Whenever he returns to the office he can import the data tables from the other devices into their tables in the HomeBase file, then have a layout where you can see all three versions in side by side portals and select which fields from each are the most current/correct and update the master accordingly.  Then update the master data table in the IPad and IPhone before he leaves the office so that he has the most current data available.

            I am assuming that there are three separate files, one on each device.  This would take customizing each so that the computer would have 3 data tables and the other two devices had 2 data tables, the master for reference (non-editable) and their own for adding/modifying records.

            • 3. Re: Looking for multi device suggestion.

              In every table in your system that stores data that might be modified by two or more devices, define an auto-entered Modifiication TimeStamp field to record the date and time a record was last modified. Add a table to your system that records the timestamp each time data is synched. You'll only need to bring up a record for such special handling if two or more machines have modified that same record since the last synch. By importing into separate temporary tables like Mark has suggested, you can use this logic to reduce the number of records that need hands on attention to the absolute minimum.

              • 4. Re: Looking for multi device suggestion.

                Whoa!  OK Question1 I get, that makes sense.  Considering I have darn near the whole thing built already it might take some work to set that up but I understand the concept. An example of the import scripting that PhilModJunk is talking about would be nice as I am still working on my scripting skills and that sounds foriegn to me.

                Question 2 on the other hand is confusing.

                OK let me add some thoughts and see what you guys think. Lets say that "Home" is where the Mac is. Very little data entry or changing will be done on the Mac. At Home there is a Wifi network.  Again we assume that we start having all three devices at the same point.  Throughout that day changes are made using both the iPhone and iPad.  Most (and I say most not all) of the time changes made on one will be different then changes made on the other.

                The new example (thinking that the devices are used for different purposes) I enter a material invoice using the iPhone and enter timesheet info on the iPhone.  Then I use the iPad to write up a new contract for a new customer.  Thinking that he would want to "sync" every night, when he goes home that night whats the best way to "sync" the three devices?  Simpler the better.  He is trying to become more computer savy but for the time being I want to keep it simple.

                That being said when talking about syncing by scripts are there some examples of this that I can look at, I am by no means a FM "Pro".

                Let me know if this opens any new considerations.



                • 5. Re: Looking for multi device suggestion.

                  Here is a thread that describes how to split an existing file into the two needed for a data separation model: 

                  Convert to Seperation Model

                  Both for deploying new copies of the data file and for any kind of Synch system you devise, you'll need to become familiar with FileMaker scripting and how to use Import Records to move data from one table to another. You can start by looking up Import Records in FileMaker help to familiarize yourself with the different options available.

                  Trying to devise a synchronization system isn't the task I'd tackle first if unfamiliar with scripting. You may want to put that task on hold until you get more familiar with how to do this.

                  A script that imports all data from one copy of your file into another is a script that's basically a series of Import Records steps, each specifying a different pair of source and target tables.

                  Set Next Serial Value is the script step you can use to update your serial number fields and you can determine what the current greatest value in a given serial number field by using show all recrords to make sure you have all records from the table in your found set, then sort your records by the serial number field. If you sort in ascending order, the last record will have the greatese serial number and you need to set the next serial value to be at least that value + 1. If you sort by descending order, it will be the first record.