A PolyGrid Success Story

A Useful FileMaker Conversion Tool Built Economically with LiveCode

Find out how David Simpson created a great little tool using the PolyGrid in LiveCode to automate a large project for a client. The very modest amount of time invested in creating the tool potentially saved hundreds of hours of tedious work.

The Customer Requirement

This tool was built as a custom app for a large FM6 to FM19 conversion project having 153 files and thousands of scripts across the entire solution. With a solution this large, there are numerous changes required to upgrade it for full functionality with FileMaker Pro 19. Why so many files? Well, before FileMaker 7 the FileMaker product could only handle 1 table per file. Today, we would likely put all of those tables into a single file to make it easier to develop and manage the application and user accounts.

First, there are 20-year-old outdated plug-ins that are no longer available. This tool helps automate the task of finding and updating these old plug-in functions to use new built-in commands. Another very time-consuming manual task is updating scripts to include Commit Records steps because these weren’t required in FM6, but they are required with newer FileMaker versions. Newer versions of FileMaker Pro give developers more control over the process of committing records into the database to enable both transactional commit and rollback functionality.

This customer knows that they have thousands, maybe even tens of thousands of scripts spread out across these 153 database files. Their request is for me to build a tool to automate as many of these upgrade tasks as possible. Then they can use their offshore developers to perform the tasks. This post will mainly discuss the Commit Records Workbench feature because this is where I have used the new PolyGrid to great advantage.

Planning Process

As part of my planning process, I like to sit in my easy chair with my iPad and Apple Pencil to sketch out my ideas in the Good Notes app. First, with a problem statement, followed by implementation ideas, and then with the sketches of the GUI I plan to build. Not all ideas make it into the final app, but I find it critical to do as much planning as I can before starting development. I have included screenshots of parts of 2 of the 6 pages I wrote in my electronic notebook. The Good Notes app works cross-platform across my iPad and MacBook Pro (Windows support is a future roadmap feature), so I can switch back and forth adding screenshots and text notes for documentation. For this project, the 12 pages of notes became the manual delivered to the customer.

Of course, some features don’t make it into the final app, as shown in the notes about the GUI section. For instance, I didn’t include column sorting functionality because I didn’t know precisely how to implement this feature, and it wasn’t critical. There is an example of column sorting in the PolyGrid package, but I haven’t had a chance to examine the implementation process.

Reducing Risk – Minimal Changes

There are a couple of methods I considered when implementing this feature:

FileMaker scripts are represented by XML when they are copied via the clipboard. Normally, most applications cannot see these scripts because they utilize a private data type that is only recognized by FileMaker Pro. LiveCode can read and write private data types on the clipboard by using the rawClipboardData property. FmPro Migrator uses this feature to read all of the scripts from a FileMaker database and store them in a SQLite database it creates. When reading these scripts, FmPro Migrator stores the original XML script and it also parses the XML to create plaintext and HTMLText versions of the scripts.

If you want to explore the private data types placed onto the clipboard by various applications, you can use this stack.

1) Re-write the entire script XML. – I considered parsing the XML of each script and then using the revXMLInsertNode command to insert the new Commit Records script step. When performing a task like this, it’s important to not delete and re-create the script. FileMaker uses the hidden script ID to reference the script when it gets assigned to layout buttons or it is referenced by other scripts by the Perform Script step. It would however be Ok to replace the contents of a script by selecting all of the lines of the script and replacing them – because this would preserve the ID of the script.

Here is a screenshot of a FileMaker script displayed within the Xmplify XML editor app:

Looking at the raw XML on the right pane, it looks rather challenging to parse thru the XML. But, looking at the left pane you can see that it could be simply a matter of looking at each node represented by a script step, and in most cases, the attributes could be ignored. This could be done by assigning the parent to the first line having the Script ID by using revXMLParent function. Counting the number of nodes using revXMLNumberOfChildren, then advancing thru the rest of the XML tree using revXMLNextSibling until all of the child nodes have been found and analyzed.

