11 Replies Latest reply on Nov 27, 2010 5:27 AM by PaulSmith

    Another conditional list newbie - ho hum!

    PaulSmith

      Title

      Another conditional list newbie - ho hum!

      Post

      Greetings,

      Sorry, but I'm just a simpleton from the land of sql:

      I have a person table

      I have a groups table which includes a flag to indicate whether this is a staff group

      I have a group members table which is a junction of person/group

      Now I have an email form on which I want to select "from" based on staff.

      For the purposes of clarity this could be achieved elsewhere by making the following the source of my selection list.

      select distinct person.personemail (or if I wanted to store the key person.personid)

      from person, group, groupmembers

      where groupmembers.personidfk=person.personid

      and groupmembers.groupidfk=group.id

      and group.isStaffGroup=true

      Any ideas how I can achieve this in filemaker, I've spent 3 days trying to work it out!

      Many thanks,

      Paul

        • 1. Re: Another conditional list newbie - ho hum!
          PaulSmith

          Now even simpler...

          StaffGroup.GroupID

          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)

          • 2. Re: Another conditional list newbie - ho hum!
            philmodjunk

            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. Wink

            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.

            • 3. Re: Another conditional list newbie - ho hum!
              PaulSmith

              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.

              tables

              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.

              Thanks again,

              Paul

              • 4. Re: Another conditional list newbie - ho hum!
                philmodjunk

                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.

                • 5. Re: Another conditional list newbie - ho hum!
                  PaulSmith

                  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.

                  • 6. Re: Another conditional list newbie - ho hum!
                    PaulSmith

                    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:

                    tablesnew

                    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)

                    • 7. Re: Another conditional list newbie - ho hum!
                      philmodjunk

                      How have you set up this value list, one column or two? If two, what values are you specifying for column 2?

                      • 8. Re: Another conditional list newbie - ho hum!
                        PaulSmith

                        2 columns, only displaying the 2nd - person::name

                        • 9. Re: Another conditional list newbie - ho hum!
                          philmodjunk

                          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.

                          • 10. Re: Another conditional list newbie - ho hum!
                            PaulSmith

                            The plot thickens...

                            To test what you said I added another staff group. The data now looks like the following

                            Group records:

                                                isStaff

                            IT Staff        yes

                            Office Staff  yes

                            OutMail        No

                            Effective group members (ie. summarised contents of tables above)

                            Staff name   Group

                            Paul              IT Staff

                            Beth             IT Staff

                            Camille        Office Staff

                            Jennifer       Outmail

                            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.

                            • 11. Re: Another conditional list newbie - ho hum!
                              PaulSmith

                              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.

                              staffGroup

                              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!

                              Paul