Welcome to the forum, and to FileMaker! Yes, this is imminently doable. What you need is a value list that is set to "include only related values." A good place to start your reading would be the FileMaker Help section on value lists. (It's a common and basic technique, so the usual textbooks will cover it in depth as well.) Let us know if you still have specific questions, or thorny trouble-shooting issues, after experimenting with the guidance offered there (although my guess is that you'll be up and running like a pro, re related value lists at least, based on the help at that link ;-).
Mark Scott wrote:
Yes, this is imminently doable […]
Good one …
I know value list and I studied File Maker Training series but when I apply their study, the value list is coming as empty.
I create a Person table and layout. Some person status is "Educator" others "Member". I want to create a value list consist of only "Educator" persons.
to do this; I created a new table name is z_RESOURCES and a calculation field Category = "Educator" as GLOBAL after that did relationship between Persons (z_resources_person) and z_resources tables.
And create a value list as follows;
it does not work, value list is empty.
Thanks for the additional detail (wherein the devil always lies). I think we'll still need a bit more, in order to correctly understand your scenario.
First, what is the predicate (i.e., matching fields) for the relationship btwn z_Resources and z_resources_Person? It's an equijoin ("="), correct?
And is the layout from which you wish to access this value list based on (i.e., "shows records from") the z_Resources table occurence?
A screenshot of the relevant part of the relationship diagram would help, along with the Edit Relationship dialog; alternatively, perhaps you could post the database itself (sans real and/or sensitive data, of course), for someone here to look at.
BTW, although (once working ;-) the value list will display both First and Last (names), it's the value from First that will actually populate a field when you use the value list in, say, a Drop-down List or Popup Menu control. The Last value will display in the value list, but not get inserted into the field. Just so you know.
I created a quick and dirty mockup for you to tear apart and see how the dynamic value list technique works.
I stuck with your naming convention (pared down to the bare essentials):
- 2 tables "z_Resources" [with a "Category" global calc field, set to equal "Educator," and another "Person" field set to receive the value chosen from the value list] and "z_resources_Person" [with only "Category," "First," and "Last" fields] . . . no key fields (to keep the demo simple!)
- a value list, defined identically to yours
- six records in the person table (2 of whom are "Educators").
In the layout based on the z_Rsources table, I added the Person field, formatted as a Drop-down list with your value list. As you can see, the value list correctly shows the 2 educators by first and last name.
You can also see the problem I aluded to above, namely, that both first and last name display in the value list, but only First is saved to the field. You can display both first and last in the field control by changing the field control to a Popup Menu (also shown on the layout), but that doesn't really solve the problem (if, in fact, it is a problem for you) that only the first name is actually stored. (Switch into table view to confirm that.)
You'll probable, therefore, want to rethink your value list to use a concatenated calc in the person table, of the first and last name. (There are other approaches which may be more appropriate, such as inserting the pk value, but displaying the names.) I'll let you play with all that. At least here you can see how to make the value list itself work, and figure out what you might be doing differently to cause it not to work for you.
Best of luck playing around with this.
Thanks for your help. I think I have done all thing as you show but there is different issue. I confused so much. I sent my orginal project (This portion). Only I want to fill Trainer field with Status field condition (Green sign) records.
I try to do as in Filemaker Training Series (Example 4.3.3). If there is another method, It could be also.
Workout.fmp12.zip 126.2 K
Thanks for sharing your file; it's apparent that a lot of work has so far gone into it and looks quite nice.
The problem was exactly as I expected! The relationship on which the value list was based was from the context of the "z_Resources" table occurence, but your layout was not based on that TO. It was based on the ""Person" TO. In fact, the "Person" TO was not connected to anything in the graph.
Thus, from its isolated context, "Person" couldn't see the relationship, and therefore couldn't resolve it to compile the value list.
I copied your "StatusPerson" field (global, calc = "educator") to the "Person" table (and renamed it "key_EDUCATOR"), created a relationship from that to "z_resources_PERSON::Status", and updated the "Egitmen" value list definition accordingly to "include only related values starting from: Person."
(I doubt you even need that old "z_Resouces" table now; it appears to serve no purpose. If so, I'd get rid of it now, because a year from now, you'll see it there and won't be able to remember what it was there for.)
Lastly, I changed the field control for "Trainer" on your "Person" layout to a popup menu. Regardless of what field control type you use, it's the trainer's pk value that will actually get stored in the "Trainer" field, but when you exit the field you want the trainer's name, not pk, to display on the layout. A popup menu will do that for you, whereas a drop-down list will display the actual, stored pk value. Compare:
The takehome lesson is that a conditional value list (one showing only "related" values) is always evaluated from the context of a particular table occurence. In order to use that value list on a layout, the layout must be based on that table occurence (or be connected in such a way as to be able to "see" and resolve the relationship).
Oh, one more thing: now you can create another relationship from the "Trainer" field (which is really a foreign key field storing the trainer's pk) to "PersonID_pk" in a new instance (TO) of the "Person" table, and use that to display additional detail about a person's trainer, if you wish.
P.S. Adjusted file attached.
Workout copy.fmp12.zip 152.7 K
Everything ok, thanks a lot for your replay.