MightyData

FileMaker and WordPress Consultants

  • Company
    • About
    • Contact
    • Team
    • Artists
  • Services
    • Consulting
    • Development
    • Our Process
    • Coaching
    • Training
  • Results
    • Customers
    • Success Stories
    • Testimonials
  • Blog

Stop Embedding Documents in Your Database!

August 9, 2012 by Kirk Bowman 30 Comments

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”.

Container field in FileMaker 12

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.

External storage setting for container field

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).

Filed Under: Relational Design Tagged With: Container fields, FileMaker 12, Performance

Comments

  1. Jane Gordon says

    September 6, 2012 at 10:46 am

    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.

    Reply
    • Kirk Bowman says

      September 6, 2012 at 6:10 pm

      Hi Jane, thanks for dropping by. This is one of the best benefits of FileMaker 12.

      Reply
      • Brian McIntosh says

        February 4, 2013 at 5:35 am

        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.

        Reply
        • Kirk Bowman says

          February 5, 2013 at 7:16 pm

          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”.

          Reply
          • Brian McIntosh says

            February 7, 2013 at 9:08 pm

            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

            Reply
            • Kirk Bowman says

              February 8, 2013 at 10:32 am

              Brian, use Set Field with the new container (remote storage) as the Target and the original container (by reference) as the Result.

          • Tony says

            February 22, 2013 at 11:15 am

            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.

            Reply
  2. Jane Gordon says

    September 6, 2012 at 12:06 pm

    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’?

    Reply
    • Kirk Bowman says

      September 6, 2012 at 6:16 pm

      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.

      Reply
      • Jane Gordon says

        September 8, 2012 at 10:06 am

        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!

        Reply
        • Kirk Bowman says

          September 8, 2012 at 12:27 pm

          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.

          Reply
  3. Cynthia McKeon says

    September 13, 2012 at 3:48 pm

    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!

    Reply
    • Kirk Bowman says

      September 17, 2012 at 9:02 am

      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.

      Reply
  4. Cynthia McKeon says

    September 20, 2012 at 2:05 pm

    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

    Reply
    • Kirk Bowman says

      September 20, 2012 at 7:39 pm

      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.

      Reply
  5. Brian Curran says

    November 22, 2012 at 4:59 am

    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.

    Reply
    • Kirk Bowman says

      November 26, 2012 at 12:01 pm

      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.

      Reply
  6. Linda Carter says

    February 19, 2013 at 4:49 pm

    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

    Reply
  7. Fred Hottinger says

    February 14, 2014 at 3:15 pm

    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

    Reply
    • Kirk Bowman says

      April 14, 2014 at 10:58 pm

      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

      Reply
  8. Mitch says

    April 25, 2014 at 4:39 pm

    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!

    Reply
    • Kirk Bowman says

      April 28, 2014 at 5:10 pm

      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.

      Reply
  9. Tom Lewis says

    June 23, 2014 at 3:43 pm

    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?

    Reply
    • Kirk Bowman says

      August 31, 2014 at 8:43 pm

      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.

      Reply
  10. james says

    August 22, 2014 at 6:46 am

    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

    Reply
    • Kirk Bowman says

      August 31, 2014 at 9:19 pm

      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.

      Reply
  11. Ann Perri says

    April 28, 2015 at 12:32 pm

    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

    Reply
    • Kirk Bowman says

      May 4, 2015 at 7:56 am

      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.

      Reply
  12. Flavio Cerletti says

    February 25, 2016 at 12:00 pm

    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.

    Reply
  13. Anand Mohan says

    July 28, 2020 at 2:21 am

    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

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Let’s get started on your project.

It will be more fun than you think.

Get in Touch

  • Company
  • Services
  • Results
  • Blog

Copyright © 2023 · Parallax Pro Theme on Genesis Framework · WordPress · Log in