Filtered value list from a self-join

[big.bobby.d]

Hi. Long time listener, first time caller...

I'm working on an art inventory database requiring nested categories.

I have a categories table set up like this, using a self-join to assign parent categories:

id

parent_id

title

etc...

This allows me to set up nested categories to any level, and I've set up a calculation field to show me the entire hierarchy for any given category, like this: cat1::cat2::cat3...

I've also set up a join table to add multiple categories to inventory records, and a portal to select from a value list of all category values.

So far so good... But I only want my users to be able to select categories with no children. In other words, only the most specific categories get attached to inventory records, as the parent categories are therefore implicit. Without validating this selection, the data can get pretty weird...

I have a calculation field in my categories table using an IsValid () function to check if a child-parent relationship exists, but this same relationship fails to generate the desired value list.

Is there another approach to this?

Thanks!

[bcooney]

If a category record does not have a parentID, then it is a parent. You could calc a flag_IsParent to 1 if isempty (parentID).

Then, the portal of category choices should only include those category records.

[big.bobby.d]

Thank you for the response!

But... er... Finding the top-level parents as you decribe is sort of basic, and perhaps my question was poorly worded.

I don't need to know if a category is a parent, but rather if a category has no children.

And I need these categories to populate a value list, not my portal.

I have a calculation field called c_terminal_category that uses "IsValid (children_categories)" to determine if a category is last in line. I've tried creating a relationship by matching this field to a global field containing a matching constant, but for some reason this is failing.

I'm tempted to create a script to perform a find and throw the values I need into a new table, and use that table to generate the value list. I could trigger the script when users exit the manage categories layout. But that really feels like a hack. Seems like there must be a more elegant way...

Thank you again.

[comment]

This is a bit tricky - requires a couple more self-joins à la "the Ugo method":

Define a calculation field cCondID (from Categories) =

Case ( IsEmpty ( ChildCategories::ParentID ) ; CategoryID )

and these two relationships (all TO's of the Categories table)

Categories::CategoryID x AllCategories::CategoryID

AllCategories::cCondID = EndCategories::CategoryID

Define your value list to use values from EndCategories, show only related starting from Categories.

[big.bobby.d]

Whoa.

I don't understand it. In fact it makes my brain feel sort of pulpy.

But it works!

If you have a second to explain the logic a little I would be grateful.

Thanks for you help!

-BBD