8 Replies Latest reply on Sep 24, 2009 2:10 PM by ripplestone

    nesting files



      nesting files



      I'm making a filemaker solution for real-world files. these are very old government files that have subfiles, and i want to be able to sort results based on them. 

      I've got a solution for the documents themselves, but i need to add several more characteristics and i'm really confused. Here's the problem: i have each record representing an individual document in a file. I want to be able to do a search for a document, find it, and then be able to pull up all of the other documents in that particular file, so i can see what its been grouped with. On top of this, some of the documents are themselves files, with additional documents stuffed into them. So I need to be able to identify a document as being both a document, and also a file that contains documents. 

      can anybody suggest how to do this? 



        • 1. Re: nesting files

          Perhaps this will work:


          Two Tables: Files, SubRefs


          Fields in Files table:

          FileID (Primary Key, must be a unique identifier)

          All other fields needed to describe document.


          Fields in SubRefs table:

          FileID, SubFileID



          File :: FileID = SubRefs :: FileID    (select allow create and delete options for SubRefs)


          On a File base layout add a portal to SubRefs with just the SubFIleId field visible.


          To add a document to a file, simply enter its FileID into the SubFileID field in the portal.

          To drill down to the next document, Make each portal row a button


          Set variable [$FileID ; Subrefs :: SubFileID]

          Enter Find mode []

          Set field [Files :: FileID ; $FileID]

          Perform find []


          Since any given file record can list multiple sub files in the portal, you can nest documents as many times as needed to track your documents.


          Edit Note: fixed minor typo in script example.

          • 2. Re: nesting files

            Wow! Thanks!

            this seems to work, and you've managed to suck a newbie into the weird and i-wont-say-wonderful world of scripts.... I'm not yet sure I understand WHY the script you wrote works, but I'm sitting here puzzling it out Along with getting the fucntionality you describe for the portal, which is now in any case really quite awesome as a displa


            what i can do with this now is search in the file table, find File 1 and see that File 1 has subfiles A, B, C, D.  

            At the subfile level if i've found subfile 1 C, I can search and find the rest of the  subfiles in file 1.


            The only problem that i have with this solution is that it keeps two sets of data on two different tables which makes for searching problems. For example, lets say i have Two files, each with several Subfiles.

            Scrawled across File 1 is a rant about Reagan and the invasion of grenada, but the subfiles don't mention that at all; they're about the monroe doctrine.

            File 2 is reversed--file is scrawled with rants about monroe, contents are all Reagan and grenada. (I'm making this up, but you get the point)

            And then there are 700 other files and subfiles that mention one or the other or both.

            I'd like to be able to search in the subfiles for ay document about reagan, but still have File 1 one pop up, even though its subfiles don't have anything on reagan. 

            See, the problem is that the file itself can act as a document, and i'd like to be able to search at both levels. I tried setting up an additional relationship between the notes fields on the file and the subfile, but that just seems to mess with the portal itself.


            the one great lesson of the day is that the way i organize information in my brain turns out to be really, really complicated in a way that you can only see when you start having to describe it in discrete units!





            • 3. Re: nesting files

              The only data in the sub file table should be File IDs that take you back to the File table. I suggested that precisely because a record may represent a file, a document or both.


              Thus, all document content searches would be performed on the File table.


              Say you need all documents that refer to the Monroe Doctrine. You'd enter "Monroe Doctrine" in the relevant fields and click perform find. You'd get a set of all documents with those keywords in the fields where you entered them in the find request. You could click through them to see each document in turn with any sub documents appearing in the portal.


              It sounds like you may need the ability to traverse the links in both directions. In otherwords, if you are looking at a document, you might need to know what files or documents list the current document as a sub document.


              If you want this, we can set up a new relationship to a new table occurrence and place a second portal on our layout.

              In the Relationships Graph, select the SubRefs TO and click the button with two plus signs. This generates new TO box, change it's name to Parents.


              Link it into the graph to make:  File :: FileID = Parents :: SubFileID


              Place a portal on your File layout that refers to Parents. Place Parents  :: FileID in this portal.


              You can set it up with a very similar script as we did the first portal.


              Set variable [$FileID ; Parents :: FileID]

              Enter Find mode []

              Set field [Files :: FileID ; $FileID]

              Perform find []

              • 4. Re: nesting files

                not quite there yet. my problem is still in the first round of file/subref tables. it works well in that i can type a subfileID into the portal, and it creates a new record in the subrefs table, with the correct fileID. For any given FileID, all of the subfileIDs appear in the portal. The problem i'm having is that while i can use the portal to creat the subfiles, it doesn't actually include that piece of the data on the file record itself--each record is unique because of content or whatever, but none have a unique subfileID.

                Say I have folder A with subfiles A1, A2, and A3. That's four records in the File table (folder A as a record b/c its also got info on it) all with the same FileID. Using the subfileID portal to make A1-A3 generates three records in the subrefs table. But when i look at any of these four records in the file table, I can't distinguish among them. Each, including the record for folder A itself, has the same fileID and the same list of subfileIDs. The more I fool around with this, the more the second portal seems frivolous because for any given record, I would have the subfileID field giving me the data on the record if it is a document in a file; the portal telling me which other records belong to the same FileID group, and the fileID field will tell me which file its in; although the key would be to set it up to click through and open any of the related records.

                • 5. Re: nesting files

                  IIUC, a document/file can belong to no more than one parent folder. If so, you only need one table, with fields for FileID and ParentID. If a document is nested in another, enter the parent's ID into the ParentID - otherwise leave it empty.


                  See a simple demo here:



                  And also:


                  • 6. Re: nesting files

                    Three cheers for recursers!

                    Totally does the trick. I've got something that does exaclty what i need to now, ie show both the parents and the children. I've also put in a portal to show the siblings; i did this by creating a new TO of the base record, and linking the Parent ID in each of these TOs to the ID in the Parent TO, and then putting in a portal to the duplicate base record TO. It works, I can see all of the siblings, though the record ID comes through as well, so the record sees itself as a sibling. I suspect this is an ugly way of doing this, but I should be able to write a script that prevents the record from seeing itself as a sibling.

                    Anyway, thanks to both of you for throwing some thoughts into the pile. Its been a big help. 

                    • 7. Re: nesting files

                      To prevent a record from including itself in the portal to siblings, add another predicate to the relationship so:


                      Table:: ParentID = Siblings:: ParentID


                      Table::SerialID ≠ Siblings::SerialID

                      • 8. Re: nesting files

                        clearly i need that to check my own ability to define my thank you above as the solution to my own problem. i've become an infinite loop.

                        looks like a good solution. thnx again.