10 Replies Latest reply on Oct 31, 2012 9:32 AM by philmodjunk

    Member_Records:  related members

    TerryCoolidge

      Title

      Member_Records:  related members

      Post

           I have a table called Member_Records filled with member data for a small organization (200 - 500 members).  One record for each member.

           I figured out how to create a related table called Family_Members where I keep track of members who have relatives who are also members.  A particular member will show up in this Family_Members table as many times as necessary with a record for each related member.  For example, I have three records in this table since my wife, mother-in-law, and father-in-law are all members.  Someone who only has a spouse who is a member will show up with just one record, and members who are not related to any other members do not have any records in this Family_Members table.

           The main purpose of this tracking is to be able to identify who is related to a current Executive Board member.  When we have elections for new Board members we run eligibility rosters, and the related members are not eligible to be elected.  My Family_Members table is able to show who is related to a Board member.  See attached screenshot for reference.  I added highlighting to point out the records that show how certain members are related to Rob Ball, a member of the Board.  Most of the data being shown in Family_Members is being pulled from Member_Records.

           My question is about how to utilize this Family_Members info from my original Member_Records table to correctly identify ineligible members.  I thought I was going to use a GetSummary function, but that only works from within the table where I'm gathering the summary.  I can't pass that summary to another table (I don't think) because of the need to sort using a breakField.  I'm wanting to be able to check a member from Member_Records to see if any of their records in Family_Members has a value in the board_member_flag field.  Can anyone share a method for accomplishing this?  I realize this may be incredibly easy, but right now I'm just not seeing what I should do.  Most of the trouble I run into with FileMaker stems from conceptual issues, not technical/execution stuff.

           Thank you in advance.

      related_members.jpg

        • 1. Re: Member_Records:  related members
          philmodjunk

               The relationship can be your "break" field though you are correct that getsummary won't work for what you want.

               Member_Records-----<Family_Members>------Member_Records 2

               Member_Records::__pkMemberID = Family_Members::_fkMemberID
               Member_Records 2::__pkMemberID = Family_Members::_fkRelatedMemberID

               The field and table occurrence names may be slightly different in your file. See Common Forum Relationship and Field Notations Explained if this notation is not familiar to you.

               Is this what you have?

          • 2. Re: Member_Records:  related members
            TerryCoolidge

                 I only have Member_Records and Family_Members.  I do not have a Member_Records_2 table.  I was wondering if I might need to do something like that, but that didn't strike me as the right way to go.  Please explain the thought behind a Member_Records_2 table.  Right now this seems like having  a lot of redundant data just for the sake of one little task that I'm trying to accomplish, but maybe not.  I just need help understanding the concept.  Can you help me see what kind of data Member_Records_2 would/could contain and if there would be any benefit beyond what I'm trying to do with related members?  Thank you, Phil.

            • 3. Re: Member_Records:  related members
              philmodjunk

                   There is no redundant data at all Member_Records 2 is a second occurrence of the Member_Records table. You create it by selecting Member_Records n Manage | Database | Relationships and then clicking the duplicate button (2 green plus signs) to made a new occurrence. This does not create a new table, only a new reference to your existing table. I assumed you had this so that your family_members table could "pull data from" two different member_Records tables, the member and the related member.

                   With such a set of relationships, if you have a field in Member_Records that identifies board members you can set up either a filtered relationship or a filtered portal that will list the number of family members that are also board members.

              • 4. Re: Member_Records:  related members
                TerryCoolidge

                     Sorry about that.  I did look at the link you provided, but didn't read all the way to the last paragraph where you mention table occurances.  I do indeed have a second table occurance, but for some reason I decided to make a second occurance of Family_Members.  When I saw your example showing a second table (occurance) of Member_Records I did not see the connection to what I had done.  Obviously table occurances are still a subject that I need to be more solid on.

                     Can I make it work using the relationship I already have established, or should I start over and get rid of Family_Members_2 and instead set up the relationship with Member_Records_2?

                     I'm astounded that I ever get anything to work at all since I obviously know so very little.  Thank you for your patience.

                • 5. Re: Member_Records:  related members
                  philmodjunk

                       The information you need: Whether or not a related member is a board member, is not in Family_members but in Member_Records so I see no way you can get what you have to work without that second occurrence. Fortunately, adding a new occurrence of a table is easy to do and you don't have to modify any existing relationships unless you want to.

                       As I understand it, you want a list of all eligible members--which means all members who are not related to a current board member.

                       A summary field defined as the "count of" Member_Record::__pkMemberID can be used to show you which members are and are not eligible, but a calculation field that produces the same total can be used in a find to omit ineligible members.

                       Which do you prefer? a list of all members with an indicator that tells you which are eligible or ineligible? Or a list of just the eligible members?

                  • 6. Re: Member_Records:  related members
                    TerryCoolidge

                         The way our elections work, we print up a roster of all members, and I use conditional formatting to cross out ineligible members using "strikethrough."  My conditional statements use multiple criteria depending on the list I'm running (male vs. female, served recently vs. hasn't served recently, etc.).

                    • 7. Re: Member_Records:  related members
                      philmodjunk

                           What version of FileMaker do you have?

                           If you have version 11 or older, we'll need to add another occurrence of Member_Records so that we can set up a special relationship that matches only to those that are currently a board member.

                           If you have verion 12, we can set up a calcualtion field using ExecuteSQL to get the needed count and this avoids the need for another table occurrence.

                      • 8. Re: Member_Records:  related members
                        TerryCoolidge

                             Sorry for the delay in getting back to you.  I had to move on to other things.

                             I'm using FileMaker 11.  I'm going to see if I can figure out what to do based on your previous post.  Your advice combined with my limited knowledge. We'll see how that goes.  ;-)

                        • 9. Re: Member_Records:  related members
                          TerryCoolidge

                               Still needing help with this, Phil, when you have a chance.  I thought I had it, but it turned out I didn't.  One of these days I'm sure I'll have an epiphany and finally get my head wrapped around table occurances, but right now I'm still struggling to see how to use them or know when to use them.  I think I'm getting there, but I'm not there yet.

                          • 10. Re: Member_Records:  related members
                            philmodjunk

                                 You may find this tutorial on table occurrences helpful: Tutorial: What are Table Occurrences?

                                 We need a relationship from Family_Members to Member_Records that only matches to member records where the related member is also a board member. For this example, I will assume that you have a text field, Role,  that stores the text "board member" if that member is a member of the board. A number field with a check box format that enters a 1 when the check box is checked could also be used with this method.

                                 In Family_Members, define a calculation field, constBMember to have this expression:

                                 "Board Member"

                                 with Text selected as the return type.

                                 Link a new occurrence of Member_Records (Related_Board_Members) so that you have this:

                                 Member_Records----<Family_Members>----Related_Board_Members

                                 Family_Members::constBMember = Related_Board_Members::Role AND
                                 Family_Members::_fkRelatedMemberID = Related_Board_Members::__pkMemberID

                                 Now you can define a "count of" summary field, sCount to Member_Records that counts the __pkMemberID field.

                                 Now your Conditional format expression can refer to Related_Board_Members::sCount to determine if there are any related board members for a given Member_Record.