Jump to content

SIMPLE 3-level hierarchical valus list


CDiez

This topic is 4938 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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" )

)

)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 2 years later...

This topic is 4938 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.