I didn’t use this technique for a couple of reasons:

• There are many places within the existing scripts where there are errors due to missing plugins. If an entire script gets pasted, the developer may be required to deal with these errors immediately, instead of handling them in a batch process so to speak. So you might go into the task of just planning to solve Commit Records statements and end up getting sidetracked with many other issues.
• I wasn’t certain that the revXMLText() function would render the XML exactly as FileMaker expects onto the clipboard. I have found situations in which FileMaker renders XML in a slightly different format than I had expected – so I have added a few workarounds in my code. Theoretically, this shouldn’t be a problem, but FileMaker uses the Xerces XML parser, and LiveCode uses libXML so there might be some differences. I didn’t want to get part of the way thru thousands of scripts and find unexpected problems.
• It would take a little bit longer to work thru parsing the XML rather than just reading the plain text. To be conservative with the customer’s budget, I chose the simplest possible approach.

2) Let the user paste just the Commit Records statement in the correct location.

Since FmPro Migrator saves both XML, HTMLText, and plain text versions of FileMaker scripts into the SQLite MigrationProcess.db3 file, I can easily choose the file version I want to use – and even use multiple versions at once. In LiveCode, it only took 100 lines of code to perform this task on the plain text version of each script.

But how can I make the process easy for the user?

PolyGrid to the Rescue

Why not use the DataGrid? Sometimes, features of the DataGrid just don’t work as expected, and it can be challenging to troubleshoot even when using the amazingly helpful DataGrid Helper. And this is coming from someone (me) who has a fair amount of experience with the DataGrid. FmPro Migrator Platinum Edition converts FileMaker Pro portal objects directly into DataGrid objects in LiveCode stacks.

Another technique I could have used would have been to use a field object and try to get a checkmark to appear/disappear. I have never actually implemented something like this, but I think that in the past this may have been done via HTMLText and images. But with the introduction of the PolyGrid, there is no need to go through such shenanigans with the legacy field object.

Since I had recently licensed the PolyGrid/PolyList widgets as part of a bundle purchase, I decided this project would be a great way to try it out. And I wasn’t disappointed.

Here is a screenshot of the Commit Records Workbench window:

My design concept for this tool is to give the user a “Workbench” for making the changes to potentially hundreds of scripts or more within an individual database file. As the user makes their changes, they click the checkmark column of a PolyGrid row, which updates the status to “Completed” in addition to adding the checkmark. Clicking again, will “undo” the status value, in case they clicked it by mistake.

The process works like this:

• The user clicks the Browse button at the top of the window to select a folder containing the MigrationProcess.db3 file already created by FmPro Migrator.
• Then they click the Rewrite All button. The text of the scripts will be read from the SQLite database and a summary of the results will be written into a new file named CRResults.JSON within the same directory.

Why did I create a JSON file?

Using a separate JSON file gives me the flexibility of adding as many virtual columns as I might need, without changing the schema of the existing MigrationProcess.db3 SQLite file. It’s really easy to read the contents of the JSON file, turn it into an array and populate the PolyGrid.

on openCRResultsJSONFile
   -- check for and open CRResultsJSON file - populate the PolyGrid
   
   local tCRResultsJSONFilenameAndPath,tCRResultsJSON,tCRResultsArray
   local tError
   
   put getPathtoCRResultsJSONFile(field "mp3_directory_fld" of card "Workbench1" of stack "CommitRecordsWorkbench") into tCRResultsJSONFilenameAndPath
   
   switch
      case there is a file tCRResultsJSONFilenameAndPath
         -- read the file
         open file tCRResultsJSONFilenameAndPath for "UTF-8" read
         read from file tCRResultsJSONFilenameAndPath until EOF
         close file tCRResultsJSONFilenameAndPath
         put it into tCRResultsJSON
         put JSONToArray(tCRResultsJSON) into tCRResultsArray
         
         set the pgData of widget "CRResultsPolyGrid" of card "Workbench1" of stack "CommitRecordsWorkbench" to tCRResultsArray
         -- end of check for CRResultsJSON file
         -- update search results
         updateCRSearchResults tCRResultsArray
         put empty into field "script_text" of card "Workbench1" of stack "CommitRecordsWorkbench"
         break
      case there is not a file tCRResultsJSONFilenameAndPath
         -- clear the PolyGrid
         set the pgData of widget "CRResultsPolyGrid" of card "Workbench1" of stack "CommitRecordsWorkbench" to empty
         hideShowCRSearchResults "hide"
         put empty into field "script_text" of card "Workbench1" of stack "CommitRecordsWorkbench"
         break
   end switch
