hard coded filter for value list

[ThomasD]

Hi,

I'm trying to use a hard coded filter for value lists similar to a WHERE clause in SQL.

I have one table with the main records and three "settings" fields. The user should be able to select a value for each of these settings from a value list displayed in a drop down. My problem is that I want to use values from a single table for all three but I want the value list to be filtered: one field in the table with the values is called "selectionType" and contains either the number 1, 2 or 3. For "settings 1" I would like to only display the values that have the number 1 in the field "selectionType". In SQL this would be so simple to just specify "WHERE selectionType = 1" in the SELECT statment. There must be an equally simple solution to have a hard coded filter set in FM.

The reason why I want all the values in one table is because I want to be able to export and import all values in one file.

The attached sample file displays all values for all three settings where I would like to have the values filtered.

What am I overlooking? Any help would be appriciated.

Thank you,

Thomas

valuelist-filter.zip

[ThomasD]

Ok - I think this works!

I added a field for each of the "settings" to the main table with a constant calculated value of 1, 2 and 3 respectively.

After that I added a second set of 3 additional occurrences of the table with the values of the value list. Now I was able to set up relationships for their "selectionType" to be equal to the 3 calculated fields and to specify to only show related values.

Is this a bit convoluted way the only way to solve this rather simple problem? It definitely took me way too long to figure out! :

valuelist-filter_v2.zip

[danek]

I had the EXACT same problem, and I actually came up with the same solution myself after much head-scratching... (I came on here to see if anybody had any better ideas, and found this post.)

In my case I have a table of "people" who can each take various "roles" ... there are 5 different "roles" and so I created five different instances of the "people" table... Then I realized that showing all of the "people" in each drop-down menu, instead of just whichever ones are appropriate for a particular "role" seemed silly, and tried to figure out a way to limit the value lists.

Short version of the end of the story: now I have 5 different value lists coming off of the same table, and 10 (!) occurrences of the same table on the graph, just to make this work, which is especially silly, considering that the primary functionality of this database is to track something else entirely which is contained in a single table. (The "people" thing I just put in so they wouldn't have to type in somebody's name every time they assigned somebody to something.)

The whole time I was thinking, "this is silly... there has GOT to be a better way..."

But if all you FileMaker-heads (I only dabble in the stuff) agree that there isn't, I guess I will have to live with my giant relationship graph.

[danek]

(here's a screenshot of my relationships graph, btw...)

[Fenton]

If you chose the role first, in a global field for example, then you'd just filter a single value list by that; it would require only 1 additional table occurrence (TO).

But I don't know if that's what you want/need. None of the above posts has told us how/where these multiple filtered values lists would be used on a layout(s). So we can't really say whether the global filter would work well.

If you do require 5 multiple value lists, each filtered differently using a hard-coded value (I'd use an unstored calculation field as the originating side), then I'd duplicate/move the TOs to another Table Occurrence Group (TOG).

Duplicate your original table's anchor TO, move it, then attach these filter TOs. Because of a Value List's "Include only related values starting from" [ TO choice ], these TOs do not have to be part of the main TOG in order to work.* It doesn't make for less TOs on the graph, but it gets them out of the way, less messy.

*It's different from Go To Related Record. It allows you to specify the "context" of where to start. It's more like the "Evaluate this calculation from the context of" [ TO choice ] in the Field definition for a calculation.

[Colin Greene]

Fenton,

I have a similar problem and really struggled to get hard copy value tables to work in a dynamic way, especially when a example table aray contains a 'blank' cell. The problem is they are easier to update or evaluate as the operator can see the whole truth table in one place.

Could you direct me as to how one uses TOC and would this be applicable in my case?

I have five or more fields which are all related. COMPANY defines a start group from which all other Value Lists are defined in pop_up tables. The selection cascades across the example table list. Each pop_up list must only show those values relating to previous fields [and]. I have tried making one hard copy list and separate value lists, but can never get more then two relationships to work.

To complicate things the lists in some cells are wrapped ¶ as they I need to be show this as a 'radio' or 'check box' selection. Location_2 is a check box, so this cell can contain more than one value. I noticed that when a sum of cells occurs you get duplication of a text value in the pop~_up list, which is probably in the method I am using.

I also want to clear values in fields if the previous field value is changed or cleared, but I i found some info on this.

When entering COMPANY 'ABC' LOCATION_1 shows all values in column ID_OPTIONS, unless I select a value - then it drills down. It seems FM ignors blank cells and added the sum view to the next filtered list. i.e."loc2,loc2, loc3" are seen if no value is selected in ID_OPTIONS. Having 'no value' is a still a valid selection.

I gave up and reverted to individual value lists without any relationshiop, which is not ideal as error selection now occurs.

Any help greatfully accepted

[Fenton]

There's several questions here, only some of which I understand.

1. By "TOG", I meant Table Occurrence Group (a group of related tables, as opposed to unrelated). Its use in this situation is not really a "functional" enhancement as much as "organizational" one. Using a separate TOG lets you set up the multiple TOs (Table Occurrences) and relationships needed for complex or multiple filters for value lists without having to put them in the TOG of that table's anchor TO. It keeps the graph cleaner.

What I was pointing out was that the option in a value list to "Use only related values starting from" allows you to evaluate them in the other dedicated TOG. But this is ONLY going to work for global field choices (as the originating side of the relationships). They are the only fields that are the same across all TOGs of a table. But these "multiple dependent filtered values lists" are often based on global choices (or can be).

2. Comment posted a simple but brilliant little file showing how to clear the choice fields further down the line, without needing a script. I can't find that post (in so many :-), so I'll add it again here.

3. Regarding "blanks". FileMaker uses the "index" of a field in value lists which read values from a field. Indexes contain only unique data, no duplicates, no blanks.

4. If the choices are in a checkbox format, the result is return-separated values. Which behave as a "many" key in relationships. So you'd see records matching any one of the lines.

I can't really see how you could, or would want to, use a checkbox format as a choice when you have multiple dependent filtered value lists, except as the first choice.

condVL2_clear2.fp7.zip