Relationship that always returns list of lowest child records

[dmontano]

Is it possible to build a single relationship that always returns a list of the lowest level children in a one-to-many aggregation?

For simplicity, I have one table that has three fields: "account_type_pk", "account_type_fk_parent", and "account_type_name" fields. They relate to each other as expected and works fine. I have been able to build additional TOs and their respective relationships that show a variety of information, such as:

Show all records,

Show all children of current,

Show all children of the children,

Show all children or children of children, etc.

The same can be done going up the parent.

I understand the number of levels in either direction is dependent upon how many TOs of the base table I place on the relationship graph in either direction.

I am trying to learn and anything that returns the values I hope for helps me understand. However, wouldn't be surprised if there is a way to show any number of levels in either direction without me having to build all of these TOs - maybe not. I understand I could just do one for the parent and one for the child and GTRR on parent or child and the data displayed will "scroll up and down" if you will, but I am exploring and trying to understand. I would like to see the complete structure within the table. Seeing the structure of the hierarchy helps put things into perspective for any user.

Since I have no idea how to build something that does show "as many levels in either direction" unlike as described above, I am going to assume this the way to show parents or children going up or down. Correct me if I am wrong.

So, is it possible to show all of the lowest children in any tree within the table with one TO? Given:

TABLE: ACCOUNT TYPE

Sample values and how they are hierarchically structured:

License: Software (2 level example)

License: Driver (2 level example)

License: Profession (2 level example)

Contact Mechanisms: Electronic Address: FTP (3 level example)

Contact Mechanisms: Electronic Address: Web Site (3 level example)

Investment: Financial: Bank: Checking (4 level example)

Investment: Financial: Bank: Savings (4 level example)

These values may seem confusing to some and the comments may go in that direction. For reference, that table is maintaining "Account Type" information to be assigned to each "Account". Each "Account" must be of an "Account Type", and an "Account Type" can apply to many "Account"s.

Since choosing which "Account Type" an "Account" is must be done by choosing the lowest possible child within a particular hierarchy, a TO that would display this "query" is sought. And all the conditional value lists I have ever tried to build have landed me in the psycho bin - I am in hopes that a multi-predicate TO of some concoction can do it. If so, then I can build a value list from that - I think.

I mention "all" this because I believe my conundrum is all related. If there is a common solution to the primary question - it may result in me not having to build all of these specific relationships to gain insight into the tables data in the various ways.

Sorry so long - thanks in advanced.

[comment]

In a flexible hierarchy, it is possible that not all lowest level children are at the same level (relative to the current record). Otherwise you can (and probably should) use a fixed hierarchy, i.e. a table for each "level".

I also find it difficult to accept that "profession", "web site" and "savings" are all members of the same "account type" data domain.

[dmontano]

Is it possible to build a calculation that would return the lowest children for each hierarchy then? Perhaps this could be used in a value list?

I was able to build a calculation that displays the hierarchical position of each hierarchy within the table - don't know if that could be used to pull out a list or not?

For example, calculation returns the position as follows:

1, 2, 3, 4

1, 2, 3

1, 2, 3, 4, 5, 6

1, 2, 3, 4, 5

etc.

So in this example, I can see 4 hierarchies and the depth of each. The calculation is unstored... I believe that's where the difficulties in trying to use it as a value list come in. However, I am not sure whether the unstored calculation issue can be overcome using some other method?

[comment]

Is it possible to build a calculation that would return the lowest children for each hierarchy then?

Yes, it is possible - see for example:

http://fmforums.com/forum/showtopic.php?tid/177738/post/210146/#210146

But the calculation MUST be unstored, because it depends on other records. You could define a relationship based on the calculation's result and use the related values in a value list.

[dmontano]

Hi Comment - thanks a bunch for the link to that post and thanks Mikhail for writing it. I am having a hard time with the calculation and the custom function that Mikhail wrote. I am pasting what he posted here for reference:

From: Mikhail Edoshin

Post#210146

You can do it with a field and a custom function. The field "Lowest Descendant ID", unstored text calc:

If( IsEmpty( Child::ID ), ID,

Get Lowest Descendant ID( Count( Child::ID ) ) )

Get Lowest Descendant ID is a custom function:

Get Lowest Descendant ID( number of children )

If( number of children > 1,

Get Lowest Descendant ID( number of children - 1 ) & "¶" )

& GetNthRecord( Child::Lowest Descendant ID, number of children )

I created a field "Lowest Descendant ID" and define it unstored text calc as shown above. In the calc I replaced "Child::ID" with the related child TO "primary_key" in my solution. I think that is correct.

In the Custom Function, I defined it as:

Name: Get Lowest Descendant ID

Function Parameters: number of children

Function: As shown above.

However, when I try and save the Custom Function, I get an error that "The specified Parameter cannot be found" and highlights "Child" in "Child::Lowest Descendant ID". I tried creating a Function Parameter to replace it and that ended up with another error.

Could someone help out?

[dmontano]

Can anyone help me decipher and reconstruct the Custom Function above?