Now even simpler...
GroupMember contains groupmember.personidfk and groupmember.groupidfk
Groupmember,groupidfk is related to staffgroup.groupid
So I only want the value list to display records with a valid group (any group!) in the staff group table. Now it gives me everything from groupmember regardless of whether there is a group or not.
I just can't make it work. Why oh why are rdbms fundamentals so difficult in Filemaker! (that was a rhetorical question btw)
Personally, I find that rdms fundamentals are not mroe difficult in FileMaker, but many of them are implemented in very "Non SQL" ways. Thus it takes a signficant change of "mental gears" to make the transition.
Here's a couple of links you may benefit from:
Custom Value List? (step by step description on how to set up a conditional value list)
http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list (knowledge base article on the concept)
If those don't get you going, please post back with where the process breaks down for you and we'll dig deeper with you.
Thanks for the links. I still can't see how to implement in this case though. Following is the screenshot of my relationship diagram.
The top part shows how the groups are setup, and now with a staffgroup table to identify which groups are staff groups.
I need to add records to mailLog where the mailfrom field is a person id. The list I would like to present to the user needs to be limited by the presence of a record in the table staffgroup.
When I use a value list on groupMembers2 it shows me all rows, regardless of whether they have a row in staffGroup2.
Also, I can't find a way of only displaying a single row per person, regardless of how many staff groups they belong to.
In what field in MailLog are you going to format with this conditional value list?
MailFrom or MailTo?
As you've designed above, you can format MailTo with a value list set up to list only people who are a member of at least one group that the MailFrom person is also a member of.
MailLog::MailFrom = GroupMembers 2::PersonID
GroupMembers 2::GroupID = StaffGroups 2::GroupIDfk
StaffGroups 2::GroupIDfk= GroupMembers 3::GroupID
Your value list would list values from GroupMembers 3, use only related values, starting from StaffGroups 2.
With this setup, if a given individual is a member of Group A and Group B, your value list should list all individuals who are a member of Group A or Group B.
At the moment I don't need to consider mailTo, it was just included to clarify the purpose of the transaction file (maillog), which is to record outward going mail from my organisation.
We can eliminate MailTo from this conversation entirely as it has no bearing on the selection of MailFrom.
So all we're concerned about here is the mailFrom field and how to limit the records displayed in the value list to only those belonging to a group identified as a staff group.
My value list is on groupmembers2 and it currently shows all rows from that table.
I need to present a list to the user of only those rows where there is a corresponding entry in staffGroups2, without having them select a group, because membership of ANY group in staffGroups qualifies them for selection.
OK, ok, ok...the wisdom of Phil...thanks for your guidance
Looking at this post http://forums.filemaker.com/posts/968fc9bd4a provided the answer to the first part of my problem:
Group::isStaff is selected by the user as "Yes" or "No"
MailLog::'isStaff is a global, calculated field = "Yes"
My list is on groupMembers3 using only related records, starting from group 2
So now the only remaining question... as I am only interested in person records (i.e. I don't care which group they belong to, provided that it is a staff group), how can I make the list only display a single record for each person? (because they could, in theory, belong to more than one staff group)
How have you set up this value list, one column or two? If two, what values are you specifying for column 2?
2 columns, only displaying the 2nd - person::name
Then you shouldn't see any given name listed more than once if you are only showing the 2nd column and it's the name field. This could actually be a problem should you get two people on staff with the same names.
The plot thickens...
To test what you said I added another staff group. The data now looks like the following
IT Staff yes
Office Staff yes
Effective group members (ie. summarised contents of tables above)
Staff name Group
Paul IT Staff
Beth IT Staff
Camille Office Staff
IT Staff was the first group
Guess what - I only get the members of a single group - IT Staff - listed. The latter group, office staff, do not appear on my list at all, despite having the isStaff flag set on that group.
I realised that my problem was structural: a record can only relate to a single other record, so trying to force my way through the groups table without selecting a row was causing my problem.
The solution was to relate to Group Members::isStaff in a similar way. I now get all people who belong to a staff group because I have the staff flag right there on the group members table.
group members::isStaff is defined as a lookup from groups::isStaff based on groupID. I have a relookup on a onmodified trigger in the Group maintenance layout to keep the isStaff sync'ed, provided of course that the form is used to update the record.
It all works rather swimmingly now.
Oh, and Phil you were quite correct - I only get a single occurrence of each person regardless of how many staff groups they belong to.
Thanks for your help!