Single Table Tree Structure Database.

Every record has a unique ID and a parent ID.

The Parent ID always matches some other records unique ID

A Parent can have multiple Children.

A child only has a single parent.

The root record has a parent ID that matches it's own ID (or zero if you like)

Given any ID I need to be able to search up through the tree to find the first parent record or parents parent record etc that has a character field = "Diamond". If not found I need to be able to return the root record.

Given the same ID I need to be able to provide a list of all the children, childrens children etc of the given record ordered by name or ID.

When displaying any record it would be great to be able to display who the parent is that is "Diamond" and list all the children as well.

Both functions would seem to require recursion but this is a little beyond me.

See whether this thread is any help

http://fmforums.com/forum/topic/58493-immediate-and-extended-families/page__p__276742#entry276742

See also:

http://fmforums.com/forum/topic/54969-recursing-down-to-root-in-a-table-representing-a-logical-tree/

http://fmforums.com/forum/topic/62995-directory-calculation/page__p__298035#entry298035

http://fmforums.com/forum/topic/72391-getting-more-out-of-filtered-portals-6-hierarchical-portal/page__hl__filtered__fromsearch__1