Multikey on selfjoin

[Anthony Nelson]

I have a self joined table. In my layout I have a portal of which I want to view the entire joined tree. So basically I want to see a particular record's children, grandchildren, etc in the portal.

I know I need to use a multikey calculation to get all the related keys but, I'm not able to get it too work.

I think a big part of my problem is there is an undefined number of levels. The self join could go 1 level or 9 levels deep (as an example).

Can some one help me get going in the right direction?

[Ender]

Check out Ugo's Multi Levels demo here:

http://www.fusiondzign.com/downloads/downloads.htm

Though there are ways to optimize it for FM7, the basic technique is the same in FM5/6 and FM7.

[Anthony Nelson]

I can't get the Multi lines demo to be editable (so I can see what it's doing) and the simple demo, doesn't seem to fit my needs since it is looking for parents and not children and a record is only going to have 1 imidiate parent where in my data, a record can have any number of children.

[Ender]

The design you described is used in this demo. You can unlock the status bar by opening Scriptmaker and running the unlock|StArea script.

Ugo can probably describe how it works better than I, but the idea is each child has to have every ancestor's key as part of its multikey. You can then toggle whether or not to show each level by taking out part of the multikey on the parent side of the relationship.

[Ender]

Hey Ugo,

The converted FM7 version of this seems to have problems with navigation. Can you release an FM7 version of this? I think the technique is still useful for people.

[Ugo DI LUCA]

Hi Mike,

I'll do it. It can in fact be simplified a bit with 7.

[Anthony Nelson]

Simple would be good

[Ender]

Thanks Ugo, It might also help us to be able to see the multi-key fields on a layout so we can see better how they work.

[Ugo DI LUCA]

Mike,

It was fun, also it turns to be late now...

Here's before it gets too complicated the way I'd do it.

Allows for quick building of multi levels relationships.

I need to work a little more on some scripts which would help build a record in tree without going into the lengthy proces of selecting once after the other each parent in Tree, even if it is still easy to do so.

Also, need to work a bit on how to prohibit changes or propagate any changes made to the tree.

Next step, but this should help as a start, even for those who wants to understand how complex FileMaker 6 was compared to 7.

Attachment delete, see next posts for new ones

[Ugo DI LUCA]

Second attempt to attach the file here.

Nope. Doesn't work.

Atteched later

[Anthony Nelson]

ok, but I still am no closer to understanding how to do this. Although I have toyed around with the thought of doing this with two tables. One just listing all the elements and then having a link table that would have each parent/child relationship link. This is how I would have done it with a typical RDBM but I don't know if I gain anything by doing it that way with FileMaker.

Any thoughts?

[Ender]

With this type of hierarchical menu system, while you could use a layout based on another table as the interface for the portal, the portal records themselves (parents and children) must all be in the same table. This is because a portal can only show rows from one table (the table defined for that portal.)

[Ugo DI LUCA]

Let see if this one come through....

Simple version with Parents, GrandParents, Brothers and Cousins.

Oops, this version doesn't have Brothers/Cousins...Are you guy saying you downloaded my earlier version, which doesn't download for me ?

MultiLevels.zip

[Ugo DI LUCA]

Here's the one with :

Parents

GrandParents

Childrens

Sisters/Brothers

Cousins

All Descendance.

I'm working on a version that would just use a global and point directly to the Parent record for record creation. It's just another way around with a global field.

Now, there may be issues with the sortfield for some large hierarchy, although FM7 indexes up to 100 characters, and each key for the sort here is trimed to 5 characters, so it should allow to up to 20 levels.

HTH

MultiLevels.zip

[Ender]

Thanks Ugo!

This looks cool, although I'm still trying to wrap my head around it.

It looks like the keys to making this work are building a sort key, and including each ancestor's key as part of the multi-key.

I figured out how to make the portal show only the decendants of the current record (by changing the relationship to the Display TO.) Now how would we add a toggle on the portal rows to turn on or off the decendants of a person (like a twiddle in a list view of the Finder)?

[Ugo DI LUCA]

The Drill Down technique isn't really a problem.

You'd probably change the way the portal is displayed with a new link from a new TO with :

- a global gPortalKey (text)

- cPortalKey= ParentID & "

[Ugo DI LUCA]

It looks like the keys to making this work are building a sort key, and including each ancestor's key as part of the multi-key.

That is correct.

I just adapted my former v6 technique where the key was dynamically built (no script) through a GetField( ) process. Note the "Reset" part of it in the KeyBuilder field, and any other one.

Then, as recursion is now possible, any new created record will auto-enter the "affiliated" parent SortKey.

Which is why auto-entering it from the CurrentParent record would be even easier to build.

FirstRecord = Johnny---> Key = Johnn

SecondRecord = Mike --> Key = JohnnMike

ThirdRecord = Lee ----->Key = JohnnMikeLee

etc.

I opted for an Alpha sort here, on a long concatenation of Left(ContactName;5), so that the key could expand (and can be sorted) up to 20 levels.

Now, with a custom function, there surely would be an even easier way to build this.

As for the different directions (Parents, Children, Cousins, etc.), it's just a matter of setting the keys (level, Record ID and ParentID) so that you'd play with them to condition the relationship flaws.

Hope this is clear now.

[Ender]

Ugo, I'm facinated by this, and I feel I'm close to grokking it.

