Hierarchy help

[dmontano]

I have 2 tables...

Categories

Category Rollups

Category Rollups is a table the contains the following keys:

category_rollup_pk

category_fk

category_parent_fk

This allows many categories to be related to many categories. (I have a constrain that prevents duplicate associations via an additional field that has an auto-enter calc that concatenates the two fk and then selected the option that value must be unique - not sure if that is the best way to validate.)

In any event - seems to work, however...

How do I build a layout... a portal... a whatever that will display the hierarchy that clearly represents what the hierarchy is? For example, an expanding tree on computers provides that graphical feedback to a user as to how they are establishing hierarchy as they go. I am interested in any solution that can serve the same help to the user.

The hierarchy may be somewhat deep - up to 40 or 50 levels and so I have to be mindful of the horizontal stepping that would render the values "off" screen if indenting was the method of choice to display.

If I could do this through relationships - more so - instead of relying on scripts - I would prefer that. Ultimately, I want to be able to export any category record and obtain a concatenated string that would show what the categories lineage actually is - for example:

animal/dog/doberman; or

organization/legal_organization/corporation; or

billy/sally/john/sue/mike;

father/son/grandson

Any help is always appreciated - thanks.

[comment]

I see a contradiction here: if you allow a many-to-many, then any category can have multiple parents - which makes the term "lineage" rather vague. If each category can have at most one parent, then why is a join table required?

For calculating cascading lineage in a recursive one-to-many see:

http://fmforums.com/forum/showtopic.php?tid/198869/

[dmontano]

Hi Comment. I agree, the term lineage is vague and maybe I am using it improperly - but then again maybe not. The lineage I am referring to is the "allowable values" for a parent, either parent, or multiple parents.

I am reaching here for an example...

"Father/Son" needs to display hierarchical and so does "Mother/Son" even though we know it is not truly hierarchical because the Son is dependent upon BOTH parents, however I need the display to show either "lineage".

There are more tables than what I presented that might have revealed that what I am working on "allows" both a one to many recursion AND a many-to-many recursion. I did not provide those as I typically have a hard time keeping my questions brief. I have the recursions functioning, but the display of the "rollup" is really needed otherwise it seems very easy to get lost in what parent has been established for each category in the correct hierarchical rollup. What is compounding the complexity is I am generalizing my entities and therefore the categories table will hold values that apply to more than one type of "party" (person) and not another type of "party" (organization).

I have discovered through great pains of building specific tables for categorical data that it can be very confusing: is it hierarchical, aggregation, peer-to-peer and I have found (hopefully) a pattern that allows me to build tables to accomodate all three in one shot.

Why? Products were categorized in one particular arrangement - then it was decided that another categorization scheme was needed to display the product in a different fashion for a different purpose - however, we needed to keep BOTH categorizations. So, it finally became clear to me that categories are a human and highly personal way of organizing the data. For example: a "fax machine" is the child record of "office equipment". But the marketing department markets it as a child record of "communication equipment".

I hope you can see the logic somewhere inbetween the insanity. I did review the exact same link you posted before I posed my question - but I believe I have the structure (tables and relationships) functioning, now I am trying to make the process of associating the two records in the rollup more graphical to aid in that process.

[comment]

I believe the same technique could be applied to a many-to-many, though I am not sure how useful such a result would be. For example, the biblical Jacob would probably show something like:

Abraham, Sarah, Isaac, Bethuel, Milcah, Rebecca

and that's just two generations back with only 2 parents each.

To display a narrowed-down lineage, you would need to make a global condition (since the lineage is assembled in a cascade) - so such condition would apply to all records alike.

[dmontano]

Hi Comment. I looked at the link last night before you referred to it again. I looked again and rebuilt the sample file in a convention and context that I could apply to a similar classification that I am trying to eventually apply this "pattern" to - in order to try and make sense of it. It is pretty cool. I do not really understand the calculation - I think it is evaluating as such:

In case...

the parent ID exists, then use the values in the parent table occurrence directory path string

&

directory_name

&

"/" (I modified and put the directory break symbol at the end)

This is a little off topic - but this calc tool is great if I could get it to build the folders and place these "nested" folders anywhere on a network to help promote the physical structure. Furthermore, this would allow the verification that a directory string does not exceed any limits within a certain environment where UNC string length is a factor. In my case - it is a limitation in order to prevent systems from breaking due to deeply nested file.

Thanks for seeing that these may be related - and useful.

Back to my initial request - how to build a display tool that can show this in a straight forward manner to allow the creation of the hierarchy? Sorry if the answer is staring everyone else in the eyes - but I am not proficient in the display of data for user interaction.

