Recursive calculations

[hmhalff]

I've set up a database that embodies a tree structure and I want to propagate a value in a field called "Enables" down the tree through a calculated field that I've called "Owner." I set up a relationship called "Parent" from the database to itself that matches each record with its parent in the tree. I try the following recursive definition for Owner.

if(Enables > 0, Enables, Parent::Owner)

Filemaker Pro tells me that this definition is circular, but it's not. What gives?

[BobWeaver]

Filemaker considers a calculation to be circular when it refers to a value in the same field even if it's in a different record. The reason is that you can't tell in advance that it won't relate to the same record.

The only way around this is to use a looked up value rather than a calculation. This is a bit messy, but will work. You will have to use a script that executes a number of relookups in order to propagate the values. The minimum number of relookups that you do is one less than the depth of the longest branch of the tree. You can do more; it won't hurt.

[LiveOak]

Bob is correct. I've just gone on a lenght about MLM (multi-level marketing ) databases and linked list structures in FM in a couple of previous threads. I've managed to fool FM by using another file with a 1:1 relationship as a "mirror" to stop the error messages. After extensive work with this approach, it became clear that FM will not reliably drive the calculated results all the way to the top of the pyramid.

The only option I can think of in FM is to find the lowest level and perfor a relookup, find the next to lowest level and do another lookup. Repeat this process until the top of the pyramid is reached.

FM (or any classical database) is not well suited to managing linked lists. Special purpose applications are definitely the way to go in this area. I come to this conclusion after considerable time spent investigating approaches in FM.

-bd

[BobWeaver]

LiveOak:

Actually, I don't think you have to do the lookup level by level. On solutions that I have done with linked lists, I just get all the records, and do as many lookups as necessary. While some records may get some erroneous interim results, the end result (after all lookups) has all records with the correct data.

Eg:

code:

---

# Given that the maximum depth of any branch is 10.

Set Field [gLookupCount,1]

Loop

Exit Loop If [gLookupCount>10]

Relookup

Set Field [gLookupCount, gLookupCount+1]

End Loop

---

Sometimes you don't know the length of the longest branch. In that case I set up a test on an applicable field to see if there was any change after the last lookup. If no records had a change in that field after the last lookup, then the values have propagated all the way through the file and we are done.

[LiveOak]

Now that you mention it, this should be correct. I never went back and rewrote the files I was working on to use lookups vs. the relational scheme I was using. The client really wanted the recalc to happen automatically.

You can actually fool FM into accepting what is actually a circular reference by using the "mirror" file concept. The stumbling block is that you can't reliably drive the calculation through the entire linked list when a data element changes in a single record.

If you intend to operate an MLM, I would still purchase a piece of software specifically for this this purpose.

-bd

[BobWeaver]

Unfortunately, the shortcoming of my technique is that whenever a record changes, the relookup script must be re-run in order to update the linked records.

LiveOak:

I'm curious about your mirror file method. Could you explain it in a bit more detail?

[LiveOak]

Nothing too mystical, I just used a second file to confuse FM checking of circular references:

In first file:

FieldA = Rel::FieldB + 1

In second file:

FieldB = Rel2::FieldA

There is one to one correspondence between records in the first and second file (relationships can be recordID <--> recordID, if synchronized). It does (or did) fool the FM parser, but doesn't solve the basic problem of driving calculations through many levels. I guess it's too much to expect that if portals don't always update, that a 50 deep chain of calculations would. The parts that would update were even inconsistent and there didn't seem to be as easy way to trigger the updates. From a visibility and consistency standpoint lookups are a lot more straight forward.

This is why I contend that linked lists are best left to special purpose programs/data structures rather than conventional databases. I have not done any research (all the MLM I ran across were slow/no pays) into such programs, but there must be a bunch of them out there (there's no shortage of MLM's).

-bd

[BobWeaver]

I agree that Filemaker isn't the best answer for linked lists.

In my experience with them, the linked list was a small part of a larger solution which really needed the power of a database. And, the propagated data was only needed for certain reports. So, it was no big deal to call the relookup script as part of the report generation script. It actually solved a rather ugly problem quite neatly.