Filter the records displayed in a Value List

[Normand]

How can I display in a Value List only the records that correspond to a certain value (ex: active) instead of displaying all the records.

Here's my scenario:

A field named "coach_name" in table "Players" displays a drop-down list showing all coach names coming from table named "Coaches". This works fine for the moment. Now, I would like this Value List to display only the records for which field "Status" in table "Coach" would be equal to "active", so that it only lists active coaches, not all of them.

I have a one-to-many relationship established between table Coaches and table Players using field Coachname.

How can I accomplish this ?

Thanks.

[jteich]

You need an additionell relation which shows only the active coaches.

Create a calculation const_active = "active" and connect it with a new relation to coaches::status.

Now you define the value list to filter the values using this relation.

-jens

[ThatOneGuy]

Hi Normand:

How can I display in a Value List only the records that correspond to a certain value (ex: active) instead of displaying all the records.... How can I accomplish this?

Here's one way. Attached you'll find a sample file with the barest of minimums to illustrate the techniques. Dig around "under the hood" and you'll see how it operates. Take a look at the Table Occurrence Graph and how each of the relationships are defined, then check out "Define Value Lists" to see how we can drive the value lists.

The crux of it is it's use of "constants" on the parent side of relationships. These constant fields are globals, so if you're modifying an existing system that's currently "served up," you'll need to take the files down, create and populate the globals, then fire it up again on the server ... so that the values in the global fields "stick." Alternatively, these fields could be defined as Auto-Enter, non-global fields.

Let us know whether it makes sense and whether it's what you had in mind. :goodluck:

CoachesPlayers_01.fp7.zip

[Normand]

Thanks jen. I followed your indications and it now seems to work ok. What I did: added a self-joined relation in my Coaches table specifying that Coaches:status = Coaches:static_active (calculated field containing "Active"), and used this relation in my CoachesValueList to filter the records (include only related values starting from:). Is this what you had in mind - the right way to do it?

The only weird thing is that I had to change the calculated static_active field to "Unstored". Without doing this, I could not get the drop-down list to display a list of coaches when on an empty field (new record for example) - the list would not pop-up. If I went to records already populated with a coach name in the field or entered manually a coach name in the field, then the drop-down would display properly. Funny enough (and I don't know why), changing the storage option of the calculated field to "unstored" fixed that !! Any explanation ?

Thanks again

[Normand]

I can't believe the efforts you guys put in when replying to our posts ! Really amazing. Thanks very much ThatOneGuy for the sample db. I am going through it presently and will let you know how I'm doing.

Before receiving your post, I was able to get it working following jteich's advice - I added a self-joined relationship in the Coaches table for status = "Active" (see details in my previous post). Now your solution does not use a self-joined "technique". What is the difference / advantage.

[jteich]

Looks like you defined the self join the wrong way. It should lead *from* the constant 'active' *to* the status field.

-jens

[ThatOneGuy]

Hey Normand!

Glad you've found the sample interesting.

... Now your solution does not use a self-joined "technique". What is the difference / advantage.

That's a good question, and it may spark a worthwhile debate. As we all learn, there's usually more than one way to accomplish something in FileMaker. Choosing one technique over another is often a balancing decision we each must make. In this instance, some of the factors may be ...

• Number of Table Occurrences: With the "constant" filter residing in the parent table (Players, in this example), we only need one TO to obtain the value list of Active Coaches. Conversely, a self-join model would require two TOs in most situations* ... one to establish our "starter set" of All Coaches, then a subsequent occurrence to winnow down the list to Active Coaches ... but this should prompt us to think about the ...

• Number of Fields: A more contentious issue perhaps is field count. There's no denying the model I submitted requires more fields in each parent table from which we may want to have a filtered relationship/value list. Yet, the alternative of placing the necessary "constant" fields in the child table leads us back into the consideration above regarding Table Occurrences ... and that seems to play into ...

• Skill Level: Please know I drew no conclusions about your coding skills, but I submitted a model intended to illustrate a technique that did not require any readers to already have self-join concepts in their toolbelts. Thinking relationally is hard enough, and I believe most of us absorb the garden variety parent-child relationship before we pick up the nuances of self-joins. In that same spirit, it is arguably easier to follow how related value lists can be defined when self-joins are not involved. Indeed, this feature alone spurs a lot of questions for many users, such as (a) including only related values starting from a certain table occurrence and its accompanying concept of context, (: displaying values from Field2 which may or may not reference the same TO as Field1, and © determining the display and sort options for Field1 or Field2, which are thankfully more intuitive than the foregoing aspects. Finally, I'm optimistic that readers who have developed their relational chops will see through the example a way to alter or refine the model and satisfy the demands of their situations. I think you're in that camp, Normand!

So, what does all this mean? At worst, I suppose it's one of those "six-versus-half-a-dozen" things ... "6 : 12/2" ... At best, it's good to think about these things, no?

Keep us posted!

* Hey, can't we just create a solitary, self-join TO to our base Coaches occurrence, then use it for our related value list in all circumstances? Maybe, but will it function properly if context is awry? If not, do we not then have to create additional relationships to establish context, then perhaps define more value lists? Hmmm....