123 Sync is a product used to integrate FM (Windows) with QuickBook (Windows). 123sync is compatible with Windows QB versions only. Mac users can run QB on Parallels, VMware or Boot Camp (This is an additional expense). Here at Productive Computing, we talk a lot about FileMaker to QuickBooks integrations. We even did a webinar about it. This is because: a) FileMaker is a powerful and versatile database platform with (almost) unlimited possibilities to help you organize and analyze your data, and b) QuickBooks is the most popular accounting software for small and medium businesses in the United States.
A common request that we receive from users is to have the ability to use an iOS FileMaker solution offline and sync it back with the server later.
There could be a number of reasons for this request, but the most frequent one we hear is because of an unreliable or non-existent Internet connection. This offline method, while great for a lot of reasons, can present some challenging maintenance issues.
Today’s blog will look at a method for pushing an updated version of an iOS solution to an end user without losing any of their existing data and without requiring much effort on their end.
Once a solution has been deployed and then additional improvements have been made to it, we face the challenge of how to install an updated version onto their mobile device without wiping out any data.
Virtual lists
123 Sync Filemaker Download
Before performing this process, you should have a bit of FileMaker scripting and calculation knowledge as well as know how to use virtual lists. If you’re not familiar with virtual lists already, check out Seed Code’s virtual list example and read Mighty Data’s tutorial on how to create a virtual list in three easy steps.
Technique
This technique involves three components
- A virtual list to show the user what’s currently in their documents folder
- A script to actually set up the virtual list
- A button on the virtual list record to call a script that runs the import
Demonstration
Take a look at this Assets file which provides an example of this process in action. This is a modified version of the Assets file that is included with FileMaker Go. There are no records in the tables that we care about so it’s a good test! Just download it, open in FileMaker Go, select Assets.fmp12 from the list and you’ll have the data from that version in this version.
Please keep in mind that this demo doesn’t include some of the important verification and error checking tasks that you’ll want to handle.
While this isn’t anything groundbreaking, we hope it saves you a headache or two when it comes time to push out your first update to end users of your offline mobile solution in the field!
Recently we have been working on a system that requires a large amount of data to be synchronized to MySQL (for driving a web interface) from FileMaker. After a few initial tests of methods for achieving this we found that the speed of the transfer was un-acceptable due to the large amount of data being transferred. The transfer was set to take place at several points throughout the day and would often still be running when it needed to run again – not good.
So lets set the scene. The FileMaker system contains information being added / edited by users and must be able to send all the changes and new records up to MySQL as quickly as possible. Records that don’t exist yet in the web table will be lacking a webKey field (so we must add the record) and those that do will have this (so we must update the record).
We tried three iterations as we went on developing this feature:
- Method 1: Use ESS to replace the data in the MySQL table. This went badly simply because the matching import ran extremely slowly to the point where even with a limited number of records – the timescales were too large.
- Method 2: Use ESS to place new and edited records into a holding table within MySQL and then use MySQL commands to update existing records and add ones that didn’t exist before. This worked a little better – but again the timescales were still too long.
So – what’s the solution? Make SQL do as much of the heavy lifting as possible, for things like data import its orders of magnitude quicker than using FileMaker to fill a MySQL database (several seconds rather than several minutes). So we created:
- Method 3: Use FileMaker to export a CSV file of the new / updated records, then import them into MySQL holding table using the command line tool – then use MySQL commands to update existing records and add ones that didn’t exist before in the main table.
By using method (3) we cut our average time down for this script to run from 6+ hours to about 20-30 minutes with method (2) and then finally to a few minutes with method (1). Wow.
So lets see how we achieved this and see the process in action.
1) Set up filemaker to export all your edited / new records from FileMaker into a CSV file – I wont go through this in detail – you FileMaker developers know what you are doing at this point. If you can its best to have the fields exporting in the same order as the fields are listed in MySQL. You don’t have to do this – but trust me it makes your life a lot easier when looking at the code.
* NOTE: If your in a country that uses the date for DD/MM/YYY – ie not USA – then you might want to double check your dates when you export / import through FM Server.
2) Setup a batch command that will run our MySQL statements within the MySQL environment. This is simply a trigger to run the SQL and provides the bridge between the FileMaker server schedule and the MySQL environment. Here is how this should look:
Content of sync_data.bat
Lets take a look at those lines in detail. The first two lines:
Simply move us from the FileMaker server directory context to a directory where we can run our script (of course you could also do something with system path on a Windows box to allow you to run MySQL from anywhere on the server).
The final line is where the interesting stuff happens:
Here we login to MySQL by supplying the user, password and host then by using the “<“ operator we run the scripts contained within the .sql file against the specified database.
The force switch means that we will continue running MySQL commands even if one of our previous commands has failed. When dealing with this amount of records it was important to get as many records in as possible – therefore if one row has a problem we carry on and import the rest.
123 Sync Filemaker Software
* Note: You can find more commands / options and details on this functionality on the main MySQL site here: http://dev.MySQL.com/doc/refman/5.1/en/MySQL-command-options.html
3) So now we have a batch command we can trigger to run from FileMaker Server, lets take a look at the MySQL scripts that this batch command will run:
The first thing we need to do is clear our temporary table so that we only have new records to deal with:
This will empty our holding table and we are ready to being our import.
Essentially this takes our generated CSV and imports it into MySQL. It’s at this point where the major speed increase is gained as MySQL imports these records very, very quickly.
I’ll break down this statement to explain what each part does (although I am sure most of you can take a fairly accurate guess already).
LOAD DATA INFILE: is the MySQL command we are running.
LOCAL means that the file is located on the client machine rather than the server running MySQL server – we then specify the full path to the file we are importing from. (In the example given the FileMaker and MySQL servers where on different machines – if they are on the same machine – you can get away without this – as well as not having to explicitly name hosts).
Fields Terminated / Enclosed By and Lines Terminated By essentially determine how our data is broken up in our import file. In this case we have a comma-separated file (hence FIELDS TERMINATED BY ‘,’) with the fields wrapped in quotes (hence ENCLOSED BY ‘”‘) and with a line break between each record (LINES TERMINATED BY ‘rn’).
(FIELDNAME1,FIELDNAME2,FIELDNAME3,etc…..) – The final part is the list of MySQL fields that relate to the order of the fields in the exported CSV in order – the fields must be named correctly and in the same order that FileMaker export exports them.
123 Sync Filemaker Free
* Note: More information on LOAD DATA INFILE syntax can be found on the MySQL site, here: http://dev.MySQL.com/doc/refman/5.1/en/load-data.html
Finally we transfer our records across into our main table, and this is accomplished in two parts:
This takes the primary SQL key (Webkey) from our main table and matches it to the records in our holding table (updating the record so that when we transfer to our main table the record will update) – if there is no match then the Webkey remains blank in our holding table (signifying the record will be added to the main table).
This selects all the information from the holding table and then uses REPLACE to get it into the main table. REPLACE will update the main record if we already have a primary key in our holding table record and add the record if not (thus filling our initial criteria).
* Note: More information on REPLACE INTO can be found here on the main MySQL site: http://dev.MySQL.com/doc/refman/4.1/en/replace.html
123 Sync Filemaker Settings
So in summary that is how we process large quantities of data into a MySQL database from FileMaker in just a few minutes compared with hours. I’ve tried to simplify the example here as much as possible to make it easier to follow, our real world solutions get ever more complex. I’d love to receive any feedback or comments people have on this or perhaps on other ways people are solving these type of problems.