Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Trying to filter a relationship based on hierarchical value list...


Stephen Wallace
 Share

Recommended Posts

Hello!

 

Here is my scenario: In my databases I have organizations that are broken down by geographical location. First by state, and then by regions within the state. I currently have a portal that displays all of the states via self-join. When you click on one of these items, another portal on the same layout displays all of the contacts within that first criteria (State). Since some of these lists can be quite long, I want to have a drop-down menu whereby I can further drill down in this second portal by regions. Of course, the regional menu list is dynamic depending on the State selected. In addition to this, I am trying to have the list also include an "All" option at the top of the regions so the user may go back at any time to view all records in the state.

 

I hope I was able to make it clear.

 

Thanks for any ideas you may have. I am using FileMaker Adv. 11 for Mac.

 

Regards,

 

Stephen

Link to comment
Share on other sites

a) You can cascade as many contextual value lists as you want. If you're using a portal interface rather than a dropdown value list, you will want to make your portal records double as buttons, and have them set a global field to the value of the portal record that you click on. The global field is then the basis for the next relationship through which your next portal shows related values only of the choices appropriate to that category.

 

Example: You pick "New York" for your state. A global field is loaded with "New York" and you have a relationship between that global field and the State field in a table of Regions (or a selfjoin, as long as the current table does in fact have both states and regions). A new portal, or a new value list, shows related records only of Regions so it would show "Long Island", "New York City", "Western New York", and other regions specific to New York, leaving off regions from other states. You can go further: picking a region could populate a global Region field and give you your next dropdown (or portal) showing, let's say, Towns within that Region.

 

b) In any such circumstance, if you want the option of "Show All", you want to utilize a calculation field in the portal / valuelist table, defined as (to use the original example)

 

State & "¶" &

"Show All"

 

Then a "Show All" button that populates your global field with "Show All" instead of one of the selectable states. When "Show All" is selected, ALL of the regions will match to that; when a state is specified, ONLY those regions of the selected state will match.

Link to comment
Share on other sites

Thanks for your reply!

 

Is there a way to have my value list be populated in the following manner:

 

Show All

-

Related Value 1

Related Value 2

Related Value 3, etc.

 

Currently I have it working properly by showing just the related values. How can I get the "Show All" text in the same list?

 

Stephen

 

Edited:

 

I now have a calculation where it shows me my list and a "Show All". The only problem now is that I want this to float to the top before everything else. I am also using a "-" which creates a separator. Ideally, I would like this...

 

Show All

-

Related Data...

Link to comment
Share on other sites

I'm curious as to exactly how you've appended "Show All" and a separator to your list of related values and caused the entire works to appear as a value list. (In your other thread I just opined that it can't be done, so teach me!)

 

Please supply details and maybe we can figure out how to make the Show All go to the top.

Link to comment
Share on other sites

AHunter,

 

It appears that I have the value list working, even though I continue to get the warning from the Value List that it won't work. However, with my sample data it is updating just fine and dynamically too. I was able to get the "Show All" and the line separator to be the first 2 things at the top of the list with the rest sorted alphabetically. Here's what I did. In the value list dialog box, there is an option to resort the field based on different languages. I am using English, but the thought occurred to me about sorting based on Unicode. Instead of using an underscore, I was able to use "(" and ")" around my Show All. Since the unicode sorts by unicode number, I looked at this list of unicode characters: http://en.wikipedia.org/wiki/List_of_Unicode_characters#See_also to see what would be sorted first. To my surprise, the "-" us sorted after the parentheses but before the alphanumerics. So my final list looks like this:

 

(Show All)

-

Values....

 

I'd like to figure out now if my value list (which seems to be working) will continue to in spite of the warning from FileMaker. I wonder why it gives you a way out by saying, "Proceed anyway?" So I am hoping that it does work.

 

Stephen

Link to comment
Share on other sites

If you're using ValueListItems () to get your values in this calculation field, the problem is that the calculation field will STORE the current results of the value list; it will not be dynamic nor will it be contextual. Once having been saved that way, it will not update when the value list itself updates.

 

But you did not paste your formula so I don't know if you're using ValueListItems or not.

Link to comment
Share on other sites

For my formula, I am just using a calc field in my Regions table:

 

"(Show All)" & ¶ &

"-" & ¶ &

region

 

"region" is a text field.

 

Is this what you're referring to?

 

Also, I added new values on the fly and everything updates as expected and is dynamic.

Link to comment
Share on other sites

Yeah, that ought to work and I have no idea why FlleMaker would be claiming that it won't. You're using chopper's solution to the value list problem... in this other thread you're discussing the exact same question.

 

These two threads did not START OUT being precisely the same question in the same thread but they have become that. We discourage asking the same question twice in different threads, so let's not post to this one any more.

Link to comment
Share on other sites

Yeah, that ought to work and I have no idea why FlleMaker would be claiming that it won't. You're using chopper's solution to the value list problem... in this other thread you're discussing the exact same question.

 

These two threads did not START OUT being precisely the same question in the same thread but they have become that. We discourage asking the same question twice in different threads, so let's not post to this one any more.

 

 

That wasn't the intention and as the problem evolved, so did the solution. Thanks!

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use