I searched a little for an answer first, but couldn't find this exact scenario. I figure it's pretty common, so I hope someone can help me.

I have a product database that I'd like to add hierarchical categories to. Right now there's a few hardcoded category field that work through conditional value lists. The structure is going through a bit of flux though and I'd like to generalize it.

The data modeling part is simple enough:

Product has a CategoryID and Category has an ID and a ParentID.

The hard part is user interfaces and reports.

I figure that I can probably generate a value list that includes dashes in front of the sub-category names by adding a Level recursive calculation field. That takes care of simulating hierarchical drop-down menus (though it's not ideal).

Reports are a problem though. I'd like to print and export a list of all products in an outline format. I can't really figure out how to do this at all. Maybe it has something to do with summary fields and parts? I really don't know.

Searching is also an issue. How would I find all products under a certain category that has subcategories?

Thanks for any help.

AFAIK, there isn't any easy way to accomplish a hierarchical search or create a hierarchical index. And you're right, the UI issues are perplexing.

Maybe you could build a UI where a Category lists its parent(s) on screen and its subcategories in a portal, each of which is a button that opens a new window with the selected category as its focus? It might also be useful to list the count of entries that use the particular category.

I know that's not "all the products under" the category, but it might be a reasonable place to start.

David

For an idea on a way to do a series of hierarchal portals look at this file by Søren Dyhr. It's a little tricky, but not too much (it's one of those "why didn't anyone think of this sooner?" kind of ideas). It exploits ValueListItems (Get(FileName), "") to produce the entire index when one "choice" field (of multiple) is blank, essentially making that choice "All". If choice fields are not blank it works hierarchical. It requires no concatenated calculation keys; it all happens in unstored calculation fields.

http://www.fmforums.com/forum/attachment.php?attid/3285/

Fenton--

What thread is that from?

David

It is not really much of a thread. It's just his post to the Sample Files section. The discussion came from another thread that both he and I were in; but that link doesn't work anymore. This is the Sample thread:

http://www.fmforums.com/forum/showtopic.php?tid/144255/

This file from New Century Data might be of help.

gtrr_multi_hop.zip