Export FileMaker Tables to JSON
Sep 14, 2015 08:22 PM
FileMaker to Xojo
We're still working to convert the Art Licensing Manager, our FileMaker Runtime based solution, to a Xojo Web App. It's a high priority for us since FileMaker Runtimes are listed to be deprecated, although we have several years as building Runtimes are available in FileMaker version 14 and will also be included in the 'next' version according to FileMaker Inc. We plan to have the Art Licensing Manager 3 available well before then.
Why do we need this?
For years, each new version of the Art Licensing Manager imported its data from the prior version via a slightly complex migration script. So, when version 3 becomes available we need the ability to import the data from the FileMaker Runtime to the Xojo Web App. We don't just want to dump in the data, but massage it as it goes in since the version 3 will have a different design, but the data is very similar.
Moving to SQLite
Since we're using Xojo to develop Art Licensing Manager v3 we can use an SQLite database. This will allow us in the future to just open the prior versions database file without having to go thru a conversion process! It will be a true 'separation model' that many folks strive for when designing FileMaker solutions. When the first update of the Art Licensing Manager 3 comes out it will just copy the existing database file and add any stuff it needs for the new update. The FileMaker Runtime migration scripts won't be needed, which will result way less migration issues since there won't be anything to migrate.
Why JSON
JSON is a very tight format that doesn't use much space to contain the data. For example, here's some data for contacts which looks wordy, but if you remove all of the returns and spaces quite small:
{ "Contacts": [
{
"NameFirst": "Hal",
"NameLast": "Gumbert"
},
{
"NameFirst": "Paul",
"NameLast": "Lefebvre"
}
] }
Get Out Ye Demon
So our goal was to export all the data from our FileMaker Runtime solution. FileMaker has an amazing function called 'ExecuteSQL'. With it you can find data using SELECT statements like:
SELECT NameFirst, NameLast from Contacts
It also allows you to create SELECT statements to get a list of the Tables in the FileMaker file as well as a list of the Field Names for a particular Table. So we created a script to get a list of the Tables. For each Table, we got a list of the Field Names. Once we knew how many records there were, use used ExecuteSQL to build the json data for each record and then saved the data to a text file.
Subsitutions
In our script, we have several Subsitutions to protect from illegal characters:
[ Char(10) ; "§C10§" ] ; // line feed
[ Char(13) ; "§C13§" ] ; // return
[ Char(34) ; "§C34§" ] ; // quote
[ Char(123) ; "§C123§" ] ; // {
[ Char(125) ; "§C125§" ] ; // }
[ Char(58) ; "§C58§" ] ; // colon
[ Char(44) ; "§C44§" ] // comma
This means that whoever opens your file on the other side will have to change these values back. My guess is that this isn't the best way to do this, but for our needs, we can substitute the characters back in our Xojo Web App.
One Plugin
While we wished we could just use one statement to spit out the data, we realized that it could end up being a huge amount of data as there could be millions of records and we'd likely end up with an out of memory issue. So we used the BaseElements BE_WriteToFile function to write each records data directly to the text file. You'll need to install the BaseElements plugin before running the script.
One Script
The only other thing you need is the script! You can test it with the unlocked example file. Using it in your file is easy. Just copy and paste the 'ExportToJson' script into your file and run it. When you run the script, it will export one file per table to your desktop named 'filename - tablename - json.text'. If you don't like that naming scheme, you can change it. As a bonus, if you add '<<$TableProgress>>' and '<<$$Progress>>' as text objects to your layout, you can see the export progress. You can run the script from any layout that you wish. Using ExecuteSQL allows the script to run from anywhere!
Download the example file.
For years, each new version of the Art Licensing Manager imported its data from the prior version via a slightly complex migration script. So, when version 3 becomes available we need the ability to import the data from the FileMaker Runtime to the Xojo Web App. We don't just want to dump in the data, but massage it as it goes in since the version 3 will have a different design, but the data is very similar.
Moving to SQLite
Since we're using Xojo to develop Art Licensing Manager v3 we can use an SQLite database. This will allow us in the future to just open the prior versions database file without having to go thru a conversion process! It will be a true 'separation model' that many folks strive for when designing FileMaker solutions. When the first update of the Art Licensing Manager 3 comes out it will just copy the existing database file and add any stuff it needs for the new update. The FileMaker Runtime migration scripts won't be needed, which will result way less migration issues since there won't be anything to migrate.
Why JSON
JSON is a very tight format that doesn't use much space to contain the data. For example, here's some data for contacts which looks wordy, but if you remove all of the returns and spaces quite small:
{ "Contacts": [
{
"NameFirst": "Hal",
"NameLast": "Gumbert"
},
{
"NameFirst": "Paul",
"NameLast": "Lefebvre"
}
] }
Get Out Ye Demon
So our goal was to export all the data from our FileMaker Runtime solution. FileMaker has an amazing function called 'ExecuteSQL'. With it you can find data using SELECT statements like:
SELECT NameFirst, NameLast from Contacts
It also allows you to create SELECT statements to get a list of the Tables in the FileMaker file as well as a list of the Field Names for a particular Table. So we created a script to get a list of the Tables. For each Table, we got a list of the Field Names. Once we knew how many records there were, use used ExecuteSQL to build the json data for each record and then saved the data to a text file.
Subsitutions
In our script, we have several Subsitutions to protect from illegal characters:
[ Char(10) ; "§C10§" ] ; // line feed
[ Char(13) ; "§C13§" ] ; // return
[ Char(34) ; "§C34§" ] ; // quote
[ Char(123) ; "§C123§" ] ; // {
[ Char(125) ; "§C125§" ] ; // }
[ Char(58) ; "§C58§" ] ; // colon
[ Char(44) ; "§C44§" ] // comma
This means that whoever opens your file on the other side will have to change these values back. My guess is that this isn't the best way to do this, but for our needs, we can substitute the characters back in our Xojo Web App.
One Plugin
While we wished we could just use one statement to spit out the data, we realized that it could end up being a huge amount of data as there could be millions of records and we'd likely end up with an out of memory issue. So we used the BaseElements BE_WriteToFile function to write each records data directly to the text file. You'll need to install the BaseElements plugin before running the script.
One Script
The only other thing you need is the script! You can test it with the unlocked example file. Using it in your file is easy. Just copy and paste the 'ExportToJson' script into your file and run it. When you run the script, it will export one file per table to your desktop named 'filename - tablename - json.text'. If you don't like that naming scheme, you can change it. As a bonus, if you add '<<$TableProgress>>' and '<<$$Progress>>' as text objects to your layout, you can see the export progress. You can run the script from any layout that you wish. Using ExecuteSQL allows the script to run from anywhere!
Download the example file.