end openCRResultsJSONFile

The line of code which updates the PolyGrid in the above code is this line:

set the pgData of widget "CRResultsPolyGrid" of card "Workbench1" of stack "CommitRecordsWorkbench" to tCRResultsArray

Here are a couple of screenshots showing how the PolyGrid properties have been configured.

In the column properties, I have configured the check column with the svg-lcname ContentType. The values written into this column will be either check or check empty, representing the names of the SVG images I want to be displayed.

You can see these data values in the screenshot of the Contents tab of the PolyGrid property inspector.

How to get the SVG Names?

If you temporarily place a LiveCode Navigation Bar widget onto a card, you can select SVG icons for the buttons and see the names of the icons. If you click an icon after having selected it – its name will be displayed at the top of the selector panel as shown in this screenshot.

• The updated scripts are written into the SQLite database as HTMLText so that the Commit Records statements are displayed in a bright green color.
• As the user updates the status for each script, the CRResults.JSON file is updated with the changes.

I use the cellClick message within the PolyGrid to determine if the user has clicked on column #1. I then get the hilitedRow to use as an index into the array of values.

The next step is to update this row to change the check status. To work with the data in the PolyGrid, it is necessary to set the pgInternalPointer so that it knows which row and column number we want to update. I am using a switch/case block to toggle the check status. I prefer using switch/case blocks because it makes my code easier to read and easier to modify if I need to add additional CASE conditions in the future.

This is also where the pill-shaped status column is updated and colorized with either “#0,225,0#Completed” or “#255,0,0#Not Started” text. I like having these bright Red/Green colors showing next to the check status so that users can easily glance thru the list of scripts to determine the completion status by color.

on cellClick pColumnNumber,pRowNumber,pCellRect
   -- set/unset the checkmark column and status text if column #1 was clicked
   local tDataRowArray,tDebug,tCRResultsArray,tHilitedRow
   local tScriptName,tCheckStatus,tCRResultsJSONFilenameAndPath
   local tResult
   
   if pColumnNumber = 1 then
      put the pgData of widget "CRResultsPolyGrid" into tCRResultsArray
      put the hilitedRow of widget "CRResultsPolyGrid" of card "Workbench1" into tHilitedRow
      put tCRResultsArray[tHilitedRow]["scriptName"] into tScriptName
      set the pgInternalPointer of widget "CRResultsPolyGrid" to pColumnNumber,tHilitedRow
      put the pgTextOfCell of widget "CRResultsPolyGrid" into tCheckStatus
      switch tCheckStatus
         case "check empty"
            -- check the checkmark
            set the pgTextOfCell of widget "CRResultsPolyGrid" to "check"
            -- set the status text
            set the pgInternalPointer of widget "CRResultsPolyGrid" to pColumnNumber+1,tHilitedRow
            set the pgTextofCell of widget "CRResultsPolyGrid" to "#0,225,0#Completed"
            break
         case "check"
            -- uncheck the checkmark
            set the pgTextOfCell of widget "CRResultsPolyGrid" to "check empty"
            -- set the status text
            set the pgInternalPointer of widget "CRResultsPolyGrid" to pColumnNumber+1,tHilitedRow
            set the pgTextofCell of widget "CRResultsPolyGrid" to "#255,0,0#Not Started"
            break
      end switch
      -- update JSON file
      put getPathtoCRResultsJSONFile(field "mp3_directory_fld" of this card) into tCRResultsJSONFilenameAndPath
      put the pgData of widget "CRResultsPolyGrid" into tCRResultsArray
      put updateCRResultsJSONFile(tCRResultsArray,tCRResultsJSONFilenameAndPath) into tResult
      -- update search results
      updateCRSearchResults tCRResultsArray
   end if -- end of checkbox processing
