21 Jan 2021: demo has been updated to fix a couple bugs; article has been updated to reflect these changes.
Demo file: file ids, table ids and field schema, v4.zip (requires FM 18 or later)
INTRODUCTION
Recently I was asked to help document a FileMaker solution by creating and populating a “schema” table as per the above screen shot.
One of the requirements was to programmatically determine internal ID numbers for the file and for the tables (as opposed to table occurrences) within that file, and this was accomplished by leveraging some script steps that were introduced in FileMaker 18.
(Thank you Rob Russell for pointing me in the right direction, i.e., to Save A Copy As XML.)
RUNNING THE DEMO
Clicking this button…
…invokes this script…
…which, in a nutshell, does the following:
- Saves as XML to the Documents folder
- Uses one of two methods to read the XML file into $xml.content
- If FM 18 on Windows, uses Insert From URL
- Otherwise uses Read From Data File to load the first 10,000 bytes
- Deletes the XML file from the Documents folder
- Parses file and table data from $xml.content into the schema table
- Uses SQL to populate field entries, as per this informative article by Andrew Duncan: Using ExecuteSQL to Query the Virtual Schema/System Tables.
CLOSING THOUGHTS
Beyond the narrow use case of today’s article, perhaps the real take away is how easy it now is to pull data from an external file into a variable using native FileMaker tools only, as opposed to relying on plug-ins or any other outside technology.
Neat technique. I tried putting the schema table and scripts into one of my files to see what would happen. It partially worked – the schema table never gets more than 95 records created in it – the data that is created is fine, it just stops early. I tried adjusting the Read From Data File step and that didn’t help. Thoughts?
Hi David,
The most likely culprit is that you’ve got a lot of tables and not enough of the XML file is being read.
What happens if you clear out the Read From Data File “Amount” setting?
Kevin
23 tables. I tried removing the Amount entry and it went further. However, it seemed to get stuck in the outer loop. The $counter field had a value but $entries had a value of “List”. Going to keep trying.
Which table name is it getting stuck on?
The loop never exits. I had to jump into the debugger. When I stepped through it the $entries variable immediately gets set to “List” rather than a number. The XML file is about 22 megs. Wonder if FM isn’t loading it all and is extracting the the wrong thing – grabbing “List” rather than the number it should be. It does make it through the tables – I can see them in the “name” field. However it isn’t ending the extraction properly – I get the closing >” and then what looks like ALL of the XML after it. After the last table entry its all of the XML inserted into the name field for each record after.
Bug fixes have been applied. Please advise if you encounter any further problems.
Will do. Thanks.