Recursing down to root in a table representing a logical tree

[FileMe]

I have a table defining a logical tree. Lets define a simple analogy table here, based upon windows directory structure. A folder may either have a parent folder, OR be a root folder and thus "hang onto" a drive (C:, D: etc).

The result (calculation) I want is the column "Magic":

Folder ParentFolder Drive Magic

a1     null         C:    C:

a2     a1           null  C:

a3     a2           null  C:

b1     null         D:    D:

b2     b1           null  D:

You get the drift - very recursive..

The "Magic" formula is obviously something like this:

if (ParentFolder != null; getSelectedRecord("Magic", "Folder" = ParentFolder); Drive)

.. the only problem being that getSelectedRecord doesn't exist.

I'm utterly new to this platform, so I have no clue. I've of course seen the getNthRecord, but this seems made for "dynamic" positioning (since it goes for record number, not record Id). Also I really find it amazing if it would be possible at all to define a field function to go look at other records, in particular a recursive variant at that. I've come to understand that there is this thing called "Custom functions", and currently have high hopes for something like that!

I have a feeling that a solution will be along the lines of some rather elaborate custom function, basically running through the entire table stacking up some big string, and from that string chop out the "drive".

Then, will it be possible to use that result and link it to some Drives table, e.g. thus also getting the "bytes left (on drive)" for each folder?

Any help would be very welcome.

[comment]

If I understand this correctly (big if), cMagic =

Case (

IsEmpty ( ParentID ) ; Drive ;

Parent::cMagic

)

Note that I am using ParentID instead of name, so folders can be renamed without hosing the relationships.

[FileMe]

Either I obviously don't have ANY idea of just HOW different FileMaker is from "a standard DB", or I've misrepresented the original problem: The four columns in my example table are just that: four columns in the same table - right? "a1" and "a2" and such are thus actual strings ("null" is .. NULL).

I don't get what "Parent" and "ParentID" refers to. Unless they are some absolute magic from FileMaker, please stick to the column names as stated. And lets call the table "FileSystem" (and the potential table holding bytes free and such for each drive C: and D: for "Drive"), just for the fun of it..

Do you still mean your code snippet should work?

[comment]

just HOW different FileMaker is from "a standard DB"

It is slightly different, but the basics are the same. One-to-many relationships are usually based on primary and foreign keys, and these keys should be meaningless - I believe that much is true for ANY relational database.

Now, do you have a self-join relationship defined, so that a folder is related to its parent folder?

[FileMe]

Two posts must have been deleted in this thread, or this website have extremely bad affordances for paging through the replies. Might due to the server move or some other lame reason. I basically answered to "comment"'s comment, and he answered back, attaching some files. I never got to see those files, though.

However, due to comment's comments (!), I got this thing working, and it was simply the self join (which makes you make a new occurrence of the table in question), and then the formula works pretty much as naively expected!

Thanks a bunch, comment!

HOWEVER, I just hit a new little wall when trying to make a layout: I regard to the original situation ("Drives and Folders"), I want a layout for Drive, showing the data for one specific Drive, that displays all Folders in a list (rather straightforward using normal relationships). However, "importing" (I still don't quite know the terminology in FileMaker) the fields "upwards" (as in adding fields from Folder in the Drive table, which is what happens when you do the layout thingy), these fields suddenly display the data "". If I throw the relation between Drive and Folder (the one relating Drive::DriveID and Folder::Magic), and instead chuck in a relationship between two normal columns (e.g. Drive::DriveID and Folder::DriveID), it "works", but obviously not fully, since only those records having data in Folder::DriveID is linked, missing the "cascading" ones.

I tried going to the Magic's field calculation, and found a "Storage Option" button there, and behold, there's a "indexing options" thingy there. But THERE I met a brick wall: one cannot make this cascading calculation indexed, due to one of a whole raft of reasons. Hopefully it isn't the one that says that I cannot do it since it refers to a non-indexed field, and the non-indexed field being referred to is myself - that would be a rather nasty Catch-22, if you ask me!

Any ideas of how to overcome this deficiency would of course be very much appreciated.

[comment]

The forum had some issues recently:

http://fmforums.com/forum/showpost.php?post/267273/

Back to the topic: the cascading calculation must be unstored, since it relies on related record. This is a good thing - if it were stored, it wouldn't update when related records are modified.

The solution to your current problem is to define another self-join and another calculation, this time in the opposite direction: let each record list its immediate children (if any) and its children lists (a cascading calculation again). This way each record calculates a list of ID's of all its descendants. You can base a relationship on this field, because the unstored calculation is on the "one" side, while on the "many" side there is a stored ID field.

You would do well to upgrade to at least version 8.5, since it has the convenient List() function. In previous versions you would need to define a value list of children ID's and use the ValueListItems() function to get the list of related values. Awkward, though doable.

If this sounds complicated, it's because it is. Certainly not beginner's stuff, so be prepared to spend some time with it. BTW, I am puzzled why you chose to single out the drives into a separate table. Isn't a drive just another folder with ancestor status (i.e. no parent)?

---

P.S. This is very close to a BOM. You may find this thread helpful, esp. the demo mentioned here:

http://fmforums.com/forum/showpost.php?post/221063/