end cellClick

• Each time the user clicks on a script in the PolyGrid, the original and updated versions of the script are read from the SQLite database and displayed in the script field on the right side of the window. Both copies of the script are held in memory so that the user can toggle between the original and updated versions of the script.
• The user adds the Commit Records statement to an existing FileMaker script by clicking the clipboard icon at the top right side of the window. Normally, they only need to do this once during a session unless they perform some other task in another app that replaces the clipboard contents. This button puts XML onto the clipboard using the XMSS private data type FileMaker recognizes as a single script step.

I am storing the raw XML for this command in a custom property in the LiveCode stack:

You can see the structure of the XML more easily in the Xmplify app window:

This XML code is passed to the copyToClipboardScriptXMSS function, which places it onto the clipboard using rawClipboardData. One consideration when working with the clipboard is that we need to lock the clipboard, put the info onto the clipboard and then unlock the clipboard.

Also, you will notice that with FileMaker, the private data type is named “Mac-XMSS” on Windows and “com.apple.ostype:XMSS” on macOS. When FileMaker Pro sees these private data types on the clipboard it immediately recognizes that this is a FileMaker script step it can read and paste directly into a script. And if you make a mistake when creating this XML code, it can crash the application. FileMaker doesn’t do much error checking on it, since it looks like the data originally came directly from itself.

function copyToClipboardScriptXMSS pXMLData
   -- copy single script step to clipboard - on Mac & Windows
   -- pXMLData - text of scripts
   
   local varTempScriptsFilePath,varScriptLength,gXMLCopyMode,varClipBoardUtilityFileName
   
   if the platform is "Win32" then
      -- put data onto clipboard - Windows
      -- Add 4 chars of binary length data to prefix the XML script - only on Windows
      put length(pXMLData) into varScriptLength
      put binaryencode("i",varScriptLength) before pXMLData
      -- write data as Mac-XMSS info onto Clipboard
      lock the clipboard
      set the rawClipboardData to empty
      set the rawClipboardData["Mac-XMSS"] to pXMLData
      unlock the clipboard
   else
      -- write data as XMSS info onto Clipboard
      lock the clipboard
      set the rawClipboardData to empty
      set the rawClipboardData["com.apple.ostype:XMSS"] to pXMLData
      unlock the clipboard
   end if -- end of Mac vs Windows - write clipboard data
end copyToClipboardScriptXMSS

Conclusion:

From a blank sheet of electronic paper in GoodNotes on the iPad to a completed application took only about 30 hrs. This is pretty impressive for a tool that will save a huge amount of manual labor upgrading thousands of FileMaker scripts. Could it be done in less time? Certainly. I checked my time log database and found that I spent about 7 hours just doing the planning for this app. Now that I have figured out the process, it’s always easier to develop it a 2nd time!

It would probably have taken at least a few more hours if I had implemented this functionality using the DataGrid. And how would I have implemented the checkbox feature with the DataGrid? I am not sure. It is just so easy to include this feature with the PolyGrid. The PolyGrid has earned a spot in my LiveCode development toolbox.

And as a testament to the high performance of LiveCode in general, this app runs fast too – converting hundreds of scripts takes less than 1 second. I hope this blog posting has inspired you to develop your own specialized time-saving tools with LiveCode.

David Simpson
www.FmProMigrator.com