5 Replies Latest reply on Oct 21, 2015 2:04 AM by bigtom

    Construct database from folder structure


      Hello everyone,


      I am completely new to FileMaker.  In fact I have been playing around with a trial version for a while, but still have some questions and before actually buying it I'd like to hear from someone more experienced if what I plan to use it for is actually possible. 


      So here's the situation:  I want to create a FMP database for data that is currently organized in a folder structure which looks like this:









                  and potentially some other files


      YYYY-MM-DD_hh-mm-ss is a folder which represents a "session", which on the disk contains all the media files and should be represented by one record in FileMaker where each of the 5 files appears in a container field.   However not all sessions have all 5 files, some might have only a video some might only have audio.


      My first question:  Is there a way to import this into FileMaker so that the structure is preserved and FM automatically detects how many items are in each folder/session (and keeps the other containers empty)?  Or if FM doesn't do it itself, is there a way to create an AppleScript to do it?   I guess that's the way to go as I'd also like to extract the date/time of each file by parsing the name string and the length of each of the media files.


      So I've played around with it a little and managed to create a tab delimited text file for a few entries, which works more or less.  Took me a while to figure out the correct format for container fields but this actually works and links the files correctly:


      moviemac:/FW Archive/2013-05-02-KT/2013-05-02_06.55.28/2013-05-02_06.55.28.wav


      But I ran into a few problems with the container fields: 

      (1)  Storing a reference to the files (which is the only way to import them, it seems to me?) destroys the link whenever the database (the folder structure) is moved.  I do want to be able to move the entire thing to an external disk for example without having to manipulate the FM file every time.


      (2)  Now FM also has the option to store the files automatically relative to the database location. However, after choosing that option, (a) I was unable to create a file path the way they are now.   I only managed to create one folder level but was unable to sort the files into subfolders.   So if I have a “Date” field and a “Sessiontitle” field I could either have the file automatically filed at /DatabaseBaseDirectory/Sessiontitle/ or /DatabaseBaseDirectory/Date/ but not /DatabaseBaseDirectory/Date/

      Sessiontitle/ as I would like too.  Is there a way to do that?


      (3)  Is there a way to have FileMaker to "watch" all the folders in the folder structure, and automatically add and list files that are stored in the session folders so that whenever I add a file to a folder it would automatically appear in filemaker instead of having to add it manually.


      Thanks in advance for any suggestions or hints!

        • 1. Re: Construct database from folder structure

          There are others who know more than I. However, it's been 3 hours, so I'll say something. It will be kind of "old school".


          (1) It is possible to use a calculation for a Container; as long as it produces a correct FileMaker path to the file. In other words, you get the movie file as a 'reference', which brings in its path (as one of the lines of info). A calculation which reads that path, with a result of Container, can also show, and use the same.


          Near the beginning is where the "system drive" or "volume name" is. Lets say that you use a field (text) to specify that instead. You could then remove the existing one and use that text instead. In this way you can move all the folders to another place, and the calculation can then produce there new paths, and the 'containers' will still work.


          That "volume" could be a field in a 1 record table. So one change would change all calculation paths.*


          (3) AppleScript has a Folder Actions, which can be attached to any folder. It can then run commands. FileMaker is accessible to AppleScript. So you could likely be able to check whether you have an file, and add it if needed. I messed with this (years ago), and don't remember much; hopefully someone does. But it can be done.


          FileMaker can also run AppleScript itself, via its Perform AppleScript script step. AppleScript can run Unix commands also. It is also possible to change a FileMaker path to a Mac path (AppleScripts default) to a Unix path. FileMaker paths are more similar to Unix paths, just start with something like: moviemac:/


          So the basic answer is "Yes," FileMaker can do it; but it (and you) may need some help. I'm afraid I'm a bit too old, and somewhat damaged, but someone knows how to do all of the above…


          * It is all unstored. You might however want to pull the file name out somehow, so that it can be stored, as I imagine you'd want to somehow do a Find or Sort.

          • 2. Re: Construct database from folder structure

            Thank you, fentonjones, for your reply. 

            That does sound promising!  I would have to look into how to build the correct calculation then.

            Anyone else who could help me with that?


            Concerning the other points:  I do know about AppleScripts folder actions.  Question is if it can also watch an entire folder structure?

            And whether that would work well together with letting FileMaker create a folder structure relative to the database?

            (For which I still haven't figured out if it's possible to create a more complex structure as I mention in (2)?)


            Any further help greatly appreciated!

            • 3. Re: Construct database from folder structure

              AppleScript, running via FileMaker's 'Perform AppleScript' can create folder (or folders); using its "do shell script" command it can alternatively use the Unix command to create folders ( which can create multiple folders with pretty complex structures, all at once ); both of theirs folder creation commands can be smart enough to never override an existing folder {critical}.


              I'm pretty sure the AppleScript "Folder Action" needs to be assigned to each folder. I think that such attachment can be scripted however ( and they would all be running the same AppleScript "Folder Action", either running commands to a FileMaker file/layout(s); or calling a FileMaker Script itself. [ I've some simple thing like this, years ago; but I cannot remember it well (if at all). I just know it can be done.]


              It would be possible, though some work, to either run an AppleScript (especially to create folders needed ahead of time), and run the AppleScript "Folder Action" to do things in FileMaker, after you manually enter a file into a folder. [ The file would need to know WHERE in the FileMaker file it is supposed to go, which table to add a record to, and how it is linked to anything else. You would have the file's name, as well as any other info the OS can read about it.]


              I will be away from home tomorrow, unless later. Perhaps you can get started on thinking about this. Hopefully someone else can help with this, as I'm not a highly skilled designer, anymore.


              [ I should say, I'm no longer actually "working" anymore, for two reasons. First, I'm 65, which certainly slowes me down. But more importantly, I had a fairly bad stroke 5 years ago, which, while not physically damaged, I have some trouble with such things as words, etc.. For example, it is difficult for me to read all of what I write, nor remember details. It is all more of a hobby…]

              • 4. Re: Construct database from folder structure

                Fentonjones, I really appreciate you taking the time to help me out!

                (Sorry for not following up earlier, but I had a hardware issue which kept me busy.)


                In the meanwhile I did figure out how to get my data into FileMaker.  It's not the prettiest solution, but given my lack of FM programming knowledge it proved to be the fastest, so I wrote an AppleScript which produces a tab delimited text file which I can then import to FM.


                I also figured out how to produce the right path calculation for my container fields in open storage.


                However soon I run into a number of problems:  (a) the container content for the imported files are only references to the files, and when I then change the path to store the files in FM it "skips" over all of them when moving the stuff to its new location.  Is there a way to change the format for container fields from reference to open storage for already imported files?  Or any other way how to do that?


                A second problem is that it looks like it doesn't accept dots in folder names, so it renames the folders.  So even if I did get the container fields properly formatted, I still would not be able to reproduce the folder structure as it is.  Any ideas? 


                I'll have to look into fentonjones' suggestion for Question (3), i.e., watching all the folders in my DB with more time later.

                • 5. Re: Construct database from folder structure

                  I am guessing you have already seen the following link:

                  Importing a folder of files all at once


                  So I gather you have some other process that generates the files and you want FM to keep track of them without moving the files from their current location. Not sure if that is possible. I believe the open storage must be in a folder where the file is. Directly specifying some other location on the server or network might not work out.


                  I am not sure if these folders follow a file that is hosted from a different location after being specified in the admin panel. If that is the case you might just put your FM file at the top folder of you data folders. The external open folders might stay on the server though. You will have to test it and see. You will not likely be able to be moving the folders if you get it to work.


                  As an idea to start you might want to import all the files into containers in one table. Then use a script to parse the file names of each record to start building the structure and moving the the files into the proper related records.


                  To keep this updating you can have a script that continues to import new files by checking folders daily. Since you have a date based folder system that might not be too hard to do. If you really need everything down to the second this could be very time intensive.


                  Some say you do not want to be letting people have direct access to files in FileMakers storage system, but some people say its ok.


                  Hope this helped a little.