Can you clarify for me what the keys are for the relationships 'PortalDrill', 'MultiLevelsNew', and 'MultiLevels 2' (and where on the TOG they are)?

Also, should I assume the value list "DescendantRecordIDs" is made up of the RecordID field in the 'AllDescendants' TO, starting at 'MultiLevels'?

[Ugo DI LUCA]

Hi Mike,

If you decide to work with a Drilling Portal Tree, then the current Display based on a Cartesian Join isn't useful. Therefore, you may rename "Display" to "Drill".

The Drilling Portal would be controlled by the content of the gPortalKey, so I'd create a new Table Occurrence of "MultiLevel", which I'd rename to, say, "MultilevelNew".

MultiLevels 2 in the above calculation was just a typo I did when writting the calc on the board. Consider it is MultilevelNew, and rename it now to a better suitable name, say "Control".

The Drilled TO is related to the Control TO with the relationship

Control::gPortalKey = Drilled::cPortalKey

When in a record, you can get a list of :

- its chilren IDs through a list tied to the Children TO.

Record B would be considered as a children from our current Record, say Record A, if Record A ID is also Record B ParentID.

In order to eliminate our current record from this list- in case you are at level 1 of the Tree-, make sure to add to the relationship a condition where RecordID doesn't match RecordID.

- its children *and* grand childrens (decendance) IDs through a list tied to the Decendance TO.

Record B would be considered as a decendant from our current Record, say Record A, if Record A ID is part of the Record B Multikey (KeyBuilder). In order to eliminate our current record from this list, make sure to add to the relationship a condition where RecordID doesn't match RecordID.

Type of possible actions :

1) Open a Folder :

Anytime the ChildrenIDs key from the selected portal row will be *added* to the gPortalKey, the portal will expend to show its *direct* children.

2) Open all Folders in the selected Folder :

Anytime the DecendantIDs key from the selected portal row will be *added* to the gPortalKey, the portal will expend to show its direct children *and* any other decendance, tied to this ParentID.

3) Close Folder :

While there are 2 methods of opening a folder, there's only one way of closing it, and it requires that you skip from the gPortalKey any *DecendantIDs*, as only eliminating the Children IDs wouldn't work - i.e : if you had 5 levels opened and skipped the ChildrenIds from Level1 Record, you'd "close" all Level 2 records tied to Level1 Record, but its opened level 3,4 and 5 would remain open and orphans in this Tree-like view -

Closing a Folder would therefore require this loop that skip from the gPortalKey any occurrence of a temporary stored global multiline, holding all Decendance from the selected row RecordID.

The 3 actions above should be combined together in a single script, may be with a "shift-click" for the "opening all" method. A Refresh Window step might be necessary.

I'm also fascinated by how these structure are easy to develop with v7.

I'm not sure wether this loop is correctly set however, and cannot test it right now. But I'll surely look at it later in the week-end (need to work a little in the meanwhile ) to see if I can post an update with a user-friendly way of creating and displaying them. May be I'll post a step by step pdf for this.

[Anthony Nelson]

Ugo, this is pretty cool and helps with me to start in understanding this, but to put a little kink in things, my data is much more complex than this.

I should have mentioned this to begin with but what I have is a set of assemplies and subassemblies. Here comes the issue, it isn't a straight one-to-one relationship from child to parent. A child record can have multiple parents with the parents themselves not being related to each other. This happens do to the fact that some subassemblies (actually quite a few) are common parts and are used in many different assemblies.

A good example is like one I read in a book using fast food.

Hamburger

bun

burger

Cheeseburger

bun

burger

cheese

So as you can see, the burger record can't list Hamburger as a parent since that isn't completely inclusive of all the parents. I just started looking at your file and it may still work but I thought I would throw this out there so that maybe you guys could let me know if it will work or changes would need to be made.

I do appreciate all the help you guys are giving me.

[Ugo DI LUCA]

Hi,

This would work as long as you'd be using an Assembly Join Table, where each item is a separate record.

You'd use the Assembly Table to get a list of all your items in a Tree and gets either the Parent or Children records.

Another way, if the goal was to print an invoice with either the Product name and below its Components in the indented way, is to bring the components in the LineItems as well and use an alternate relationship to either see the Products, or the Products and their components.

Let us know if you need help for such a structure.

[Anthony Nelson]

Yes, I already have a link table setup since I couldn't figure out any other way to get my layout to only show an assembly once.

I'm going to play around with this and see what I can come up. Thanks again.

[Anthony Nelson]

Sorry for the break in posts but I had other projects that needed addressing.

Could you explain some of what is being done with some of the calculations in the fields? If I could understand it better, then I think it would be easier for me to implement in my current structure.

I get what it's doing, I am just having a little bit of trouble understanding how it accomplishes it. Some of that I think is do to some lack of knowledge with how filemaker does certain stuff.

Anyway, you have your keybuilder field with this calculation:

Case(Reset;If(not PatternCount(GetField("KeyBuilder");ProgressiveAffiliation);GetField("KeyBuilder")&"

[Ugo DI LUCA]

Anthony,

My answer and the file uploaded was too much inspired from the FM6 version. Take a look at this new upload (which isn't quite over but which should get you started) and if you need help, I'll kindly answer you on a private basis.

I have too much to do right now to post a "how to" but will take a look at it as soon as I can, then throw it in the Sample section.

Again, the solution posted before was too much inherited from the Multilevels.fp5 file, and FM7 really helps now.

Multilevel_new.zip