8 Replies Latest reply on May 16, 2011 1:15 PM by philmodjunk

    Attachments - Internal to DB File or External?

    Vinny

      Title

      Attachments - Internal to DB File or External?

      Post

      Hello,

      I'm looking for experience and feedback concerning how to handle attachments for my database.

      I'm developing a complete ERP system for my small manufacturing business.  I'm planning to have attachment capability for products, quotes, invoices, accounts, and contacts.  I'm estimating that there will be many files, of many file types including: pictures, MS office, pdf, etc.  I plan to have the DB on a dedicated machine within our LAN.

      I'm worried about the long term and the database file becoming too large if I use the standard file attachment methods built into Filemaker.

      My concerns are as follows: Slow database, slow back up of database, DB corruption

      I may have found some plug ins that will allow me to "attach" files, but actually store the file on the dedicated host machine.  This method will most likely require much more application logic as it will have to handle many If conditions (i.e. file or folder exists, etc.), and I will need to implement conventions for file naming, etc.

      Does anyone have any guidance on this topic? Any experiences to share?

      Thanks in advance for your help & support.

       

        • 1. Re: Attachments - Internal to DB File or External?
          Vinny

          Anyone have any experiences they can share?

          For example, an obvious risk is losing all of your attachments if they are embedded in the FM file and for whatever reason, the file is damaged or lost.

          In contrast, if the files are stored on the server PC, then there will be much more coding to handle the files.  i.e. uploading the file while in the DB...the DB will have to copy the file and put it on the server in the correct folder, etc.

          Please let me know.  I'm building a DB from scratch and want to make the best choice.

          • 2. Re: Attachments - Internal to DB File or External?
            philmodjunk

            Apologies Vinny,

            There are bugs that "lose" posted responses once in a while and I never noticed that such a bug lost my previous post. That's why there's a blank post here with my name on it. Undecided

            You normally attach files by inserting them into container fields. If you use the store by reference option, the filepath to the file is entered into the field. If you do not select this option, a physical copy of the file is entered into the field.

            I wouldn't agree that "there will be much more coding to handle the files" when you store by reference, it can actually require less coding depending on whether you need to edit the files after they are inserted or if they are strictly read only.

            I also wouldn't rank the risk of losing all attachments if the file is damaged very high either. All data in your database is potentially of critical value. You need a good system for backing up all your files frequently and you should keep mutlple copies of these backed up files with at least a percentage stored "off site" so that you are as disaster proof as possible. Whether the files are stored internally or simply linked, you would still want to back them up in this manner.

            Here are the key trade offs to consider:

            Without store by reference:

            1. FileMaker files increase in size rapidly with each inserted file.
            2. Files cannot be opened for editing by double clicking the file. Instead, a copy of the file must be exported using export field contents. If you then want to save the edited file back into the container field, you must re-insert the file from whatever location you exported the copy. This process can be supported with extra scripting.
            3. Files can be more easily accessed over a network when you have both Mac and Windows Platforms.
            4. Files can be accessed from iPhones and iPads when using FM GO on these devices

             

            With Store by Reference:

            1. Database file size does not increase significantly with each inserted file.
            2. You can open a file for editing by double clicking the container field. When you are done, you simply save your changes and they will be accessible/visible to the next person to use Filemaker to open the file.
            3. When you host the database over a local network, users will not be able to access the files unless they all map and mount the shared directory in exactly the same way as the computer that originally inserted the file. This can be a challenge when you have Mac and windows systems on the same network as each maps such directories differently. This can require using special calculated container fields that return the correct filepath for the current platform.
            4. FM GO users cannot access the file as iOS devices do not use the same Filepath technology.

             

            There are two other methods you can use to link a file (by reference) to your database:

            You can use a Web Viewer to access the file. This can be a very convenient way to display the contents of a PDF file on both Mac and Windows platforms and for FM GO users.

            You can use Open URL in a script to open files for editing.

            • 3. Re: Attachments - Internal to DB File or External?
              Vinny

              Thanks Phil.  As always, very much appreciated.  Once my company starts making money I will look to hire you for the ultimate FM solution (assuming you are available for such a job).

              I think I'm going to investigate a hybrid approach to attachments.  My idea may not be possible, but what I plan to do is the following:

              - All computers are running Windows XP, Vista, or 7

              - I am the only Iphone user, and there is no need to get attachments on any mobile device

              I want filemaker to handle the file attachments, BUT, in the background, it will be managing the files located on the server (same location as the actual FM file).

              For example, when a user "uploads" an attachment to filemaker, filemaker will just be making a copy and store it in the predefined place on the server.  Also, at that point, the user will supply a subject for the file, and filemaker will give it a filename (also predefined structure).

              When a user wants to view the file, filemaker will give them a local copy of the file to view / edit, etc.  If the user then wants to upload the changes, filemaker will keep the original, and add the revised copy as a revision.

              Am I dreaming?  I'm thinking I found a script call Moo Plug in that would give me some additional handling to do such a thing.

              • 4. Re: Attachments - Internal to DB File or External?
                philmodjunk

                There are several file handling plug ins available and Moo Plug is free, but you can actually do all of the above with just FileMaker. If you need to do additional file/folder tasks such as creating a new folder into which to add new documents, then you'll need that plug in.

                For FileMaker to store a file on the server, a script can insert the file into a container with store by reference enabled, then can use Export field contents to export a copy of the file to the server directory where you want to store it and it can then re-insert the file with store by reference from the server location.

                To provide the user with a "local copy", a script can use Export Field contents to export a copy of the file to the user.

                • 5. Re: Attachments - Internal to DB File or External?
                  mgores

                  Just an additional suggestion......

                  I use the method described by Phil where when the file is placed in the container the script exports it to a location on the server, then reinserts itself as reference from that location.  For the purposes of future modifications or possiblly getting files with the same name, I also add the recordID number to the filename when I defined the variable for the filename to save on the server.  That way if Jim places a file named Scan001.pdf in the data base, it gets renamed XXX-Scan001.pdf.  If anyone else tries to attach a filenamed Scan001.pdf, it will be named with that records number and not overwrite Jim's or any other version.

                  • 6. Re: Attachments - Internal to DB File or External?
                    philmodjunk

                    That's an excellent idea!

                    • 7. Re: Attachments - Internal to DB File or External?
                      Vinny

                      Well, I finally reached the section of my project concerning attachments.  I plan to use Phil's method above.  All files will be copied to the "server" (host computer) in folders designated by the usage.  The filenames will be renamed using a scheme similar to what Mark recommended above (using IDs).

                      As of right now, I can insert a file, and then export it.  However, I just realized that this method will leave a copy of the file on the host computer.  I need to find a way to manage the files (delete, replace, etc.) on the host computer.

                      Do you recommend using the send event function and calling a delete command similar to a dos command?

                      • 8. Re: Attachments - Internal to DB File or External?
                        philmodjunk

                        Actually, I've seen a technique used with Export Field Contents to delete such a file.

                        You can use Export Field Contents with the same file path as the originally inserted file, but with no field specified, to delete the file--provided user permissions currently in place permit deleting a file from this location.