SIMPLE 3-level hierarchical valus list

[CDiez]

Hi,

I have reviewed hundreds of posts and tried numerous things and still can't get this to work. I could use some help in finding a simple solution.

I'm developing a property database (real estate) that has at least two areas where I will need a 3-level conditional value list.

One is for selecting the zoning district, based on the city, which is based in turn on the county. When I enter a property record, I want to be able to select the county from a drop down, then go to a city drop down that will show the cities within the selected county, then a third that will show the zoning districts in that city.

The other is the same concept, for property categories (industrial, retail, residential, etc.), then types (e.g, under retail, there is: shopping center, restaurant, etc...), then sub-types (e.g., fast-food). Not all types will have sub-types.

I would like to use the same solution for both.

I have no trouble getting the 2nd level to work, but at the 3rd level it breaks and I get inconsistent behavior.

I have zeroed in on two solutions. One is using self-joins and a single table. Here, the problem at level 3 is that I'm getting the level 3 options for the level 1 selection (I get all the zoning choices for the county, instead of for the city).

The other solution uses 3 tables. Here, the problem I'm having is inconsistent results. For example, I can pick hotels > full service > and I get my correct 3rd level choices. But when I pick retail > restaurant > ... I get NO 3rd level choices, even though they're in the table (fast food and full service).

I'm attaching a simple mock up of each solution with some of the values populated so you can see where it breaks. Perhaps someone could take a look and point me in the right direction? I would be MOST grateful for any advice.

Note: in each case, the ENTRY table is the data entry interface, which is where I want to be able to make the selections from.

Thanks in advance for any advice...

Cheers,

Carlos

3-level_test.fp7.zip

3-level_test_w_self-join.fp7.zip

[comment]

Try it this way?

Archive.zip

[Simon K]

Attached file based on self joins

Although it seems like hierarchical value lists -

What you need is for the third level to be dependant on the second level - as defined in the layout you're starting from - in this case "ENTRY"

The relationship for the third level is then directly from "ENTRY" to a TO of values...

NB - hierarchical value lists are, by definition, always context sensitive so if you plan to use them on a lot of different layouts they may lead to a proliferation of TO's and do need some planning...

HTH

Simon

3-level_test_w_self-joinSK.zip

Edited August 28, 2008 by Guest

[Simon K]

Hadn't seen that Michael had already responded

Go with Michaels

Cheers

[comment]

hierarchical value lists are, by definition, always context sensitive

Not necessarily.

IndyCondVL3.fp7.zip

[CDiez]

Gentlemen,

Thank you so much. I will need to study why what you did works, so I understand it. But for the moment, can you answer this: what would be the pros/cons of using each of the two approaches (the one with a single table and self-join, vs using 3 separate tables)?

After your repairs, they both work, and I'm not sure which approach to go with.

Thanks again,

Carlos

[comment]

It has nothing to do with building a conditional value list - you have seen that it can work in either configuration. All depends on the type of data you have.

[Simon K]

I like that a lot - very neat

Simon

[CDiez]

Thanks guys!

I decided to go with the single values table with self-join. It seems cleanest.

I do have a follow up question. In my ENTRY screen it is possible for a user to make a selection for each level, and then go back to say level 2 or 1 and make a change (change cities or counties), without changing the selection for level 3. This makes it possible to select an invalid combination of values for a record (e.g., MIAMI > MIAMI BEACH > MIAMI BEACH ZONING... then to go back and change the city to MIAMI, or the county to BROWARD).

Is there a simple way to prevent invalid combinations, based on the hierarchies established in my value list?

I'd be grateful if you could point me in the right direction...

Thanks again,

Carlos

[Vaughan]

Yes... use auto-enter calcs on the level 2 and 3 fields that check whether the entered value is part of the value list, and clear the entry if it's not.

The auto-enter calc for Level 2 field would be something like this:

Let(

$trigger = Level1 ;

FilterValues(

Level2 ;

ValueListItems( get( FileName ) ; "Level 2 List" )

)

)

The auto-enter calc for Level 3 field would be something like this:

Let(

$trigger = Level1 & Level2 ;

FilterValues(

Level3 ;

ValueListItems( get( FileName ) ; "Level 3 List" )

)

)

[comment]

Again depending on the nature of data itself, but IMHO only the last selection - zoning in this example - should be actually stored (as shown in my last file). The other levels can be displayed by placing related fields on the layout. Granted, this is more difficult to implement, esp. if re-assignment is expected.

[CDiez]

Brilliant!

Thank you mate!

BTW.. I had not seen Michael's last solution when I last posted. I see that it solves my problem, however I do need to retain the ability to search the level 1 and 2 fields (e.g., to search by continent or country, in his example). So I do need to store, not merely display, the level 1 and 2 choices.

I believe this solution gives me what I need.

You guys are great! I hope to become proficient enough eventually to be able to contribute solutions to this forum myself.

Thanks again,

Carlos

[comment]

You can search related fields, too - it's only a tad slower. Storing the upper level choices is redundant, and as such can pose a problem when an entire level is reassigned.

[Isaiah 40:31]

Thank you guys for your help. The self-join worked beautifully. However, I have tried all sorts of iterations for the 4th and 5th hierarchies, but no luck.

Can you please help with this? Thank you!