Moving from Referenced Storage to External Storage

Discussion created by philmodjunk on Feb 10, 2018
Latest reply on Feb 11, 2018 by philmodjunk

This is a Blog type post of what I did, what worked and what didn't. It isn't a specific request for assistance as I've successfully managed to do what needed to be done, but comments and suggestions for how better to do this are welcome. Hopefully, others that encounter this task can glean some useful techniques from this post and any ensuing discussion:


The task:

I had a FileMaker 10 system that I had just upgraded to FileMaker 16. All client and server machines are windows boxes with either W7 or W10. The business is required by law to take pictures of customers and image scans of their Driver's License ID's to discourage the purchase of stolen property. In the solution, the image files thus generated were inserted into container fields with "store a reference" specified during the inserts and Import Records actions that were thus inserting the images into the needed container fields. This was woefully insecure storage of these image files as every machine where these pictures were added to the system needed shared directories with write access. A disgruntled employee could have wiped out multiple image files and one criminally minded could have grabbed multiple images of DR Licenses for purposes of identity theft. It was also the case that workstations periodically disconnected from the shared volumes. I was able to mitigate that by providing scripts that exported and executed batch files to remap the disconnected volumes but it was an annoyance that we'd all be glad to avoid.


So an early high priority task was to transition these images first to a new server (as we wanted to take down the original server), then change the storage option for these container fields to external secure storage.


So step 1 was easy. I simply copied the files to identically named folders on the new server. Shared them and then updated the code that generated the Batch file and executed it to map the volumes to the new server. That worked and there were no major problems. But this was just the warm up. The files were still stored as "references" not external. I also had a number of records where the referenced file had gone missing and I wanted to clean them up while I was working with this part of the system.


So my next step was to change over to external, secure storage. I found the container field in Manage | Database and changed its storage option to external, secure. I clicked Ok where needed to close dialogs and exit Manage | Database and sure enough, I got the expected dialog that FileMaker was transferring the files. After a brief sense of accomplishment washed through me, I took another look at the dialog reporting the results of this change and was disappointed to read that less than a half dozen files were actually transferred to secure external storage. It appears that "by reference" images cannot be automatically transferred to external storage. The few files that transferred were probably images embedded during some testing that I was doing to ensure that the FileMaker 16 version of this solution was correctly handling images the way the version 10 copy of the system did it.


After unsuccessfully trying to use a script that exported and re-inserted the image to move the files into external storage, I decided to shelve this part of the task for the moment and clean up the records with missing image files. That proved to be a bit of a challenge: How does one find all records where the only thing that shows in the container field is a message that the file cannot be found? The container field is not empty when the file reference is no longer valid and the error text displayed wasn't something that I could figure out how to access via script or calculation to put it in a text field where I could do a find for it. After trying several options, I hit on adding a calculation field with the following expression:


Length ( GetContainerAttribute ( ImageTable::ImageField ; "md5" ) )


I had to make this an unstored calculation field, but then I got a 0 in this field if the referenced container file was missing. Even then, for reasons that are not clear to me, I couldn't perform a find for all records where this field was not zero. I ended up using a looping script that looped through the records and omitted those where an 8 appeared in this field leaving just those with Missing image files in the found set. That allowed me the option of either deleting these records or using replace field contents to copy the embedded thumbnail from its container into this container field. I went ahead and copied over the thumb nails, the main reason was that I wanted to confirm that these thumbnail images, not being inserted as a reference, would correctly end up as externally stored images and this proved to be the case.


In any case, this left me with 1000's of files still stored by reference that I wanted as secure and external. After some thought and some trial and error, I tested the following script step to see what resulted:


Set Field [ ImageTable::ImageField ; Base64Decode ( Base64Encode (ImageTable::ImageField ) ; "fileName.jpg" ) ]


This worked. I could confirm this because I'd added a calculation field with this expression: GetAsText ( ImageTable::ImageField ) so that I could easily see if "Remote:" appeared as the first word returned by that use of GetAsText. I could have used this in a data viewer, but by adding the field and including it in a table view, I could inspect multiple records at a glance and see what storage option was used for that particular image file. I could also perform finds to omit records where "remote:" was the first word in this calculation field to omit records with correctly stored images from my found set.


So then came what I thought would be the perfect after hours one time only batch update to get these images where I needed them. I used the above expression that served as the calculated result in a replace field contents menu option to update all records that did not yet have remotely stored image files. (The actual calculation didn't use quoted text as the file name, it used getContainerAttribute to get the file's extension and appended it to text from the Image Table Record's primary key to create unique file names that also specified the record where they are stored. I figured that I might need to export the file from the container once in a while and that primary key text would help me keep track of what record stored the original.)


This produced less than perfect results. I had two tables to update like this. (Two tables that really should be merged into a single table, but that's a future upgrade to tackle...) On the first table, the client crashed. It may have crashed due to an issue that I've reported where externally stored images seem to bog FileMaker down with a spinner that takes multiple minutes per record before the screen properly refreshes, but I really can't be sure. In any case, the file stayed open on the server and when I reopened the file from the client, all appeared correct and in order, mission complete for the first table.


But then I repeated all of the above with the second table. My methods worked fine for resolving missing image file problems, but when I used Replace Field Contents to convert the referenced images into secure external storage, I didn't get a crash, but large numbers of records failed to show the expected picture. Instead I saw a file icon and a file name that was missing the file extension. When I checked the calculation field that used GetAsText to show the text from the container, the file extension was missing in that text as well. (checking this text for a field where the data correctly converted over did show the file extension.)


So I pulled a back up copy of the file and use an Update Matching import to restore the records and try again. After several unsuccessful runs around the bush, I decided to see if that missing file extension might provide a clue here. I set up a looping script with set field to make this change instead of Replace Field Contents. I then set a variable to the calculation that extracted the file extension and used the variable in the calculated result part of Set field to assign the desired file name. This allowed me to set up the following code:


Set Variable [$Extension ;

If [ IsEmpty ($Extension ) ]

   Show Custom Dialog ["No extension" ]
   Exit Script


   Set field [ ImageTable::ImageFIeld ; Base64....

End IF


I figured that it might halt just before the first case where this was failing to work and then I might be able to learn something about the image file that might provide a useful clue. But when I ran the script, it perfectly updated all of my records, completing my project to convert my referenced images to external, secure storage.


I really don't know why the looping script worked when Replace Field Contents failed. Perhaps some system resource was getting over extended and the looping script was just slow enough to avoid that issue. Given that this is a legacy system converted from FileMaker 10 (and previously put through numerous conversions starting from .FP3), maybe there was some latent issue in the file that complicated this process even though recover didn't report any issues with the file.


In any case, after several evenings of working with this part of the system while the business was closed for the day where I did the above, plus rewrite scripts so that new images were not inserted "by reference". I now have these images all stored externally and secure. Part of the testing process was also to leave the shared volumes mounted and check dates to confirm that no new files were appearing in the folder before disconnecting shared volumes and confirming that they no longer needed these shared directories in order to add and view images.



While working on this, I did research the knowledgebase to see what might be documented. I did find an article that warned that simply using set field to copy a file from one container to another would not convert a file reference to external storage. That came as no surprise since the data in a container field is quite different for referenced files as compared to externally stored.


With 20/20 hindsight, it probably would have been simpler to use "Save a Copy" with the self contained option to create a copy with embedded files, but the resulting file, already large with referenced images, might have been huge and then uploading to server before converting the file to external storage after embedding everything may not have been any faster than what I ended up doing.