I had an interesting design issue and wanted to ask about ways of solving it.
The setup:
a popup menu
with values from a Value list (set to include all values from a dedicated Values table).
the table has simple values [A, B, C, D, E]
The change:
depending on the state of a checkbox (let's call it field "X") it was desired that the popup menu should include D or E, but never both. In other words, the popup should either show [A, B, C, D] when X="" or [A, B, C, E] when X="foobar".
The hard way
Unfortunately you can't Filter a Value List directly.
The "proper" way to solve this would be to change the value list to pull values from a relationship, add a relationship between the two tables, and add some sort of filtering between the two (perhaps a calculated field saying Case(X="foobar" and Name="D", ""; 1)
I don't like this design as it adds a bunch of complexity.
Use a Portal and a Popover
Since you can filter a portal, another design would be to use a portal, and add a filter to the portal.
Since portals are big, to save space on the layout, the portal could be hidden using a Popover button, like this:
Add two fields in the value list table: one that contains the flag that dictates which values to show; one that is a calculation that, depending on the flag value, shows the list value or not. The value list must now be based on the calculation field.
This solution works because blank values are removed from the value list. No relationship needed except any needed to set the flag field.
Remember that the flag field cannot be a global field because the calculation field must be indexable.
I don't think this works, because the value of this flag field would need to depend on which record I'm viewing in the main table, and there's no way for the value list table to know which record I'm on in the other table.
One limitation is this would work only if the filter has the same # of results, since a button bar always has a fixed number of buttons. I suppose you could have buttons which have no label and do nothing when clicked.
It's too bad we have to go to all this effort; it would be really nice if FileMaker had the concept of a relationship that included (A) a Query, (B) a Sort, and (C) a Filter.
As it stands, these 3 features are not consistent:
Component
Query
Sort
Filter
Relationships
yes
yes
no
Value Lists
yes*
yes*
no
Portals
yes*
yes*
yes**
Value lists and Portals rely on the Query and Sort from a pre-existing relationship, and can not override the Query. However, they can override the Sort (but only partially : while a Portal can do an arbitrary multi-field sort, a Value List can only re-sort to English or another language).
Only Portals allow you to add an filter on top of the relationship.
@bdbd I'd love to get your method working, but I tried several variations and they all failed: if the filtering logic resides in the Values table, but depends on data in Table 1, then you are unable to have an indexed field in the Values table, and thus can't create a Value List from it.
I love these threads, as I always learn some new, interesting techniques. My reaction to Xochi’s OP is that his is an interesting solution at smaller scales; at larger scales, rather than scatter custom value lists across my layouts, I would prefer centralizing them so I can more easily see what is where.
Years ago I watched a Matt Petrowsky video about dynamic value lists, and he made a point I’ve never forgotten and indeed now underpins everything I do with value lists, and that’s that a user can only ever select on any one pop-up/drop-down menu connected to a value list at once, and an implication of this is that value lists can be dynamically defined at the point the user clicks on the pop-up/drop-down menu.
In the video he discusses how a user-defined, table-based value list management system can easily be added to any new app, and that this made it really easy to manage things like 1) dynamically diminishing value lists; 2) language localization, 3) hierarchical value lists that can be aggregated “up” into any parent value lists that might exist, and 4) custom sorting value lists - an ongoing limitation is that FileMaker makes native dynamic (i.e. field-based) value lists sort in such a way that gives you this:
Using leading byte-order-marks in the table-based value list values allows up to 100 custom-sorted values in a value list, although in practice my lists are never this long;
If I had to do this from scratch every time I started a new project, it would be a pain. But all it requires is a single, dedicated, administrator-level table containing value list names and individual value list items (and if appropriate, any parent value list record ID) and a global field in the interface table/layout to contain the record IDs of whatever value list items I wanted displayed. I can and do easily copy the table from app to app, and in some cases, just link to an existing value list table to leverage those I’ve already created. In all candor, this approach makes value list creation the easiest part of setting up a new app for me.
You are attempting to remain within the same context. I suggest you use a new window to go to the Values table context.
This approach has many limitations. It works for small data sets. It sucks for large data sets. It may fire too often with onRecordLoad. It could be better to trigger via a button (that ends with going to the value list field).
Its redeeming quality, if one can say that, is the fact no relationship is required for this to work.
Another approach is to use a one record table for your value list(s). Each dynamic value list needs its own field. You then use a script to set the field to the desired values prior to displaying the value list. It could even be done using a cartesian relationship to avoid using a separate window (see my previous posts in this discussion).
I actually use this strategy from time to time. Useful even for large value lists.
There is a limitation, and it's potentially a deal breaker: it works well in single-user environments, not so well in multi-user environments. The same can be said of my other suggestion. Using relationships with global fields, while more unruly, is more adapted to multi-user environments.
It's not clear to me how many values you may have in a Value List, but I have some solutions that use "portal pickers" which I prefer to do on a Card-type window (depending on placement requirements on the layout). The portal of values is scroll-able; and I add "type ahead" or "find as you type" via a global field above the portal. This method can be very handy, but requires a bit of scripting to tease out your D or E situation. And, of course, performance can be sub-optimal using "find as you type" on large record sets.
I do still use popovers for some solutions, and often with vertical button bars on them as mentioned by @Malcolm. My blather here is just to add noise to the party.