Side question: in the calc, I moved the forward slash because a domain does not start with the forward slash, but then it leaves the forward slash at the end. Working towards a complete solution to the UNC path naming conventions I would have to account for unique patterned slashs at the beginning (//domain/server/share, etc.) or at the end (no slash) and account for different platforms.

I would think checking platforms before delivering the result - or deliver both - is one solution.? But what about the beginning string symbol oddities? For example:

"domain://servershare/directory/directory/directory/directory"

This is not verbatim - however it shows the unique symbols that a particular enterprise may have AND it shows the common directory slashes, then finally ends with NO slash.

I think I would have to allow fields for each of the unique types of "structures" (domains, servers, directories) to be hard coded for a particular environments' nomenclature into the clac?

My! How one question leads to more questions....

[comment]

how to build a display tool that can show this in a straight forward manner to allow the creation of the hierarchy?

I don't know what you mean by that. The hierarchy is created by the relationships. The display tool merely reflects the hierarchy, it plays no part in its creation. I don't see a clear statement from you regarding what exactly should the tool display.

I am afraid I didn't follow your second question either. In general, each record know who its parent is. A record also knows whether it has any children. I believe this should provide enough flexibility to calculate the separators as required.

[dmontano]

Okay. I figured out one way to resolve the various "directory type" prefixes in order to concatenate them into one string.

I have attached the directory file from the link you provided and I modified it to see how it would apply - thanks for the link.

I "guess" what I mean when I say "display tool" - perhaps a portal. I am not sure if there are other "tools" that are used to allow creation, modification, or deletion to a record when the positioning of that record in relation to another is important - a hierarchy for example.

I understand that the structure intrinsically maintains the hierarchy - however - from a user standpoint (myself included) I would need to see the portion of the tree (ideally all of it in scrollable form) in order to locate the new record in its place. I guess - I need other records to help see where another record should go.

When categorizing something we are attaching a "keyword" if you will. Then we are locating that object in some existing set of classifications or organizing it within some framework.

Imagine a doberman that needs to be placed into the classification scheme. Does it go under Animal, or Dog, or Mammal? All. Having the parent of doberman be Dog is the correct choice in this example - however, as the classification become larger - the list become longer. I can see that a record for doberman is created and then Animal becoming its parent - thereby skipping the fact that we would like it to be categorized as Dog - which would then indicate that is an Animal.

This is why I seem to always gravitate back to thinking top-down approach to classification that a user would choose from in order to arrive at the appropriate choice of what its parent should be.

For example:

Is it an Animal, Insect, or Plant? Answer: Animal

then a list appears that show allowable values for animals

etc.

Let me guess... a conditional value list?

[comment]

Perhaps a picture of what such "tool" would show might help to clarify what you mean. From what you did say, it sounds like a "hierarchical portal" technique should fit almost perfectly:

http://www.excelisys.com/web/downloads/

http://edoshin.skeletonkey.com/2008/07/hierarchies-in.html

Note, however, that both these and your example presuppose a one-to-many hierarchy. Moreover, I believe biological classification is a prime example of a rigid hierarchy - for which a recursive data model wouldn't be suitable at all.

[dmontano]

Thanks Comment. Those are both incredibly complex for my feeble mind. Both examples you provided did show screen shots of something I would like the User to see. However - as mentioned - way too complex for my level.

Ready to jump in to my incomprehensible mind?

I currently have two portals on my layout (portal showing all children; portal showing parent) in addition to the record that the User is sitting on displaying the current record name and another field for selection of the parent. Perfect. Works as advertised.

Now the data entry/building/assembly of the hierarchy is difficult: In the above arrangement - I can not see the parents parents parents parents, etc. If I make a concatenated string (using your calculation - I do see the hierarchy - however it is not easily understood without reading the string. Definitely one solution to my problem, but not graphical showing something like the indented folder method. The reason for a scrollable type view is due to how deep the nest may go and a string would run off the field bounds - or wrap.

This makes building the hierarchy tricky. At least to me. Maybe I obtain my context better in a graphical manner.

For example: building the proper sequence for where a record containing the name: "washer" is suppose to go would be easier if I saw this tree:

Bolt

-lock washer

--nut

---cotter pin

I would be able to determine that I want the washer to go right after bolt. As it stands now, I would pull down a "parent_fk" field menu and would see something like this (alpha sort):

Bolt

Cotter Pin

Lock Washer

Nut

So the positioning "hints" I am looking for are problematic in two places: in the drop down where the parent is selected AND in the string - for reasons explained above.

If the list of items being categorized is large - the drop down would be very difficult to select from, and the string almost as hard.

Creating a record results in ALL records being possible candidates to be the parent in the drop down. Having an existing hierarchy displaying from parents down would allow a one selection from a few, to expand to another selection from a few, etc. until the proper nest was found.

Am I missing something that is obvious?

[comment]

Both examples you provided did show screen shots of something I would like the User to see. However - as mentioned - way too complex for my level.

It's not an easy subject. There's not much I can do about this - perhaps you just need to give yourself more time.

So the positioning "hints" I am looking for are problematic in two places: in the drop down where the parent is selected AND in the string

I don't see a practical way to do this within a drop-down, but the lineage string shouldn't be too difficult to indent if you make the cLineage calculation =

Let ( [

tab = "	"

] ; 

Case ( 

ParentID ; 

Parent::cLineage 

& ¶ & 

Substitute ( 10^ValueCount ( Parent::cLineage ) - 1 ; "9" ; tab )  

)

& Object

)

However, this is display only, with no user interaction possible - that's why the hierarchical portals can be more useful.

[dmontano]

Comment - once again I appreciate your help. I will stop at the interface I have now until maybe at a later date when I might know how to build it.

Thanks!