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:
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!