1 2 3 Previous Next 30 Replies Latest reply on May 14, 2012 3:34 PM by brianquillin

    One People Table - Need to Filter people by Label

    brianquillin

      Title

      One People Table - Need to Filter people by Label

      Post

      I have one People table.  Each Person will be assigned a "label" via a field called "label".  (currently the three labels are "Board Member", "Parent", and "Patron")

      On my People Layout (based on my People TO); I need to be able to ASSIGN people to those certain people labeled as "Board Members".  I want a pull down list showing only those people labeled as Board Members in the list.

      I have tried several things (obvious failures since I'm posting here) and I've been chasing my tail for too long.

      I have created a new TO called Board Members (based on People table) but I'm having difficulty with the proper structure of the relationship between the new BM TO and the PPL TO.

      Any help would be greatly appreciated... (thanks in advance)

        • 1. Re: One People Table - Need to Filter people by Label
          philmodjunk

          WHen you want your value list to selectively list values from a table instead of all the values, you have a conditional value list.

          See these links to learn more about how to create conditional value lists:

          Forum Tutorial: Custom Value List?

          Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

          Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

          Hierarchical Conditional Value lists: Conditional Value List Question

          Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: One People Table - Need to Filter people by Label
            GuyStevens

            I'm not entirely following on who can be assigned to whom?

            Is it so that you want to be able to assign Boardmembers to a person with any label?
            Or do you only want to assign people when the original person's label is boardmember?

            In other words. Do you go to a person who's a boardmember and start assigning people of any label?
            Or do you go to a person with any label and assign only people with the label "Board Member?"

            Anyway it seems you might need a join table.

            A table that lists:

            The ID of a person
            The ID of the person they are related to.

            This way you can make multiple relationships per person.
            The only tricky part is with the "Boardmember" label but that's not very hard to do. I just need to know exactly what it is you want to do.

            So for now the tables you would need are:

            Your Original People table with a Unique ID number field.
            A second TO of this table, You called it "Board Members"
            A table called "Join_People_BoardMembers" for clarity

            This last table has as fields:
            It's own Unique Id as a Number field with an Auto Enter Value of Serial Number
            A PeopleIdFk (Foreign Key from the people table)
            A BoardMemberIdFk (Foreign Key from the Board Members TO)

            Then create the following relationships:

            People::Id-----<Join_People_BoardMembers::PeopleIdFk
            BoardMembers::Id-----<Join_People_BoardMembers::BoardMemberIdFk

            Edit the relationship between the People and the Join table to Allow creation of records in the Join table.

            Then in a Layout based on th ePeople table create a portal based on the join table and add only one field there: Namely the BoardmemberIdFk field.

            Set this field to be a dropdown list that get's it's values from the Boardmember table. First row is Id Second Row can be the name of the Boardmember.

            This is the basic beginning. Now we only need to add the restriction for the boardmember. But Il'll wait for a little more 'splaining from you :)

            • 3. Re: One People Table - Need to Filter people by Label
              brianquillin

              Thanks for the quick response... I think that's what I'm having the most trouble with... wrapping my brain around how this is similar but somehow different to filtering a portal on the layout (which I've already setup for a different purchase, but that instance utilized two different tables, not occurences of the same table).

              Here's what I'm trying to do (or have tried to do):

              I created a field called 'boardmember' with the calculation value of:

              If (label = "Board Member" ; person_name)

              Then I've tried to create a value list (in various ways although I'm not sure which TO should be used here, People or Board Members)
              BUT I get a Filemaker Dialogue box that says it won't work because it isn't stored.

              Here are a few things I'm unsure of:

              1) the error I just described above (I've created working conditional value lists before but always from different TOs)

              2) should there be a cartisian join between People and Board Members or a standard People::ppl_ID = Board Members::ppl_ID?

              3) How can I filter a relationship (in the same way that I filter a portal)?

              I hope that once I get this solved, the next step is 'normal' to me.  That step is to then create a layout for Board Members and show a filtered portal of all the people that are assigned to them.

              • 4. Re: One People Table - Need to Filter people by Label
                philmodjunk

                If you are using the calculation you show, you don't need a TO nor any relationship to get the value list to work.

                You can just select the Use values from field option for your value list and specify that values be listed from your calculaiton field.

                But I'd change what you have here to use an ID number for people instead of their name. Linking records by name opens the door to a number of issues avoided when you use an ID number as the link.

                1. Names are not unique.
                2. Names change.
                3. Names are vulnerable to data entry error and correcting the error can break a link using the original, incorrectly entered spelling of the name...
                • 5. Re: One People Table - Need to Filter people by Label
                  GuyStevens

                  Question: Do you need to be able to link multiple people to one person?

                  • 6. Re: One People Table - Need to Filter people by Label
                    brianquillin

                    Yes, I do need to be able to link multiple people to one Board Member...

                    • 7. Re: One People Table - Need to Filter people by Label
                      brianquillin

                      Concerning the calculation, is your recommendation this:

                      If (label = "Board Member" ; ppl_ID) 

                      and then use the value list to indicate which values will be seen in the list...

                      ----

                      Also, if I'm needing to create a relationship between multiple People to a Board Member, it seems that I would need a relationship to present that information on the Board Member layout (soon to come).

                      Would establishing the value list based on my calc field suffice for tracking People to Board Members?

                      [My apologies if I'm making this more difficult than necessary].

                      Thanks for the help...

                      • 8. Re: One People Table - Need to Filter people by Label
                        philmodjunk

                        That's the idea and I'd specify the name field for the second field in the value list so that I can see their names.

                        A relationship from an added occurrence of your people table can list all people records linked to at given board member via a portal.

                        Please note though, that you may need an added table here if you need to assign multiple people to a given board member and also need to assign multiple board members to the same person...

                        • 9. Re: One People Table - Need to Filter people by Label
                          brianquillin

                          Update (although it's not right or pretty):

                          The value list is filtering correctly via the calc field; HOWEVER, I cannot modify the field (and the list is only showing the ppl_ID rather than their name).

                          Therefore, the conditional list is in effect working but not allowing me to accomplish my goal.

                          ---

                          Moving on:

                          Should I create a join table, 'Assign People' and place it between the People TO and the Board Member TO?  (What would confuse me here is that both People and Board Members are based on the same table; I've not needed to do that before).  

                          Any thoughts?

                          • 10. Re: One People Table - Need to Filter people by Label
                            GuyStevens

                            As for the Join table, have a loot at this example:

                            http://dl.dropbox.com/u/18099008/Demo_Files/People_BoardMember.fp7

                            It's the absolute basics to link people to multiple people using a join table.

                            But it does not yet restrict anything. It allows you to link anyont to anyone.

                            If you can explain a little more about the restrictions of who can be linked to whom we might e able to build further on this example.

                            • 11. Re: One People Table - Need to Filter people by Label
                              philmodjunk

                              The value list should not be used as a data entry format for the calculation field--probably why you can't modify the field. You need TWO fields--one as a number or text field that you set up with the value list format and the second field as the calculation field for providing the values needed in the value list you use with the first field.

                              To see names along with the ID numbers...

                              When you specify the details for your value list, click the "also use values from" check box and you will be able to select the name field as the second field for your value list. Then you will see  both names and ID numbers, but the ID number is what will be entered into the field.

                              You do not need a join table unless you need to select more than one board member for the same people record. So far, you've only described selecting multiple people for one board member. The Join table enables you to do both--but that's not what you have described so far...

                              • 12. Re: One People Table - Need to Filter people by Label
                                brianquillin

                                Thanks for the sample... very helpful...

                                I'm adding in a join table now, very similar to your example...

                                As far as restrictions, it would be very beneficial for me to create a People TO for each label (like Board Members in this instance).

                                • 13. Re: One People Table - Need to Filter people by Label
                                  brianquillin

                                  @Phil:

                                  You are correct, I only need to track multiple people to ONE board member.

                                  Here's what I've done:

                                  1) removed Join Table

                                  2) created a foreign key in PPL labeled 'bm_id' and connected it to the ppl_ID (primary key) in the Board Members TO.

                                  3) the value list is assigned to the 'bm_id' field on the People Layout

                                  4) the value list is created from PPL with ppl_ID as field one and the calc field as field two (showing only results from field two)...

                                  I think I've got it (although I haven't test a portal based on the Board Member layout yet, but I'm confident you guys have straightened me out)...

                                  thanks for your patience...

                                  • 14. Re: One People Table - Need to Filter people by Label
                                    philmodjunk

                                    I would use the calc field as field 1 and the name field as field 2, but maybe your calc field still returns the name where I am thinking of it returning the ID...

                                    1 2 3 Previous Next