Recently, I converted two customer databases from FileMaker 11 to FileMaker 12. Both customers were embedding documents in the database by inserting them into container fields. The result in each case was database bloat—a file size much larger than necessary (1GB or greater).
The disadvantages of a large database in FileMaker are several including slower performance (more data to push over network), lower productivity (long wait times during backups), and greater risk (more susceptible to corruption in the event of a crash). Fortunately, FileMaker 12 has a new feature called Enhanced (or external) Container Fields to address this.
For each database, I converted it by dragging the file onto the icon for FileMaker 12 and going through the automatic conversion process. Next, I opened the database and sorted the field list by “field type”.
Next, I went through each table looking for the any container fields. (You could also use BaseElements or Inspector to search by field type.) For each container field, I changed the storage option to “Store container data externally” under Field Options.
Finally, I closed the Manage Database window to save the changes. FileMaker Pro started moving the documents in the container fields to a folder called “Files” outside the database. It then compressed the database to reclaim the unused space.
The results were impressive. For the first database, there was one container field in one table with 68K records. Each record stored one thumbnail image. By moving the container data to external storage, the database size was reduced 46.5% (750MB to 400MB).
For the second database, there were twelve container fields in one table with 750 records. Each record stored multiple images and PDFs that were not optimized. Moving the container data to external storage reduced the file size by 99% (2.3GB to 3.4MB)
The conclusion is obvious. Stop embedding documents in your database! It increases the file size unnecessarily and creates the negative effects mentioned above. Of course, you have to upgrade to FileMaker 12 to take advantage of this new feature. The only caveat is to test the functionality of the new database before deploying it to production (just in case).
Oh my gosh!!!! Thanks, Kirk, for this posting. I had no idea that scanned documents were inflating the file size. One of my clients has 3000 records. Many of them have scanned documents and CAN have as many as 10 documents each! No wonder it takes FOREVER to make a backup copy. I just couldn’t figure out why it was so huge! I have recommended she move to FM12 as soon as possible! Thanks again.
Hi Jane, thanks for dropping by. This is one of the best benefits of FileMaker 12.
If you are using FM11 and plan to soon upgrade to FM12 then keep embedding your files into container fields. I say this because when you convert your database to FM12 ‘referenced’ files in container fields will NOT be transferred automatically to external storage. If I am wrong on this please correct me, but this is my experience.
Brian, FileMaker 12 supports container fields “by reference”. Of course, the path to the file must still be exactly the same. After converting the solution to FileMaker 12, you can write a script to move the files from the container field “by reference” to another container field using “remote storage”.
Hi Kirk,
Is there a script to convert referenced data to embedded data? I would appreciate some help with this if you can give some.
thanks in advance
Brian, use Set Field with the new container (remote storage) as the Target and the original container (by reference) as the Result.
Hi Kirk, I’ve tried to move the pics I’ve got from a container ‘by reference’ to an ‘external storage’ one using the script you suggested. However, even if the pic is actually copied in the new container, it is not saved in the directory setup. It looks like the script is copying the reference and not the actual pic. Any suggestions? Many thanks.
But here’s a question: My client just asked me, “I thought when we checked ‘store only as a reference’, that FM did NOT put in the actual data, but only a link. What’s the difference in ‘store only as a reference’ and the new ‘store container data externally’?
Great question Jane. “Store only as a reference” stores the document on the hard drive or network volume with a path specific to the user. The reference will break if another user has a different path. For example, if User 1 has the network volume as the D drive and User 2 has it as the F drive.
“Store container data externally” is actually managed by FileMaker Server (not the OS). The path to the document will be the same for each user because it is accessed through FMS. In addition, FMS can backup the documents and the database, providing a better level of protection.
But my client is not using FMServer. So if she were running only FMPro12, would she check “Store container data externally”? I know, I know…….I should talk her into getting Server!
Jane, “Store container data externally” will work with FileMaker Pro for a single user and with FileMaker Server with multiple users. I have NOT tested how it works with FileMaker Pro for multiple users (w/o FMS).
If your customer has two or more users, I would recommend FileMaker Server. You can get an account with FileMaker hosting provider for $30-50/month or license FileMaker Server annually for around $300.
This is very helpful, but I have a question. I designed a FileMaker Pro database to be used as an electronic portfolio for my students in art class – I move the database back and forth between my iPad 2 and my computer – I use my iPad 2 in class to sort students, and take photos directly into container fields. I can view portfolios by grade level, or as thumbnails from each year of school. The intent is to track growth over time. Used this way (utilizing the camera in my iPad 2, then transferring back and forth to my Mac – this has to happen for back up purposes, for updating class lists, adding new layouts as needed, and also for publishing student art to my art room website, etc.) while also adding new images – don’t I need to embed the images in the container fields? Where do they get stored on the iPad/Mac with all the transferring back and forth? I need to have the images linked to specific fields/students in both places. My database is over 4 gb, and I know that is way to big. Thank you so much!
Cynthia, there are two methods with FileMaker Go and container fields on the iPad. The first is to embed the documents or images in container fields directly in the database. This is necessary if you need to move the database back and forth from the computer to the iPad. This sounds like what you are currently doing.
The second is to host (share) the database from the computer using FileMaker Pro. The database will reside on the computer. Then you access it on the iPad over WiFi. The iPad can view and edit records including adding pictures taken with the camera. You can also use external container field storage so the database will not grow to a large size.
Thank you, Kirk.
Would you please answer three more questions?
1) Would hosting the database on my Mac work with my iPad via bluetooth/Bonjour? I was able to create a network that way in order to use the “Reflection” app from my iPad to my SmartBoard, (and am able to project student work from where ever I am in the classroom via my iPad.) The school wireless would not allow me to connect.
2) If I am able to use Bluetooth/Bonjour to set up a local network, and I have all the images saved on my Mac rather than embedded in my database, will I still be able to use scripts that I have added which enable me to export specific container field contents from a found set sorted in a specific way? Will I need to change the scripts? I need to still be able to export those images so that I can use them online, as well as in my digital portfolio. I linked my iweb “galleries” page so that you can see the amount of work I that upload.
3) Last, if it is indeed the best option for me to continue on with what I am currently doing, at what size would my database become unmanageable? I always have backups, however corrupted files are never fun. I purchased my iPad with a grant, and it has 64 GB in memory. However, if I continue as I have been, using 4GB a year, desiring images from each year for 6 years, we are talking 24 GB. For a database. Scary music.
Thank you! I am self taught on FM Pro/FM Go, but I know in my heart of hearts that this not only makes a difference to me as a teacher, but to my students.
Thank you so much, Cynthia
Cynthia,
1. You cannot connect to a hosted database over Bluetooth.
2. You can still export the container fields with “external storage” on your Mac.
3. In my opinion, 4GB is a large database for a mobile device.
Hi,
We are using FM Pro 12 Advanced on a Macbook, FM Pro 12 on a Windows PC and FM Go 12 on several iPads and iPhones. The database is hosted remotely on FM Server.
We have integrated GoZync from SeedCode, which allows us to sync data between devices. This works great but it also means that we have to embed PDF files in container fields if we also want them to sync properly.
I’ve started to notice an increase in file size but I don’ t think there’s much that can be done about it…
Thanks
Brian.
Brian, I would modify the script to “sync” the container fields to purge records on the mobile device of unnecessary PDF files. The specific criteria for purging will be unique to your situation.
Hi Kirk – I have a question about moving a folder of externally stored documents that have links in my FMv11 solution. I am upgrading to FMv12 and a new server machine. How can I move FileMaker to a new machine and not lose the connection to the unstored documents?
Thanks –
Linda
Kirk, could you be a little bit more explicit on creating the script per your 2/8/2013 post saying “Brian, use Set Field with the new container (remote storage) as the Target and the original container (by reference) as the Result.”
I have a container named Picture. The pictures to go into this container are stored by reference and found in a folder named Pics. This Pics folder resides next to the actual database.fm12. I need to move the Pics folder into a new folder named Files, where Files resides next to database.fm12, so my path to the external storage will be [db]/Files/Pics/Picture
Fred – A limitation of using “By reference” containers is the file path will break if the folder is moved from the original location. The following answer assumes this is NOT the case in your situation.
To move the “By reference” container to external storage:
1. Create another container field in the same table
2. Under Field Options > Storage, check “Store container data externally”
3. Use the menu File > Manage > Containers to modify where the files are stored
4. Write a script using a Loop to move the files from the original container to the new one
Hi there,
I’ve recently moved to external data storage and really love the speed/size benefits. The only limitation I’m stuck on is that the changes aren’t saved until you close the database.
Do you know if there is any script to run that can push these changes through without closing the database?
Thanks!
Mitch – Can you elaborate? What do you mean by “changes aren’t saved until you close the database.” FileMaker saves a records when it is committed, usually by changing records, layouts or modes.
This is a great tip. Something I’ve been looking for a long time. I want to add code that will open the file (PDF) in a web browser. My problem is determining the path to the file that I can send to the web browser. This is a single use app that I need to move between a mac and windows computer. Any thoughts?
Tom – You could save the PDF to the users desktop or temporary folder (see Get functions). Then use the Open URL script step to call the file locally using the “file:///” protocol.
Great post and thread – very helpful : )
My question: We have lots of multimedia for our co-creation tool, and made the mistake of not storing externally so now have DB bloat. We will fix this as you have suggested…but as we are paying for filemaker server as a service, we still have to pay for data storage. So, is it possible to store these blobs on an external cloud services such as AWS ?
Many thanks
James – With FileMaker Server 13, you can specify a separate folder to store external containers. It can be a local or remote volume. With a third-party app to mount S3 as a local volume, it may be possible.
This thread is very helpful. I am confused on one thing though. So does this thread mean, if a container field is set to store externally, and a document is added to that container field, even if “store only as a reference” is UNCHECKED, the PDF won’t actually be embedded in the database, but it will go to that external files folder location? The reason I ask is this. I have FMP 12 Server, and FMP Pro running on 2 Macs who previously always added PDFs via “store only as a reference.” But now we just added a PC to the mix, and the PC can not “see’ the visual of the PDFs. SO i am hoping chaning all of our container fields to store externally will fix this problem. But before I “reseat” about 1200 PDFs (that were originally added via “store only as reference”) I want to make sure this is an accurate path forward. Thanks! – ann
HI Ann – To transfer containers from “by reference” to “external storage”, you will need:
1. The original container field with data stored “by reference”.
2. A new container field set to store data using “external storage”.
3. A script to loop through the records and set the new container field equal to the original using this calculation:
Let ( [EncodeFile = Base64Encode ( Demo::DocumentReference ) ;
DecodeFile = Base64Decode ( EncodeFile ; GetAsText ( Demo::DocumentReference ) ) ] ;
DecodeFile )
Note: You will need a copy of FileMaker Pro 13 to make the conversion. The Base64Encode and Base64Decode functions are only available in FileMaker 13.
I have a aplication created in FileMaker Pro 13, exported to an iphone with IOS 9 with filemaker go 13 installed, Is it possible to create a folder in the cloud (icloud or dropbox) to externally save the data in the container fields ?
The idea is not to load the iphone with the images in the data base.
Hi Kirk,
Hope you all are healthy and safe !
I have a question about moving a folder of externally stored documents that have links in our FMv11 solution. We are upgrading to FMv19 and a new server machine. How can I move FileMaker to a new machine and not lose the connection to the unstored/referenced documents and images ?
Thanks,
Anand