Newbie Planning Question

[acsulliv]

Ok, I'm new to Filemaker, and fairly new to databases in general. Here is my setup: I have a table called "Master List". This is a list of all contacts. I want to have 2 other tables, "Members" and "Leaders". Those contacts in the "Master List" that are members, I would like to dynamically appear in the "Members" table, and likewise, those contacts in the "Master List" that are leaders, I would like to dynamically appear in the "Leaders" table. Contacts can be either Members, Leaders or Both. I have yes/no radio button field in the "Master List" for both Members and Leaders. Is there any way to dynamically generate a table based on those contacts that have "yes" for being a Member, or "yes" for being a Leader? Thanks for your help.

[Jacob22x]

I'm not particularly an expert, but I would suggest that you don't actually want two other tables, since "member" and "leader" are just characteristics of each contact. What you really want is one table for contacts where member and leader are just fields you can perform a find on. I've created an example (not particularly elegant in how it works on the backend, but looks nice) of what I think you're looking for. Let me know if I'm right.

MasterList.fp7.zip

[acsulliv]

That is EXACTLY what I was looking for. Thanks so much!

Adam

[bruceR]

Another way to handle this is with a join table. Since you are new to database, it's a good concept to try to get a handle on. As Jacob said, it doesn't appear to be something you need to do at the moment - if one person can only be a Member OR Leader. But if they can be both - or perhaps they can be leaders or members of mulitple groups - or you want to track their status over time - then you need a join table.

A join table might have these fields: ContactID; Type; DateStart; DateEnd. So you could then record that Sam Spade became a Leader on 5/6/2004. Perhaps this position expired or he stepped down or whatever on 8/2/2004. Or you could add a field - leader of what? Maybe your organization has several activities that a person can lead. Again, you can record that Sam led the volleyball team or corporate outsourcing team or whatever, over a given period.

[acsulliv]

Let's say I did this join table and made Sam Spade the leader of the volleyball team. In another table, "teams", I want to browse through the various teams and view their current leader. How would I do this? I am currently viewing the members of the various teams through a portal. Would i use the same approach?

[bruceR]

Basically you would add the field TeamID to the join table I described. Then you would have a Teams table, with fields TeamID, TeamName, Sport, TeamLeaderID.

Then you would create a portal in Teams for viewing the join table records, linked by TeamID. You would have another relation, direct to People, based on TeamLeaderID = ContactID, for viewing the team leader's information. There are some additional possible variations on how you might view the team leader.

[acsulliv]

Ok, I am attaching my file so I can get some specific answers. I am using Filemaker Pro 7. There are (2) tables we are dealing with here: "Small Groups" and "Master List". I would like certain details from the "Super Fast" layout to automatically appear in the "Small Groups" layout.

More specifically:

In the "Small Groups" layout, I would like to see the Leader and Co-Leader for each group. I can currently see the members of the group via the relationship I have created between the two tables. In the "Master List", I have designated certain members "Leader" or "Co-Leader" via a check-box entry. Take for example, Tim Geisland. He is the Leader of the group "Geisland". His wife Nicole is the Co-Leader of the group "Geisland". I have checked the respected boxes for each. Is it possible for the "Small Groups" layout to dynamically show who the leader and co-leader are, depending on who is checked in the "Master List" table?

Also, I would like to carry over the "launch date", "child care proviced" and "current focus" for each small group. This information is found in the "Super Fast Leader Update" layout, for those members that have been checked "Leaders".

I'm probably making this much more difficult than necessary. It's just that I'm in the starting stages of this project, and the project is expected to grow considerably over the next year or two. I want to make as much of this database as possible dynamic, so that I don't have to change the same information in multiple places. So please feel free to suggest a total overhaul if that's what I need at this point. Thanks.

smallgroups.fp7.zip

[bruceR]

Yes, it does take some time to catch on to this whole relationship business. Much of the data you mention most likely needs to be generalized and needs to be part of a separate table. Rather than have fields SmallGroup, LargeGroup, JustRightGroup, it is better to have this information in the group table, in a field groupType. Then you can add or edit group types much more easiliy. I've downloaded the file, maybe I'll modify it and upload.

[Fenton]

To show the Leaders in Small Groups you need a relationship from the word "Leader" (Groups) to the checkbox field in Master List. It should be Unstored, as it's only for this purpose, and only going in one direction. Same for "Co-Leader".

The Leader checkbox (Master) really should be a radio button not a checkbox. Unless someone can be both Leader and Co-Leader (and you want to show that).

It is also possible that you could check off 2 Leaders for the same Group. In which case you'd have to show them in a portal in Groups (same relationship). You may want a validation for only 1 Leader per group. I added that to the Leaders field; you can remove it if not wanted.

Some of the data in Leaders was incorrect. It had some "Yes" and "No"s in there. Be careful when assigning checkbox values lists, or you can end up with data you can no longer normally see, but which is there; it was screwing up the self-relationship for the validation. Put a field on a Developer-only layout, with no value list, put your cursor in and use Insert from Index (Cmd or Ctrl-I) to see all unique values in the field.

[At that point I'd say you'd want to use real auto-entered IDs instead of names, for relationships especially. If you are going to expand this solution and make it a serious database you need to learn to use IDs.]

It's a little awkward to Find just Leaders; a Find on "Leader" will find "Co-Leader" also. Perhaps that's a feature, not a problem. If you want just Leader, then an exact Find, =="Leader", would work.

"Are you currently leading a small group" may be redudant. You already have the Leaders choice. Why is this also needed? It is not a good idea to ask the same question twice; you may end up with incomplete or conflicting data, get misleading Finds (empty in one case, answered in another, one on one layout, one on another layout).

Finds and Sorts must happen in the table where their field(s) are. So you either need to switch to the correct layout(s) in the script, before you perform the Find, or switch off visibility in the Scripts menu and use a button on the layout to run them instead.

Fields which are about the Group (launch date, child care, etc.) belong in the Group table, not in the Master table. I didn't move them. If you want to show them on a person's layout, you'd use the Small Group relationship. But they really belong to and should be viewed in the Groups table.

smallgroups.zip

[bruceR]

As usual Fenton has made some excellent suggestions. The attachment includes some of those suggestions, such as ID numbers.

smallgroups.zip

[Fenton]

What Bruce has done, in case you don't understand it, is to add a "join" table, GroupMembers, so that a person can be in more than one group. If this is ever possible, then that's the correct design.

The "role" (leader, co-leader, member) is chosen (and shown) there, 'cause you a person would likely have different roles if they were in 2 groups. So it is not part of their "people" data (Master table).

I prefer my method of showing the Leader and Co-leader, with a relationship from the Groups to, in this case, the GroupMembers; the data is more about a person than a group per se. Yeah, it looks funky, with the "Leader" and "Co-Leader" hard-coded key fields in Groups. But as unstored fields they use little space, and they keep data entry of the type simple, ie., only in the GroupMembers table.

Alternatively, you could have LeaderID and Co-leaderID fields in Groups, which would be a chosen ID from the people. In other words, you chose a person to be the leader, not mark a person as the leader; it's a different data entry interface. But you should not have anything to do with Leaders in the people or join tables. It's the old redundancy problem. Use one or the other, but not both.

BTW, the Co-leader field in the Master table is junk. I cannot go to sleep without saying so. Cathartic you know :-)

[bruceR]

Regarding leaders/co-leader, the attachment I provided does have a key field in Groups for these fields. But they are redundant and like Fenton says you don't wanna do that. RIght now it's a work in progress that represents a couple of different approaches. So - leaders could be identified in the People table; they could be identified in the Group table; or it could be just an attribute of the join table record. Note that in the join table method, you could eliminate Co-leader as a category. A person could either be a member or leader; but you could have multiple leaders.

[acsulliv]

Ok, I eliminated Co-leader as a category. That was a great idea. There was no purpose for that. I appreciate all of your help here. I'm sorry that I'm really new at this. I'm having a hard time deciphering the various relationships you created and what their purpose is. I understand that Groups is Group info and Master List is the member info. I'm assuming GroupMembers is the Join Table. I'm not sure what the other three are for. Also, I would like to add records to the Master List or GroupMembers? by entering them in the Portal shown on the Groups layout. I know I can do this by allowing it via the relationship, but I'm not sure which relationship needs to be altered. Thanks.

[Fenton]

I see that BruceR is not online at the moment, so I'll blunder on :-) One question which you really should answer is whether a person can EVER be a member of more than one group. If "No," then the join file is unnecessary. Otherwise it is (and is a good learning tool as well). We'll assume it is, so we'll use Bruce's file.

His already has "allow creation" where it should, in the "Groups for this person" portal's relationship. This is for adding a person to a group. It is a portal into the GroupsMembers file. You first choose the Group, then the date joined (could auto-enter today's date, but that might not be correct), then the "type" of membership, if necessary (auto-enters "Member"). This adds a record in GroupMembers, with GroupID and PersonID.

People (Master) you just add with New Record (needs a button). You could also Import from a text list; but be sure to check "Perform auto-enter options while importing," so they get their auto-entered serial ID (ContactID).

The other relationships; this is a good tutorial on Table Occurrences (with their relationships) in version 7. First, the global relationship. I think I'll draw a picture (save the image to read the text; it got squashed):

[bruceR]

" I'm sorry that I'm really new at this. "

No need to apologize for that. What really helps is being willing to listen, and hopefully catching on at a reasonable rate, and communicating clearly. You're doing all of that. Many here, including Fenton and I, have been at this a very long time and recognize that there is quite a lot to learn.

[acsulliv]

Ok, I'm finally getting my head around all of this stuff. A huge thanks for the picture. That has saved me a lot of pressing shift-cmd-D. The join table threw me for a loop for a while. But I think I understand it now. For right now, people can't be in multipe groups. But they may be allowed to in the future, so it's awesome that I can put that in place now. I just need to adjust my logic to take that join table into account.

So as of right now, I create a new record in the Master list, and then assign that person to as many groups as I want - which creates the necessary records in the GroupMembers join table. Right? Is there anyway to add new members in the "member" portal in the Groups layout that would create records in BOTH the GroupMembers table AND the Master List?

Also, I would like to know how many members are in each group. I've tried messing around with the count function, but to no avail. As I browse through the Group layout, I would like a field that counts the members for that particular group. I would also like to reference this same field in the Master List under the "View members of any group" portal (which is awesome by the way).

Thanks Bruce and Fenton for all the help. I really feel like I'm learning a lot here.

[bruceR]

Yes. But would it be a good idea?

You can turn on "allow creation of related records" in that relationship. But this brings up the basic question of what is a valid new contact record? If you do it as you suggest you don't have access to phone number fields, etc. In general it is better to set things up so that you MUST complete a contact record before the contact can be linked to anything else; and it is better to do this on a layout where you can edit a complete record. But this may not be necessary for you, and it is possible to do it the way you want.

There is yet another issue though - when creating a NEW contact record, no problem. But what about trying to enter an existing contact into the portal you asked for? How do you do that? You can't just type in their name - that creates a NEW record by that name. This can be done, but for the moment I leave it to you to puzzle out how.

[acsulliv]

Would it be a good idea - NO. Tomorrow I'm going to receive lists of members for each group. I just thought it would be easy to enter them all into the portal as a group. Which it would, but it wouldn't be healthy for the database as a whole, or for the completeness of the individual records.

Let me take a stab at that other issue though, just for the fun of it. I couldn't enter an existing contact into the portal, because it would create a duplicate record, right? So I would add contactID to the portal as a pop-up menu (which could be overwritten with different data). For existing contacts, I would select their contactID, which would then retrieve their name and other info.

Is that even close?

Also, any idea on counting the number of members (see my last post)?

[Fenton]

I agree with Bruce. You should probably enter new people in the People table. If however your data entry is often done from a list a group leader hands, ie., a bunch of people for one group, then it could be done.

I'd have a button in groups, which would create the new person record (in People), then create the new person-group record in GroupMembers (with that GroupID); so it's 2 records, one in each table, that would need to be created.

Then I'd return, and show the form for the People record, so you could continue filling it out. The "form" would likely be a popped up, sized and positioned window of the People table.

So, yes, it's possible. But there are interface layout considerations, as well as a fair amount of scripting. You have to ask yourself whether it's worth it, considering it is only an alternative to a simple (but possibly repetitive) group choice in a portal. It would need to be considerably repetitive.

One aspect that has not be discussed is how you are going to check for duplicates. Perhaps you're assuming you will know that you already have someone. But this may not always be so. Data entry people must check first to see if they have the person before creating a new one.

I've recently posted a filtered portal popup list method for doing this. I think I'll move it to the Sample files section, soon (after coffee :-)

On another note. You could move the global "View members of any group" to its own dedicated Overview layout. Or not. In any case, because it's based on a global Table Occurrence relationship it is highly portable; it can appear wherever you want (may need slight modification to the graph if moved from a People table TO).

[Fenton]

Are the lists on paper or data?

[acsulliv]

The lists are on paper.

[Fenton]

In that case, I'd just click into the GroupID in the portal and type the ID from the last time. Make it a popup list instead of a popup menu. Click twice, or click once and hit the Esc key (Mac), then type.

[acsulliv]

Great. Thanks all for the help. I am finally getting underway with things, and it is working out perfect. I would still love it if someone could address how to count items in a portal. I want to browse through the different groups (in group layout), and have a field that counts the number of members in each group. Each group can only have 12 members, and I want to be able to tell very quickly how many members the group has. Thanks.

[Fenton]

I guess with all the commotion we forgot to count. It's easy. You just find a field in the target table that you know has data (a serial ID field for example, or a Constant=1 calculation field) and count it, via the portal's relationship.

To count the members of a group, from People, it would be the GroupID relationship to GroupMembers, counting the GroupID in that same table. I don't remember the exact names of the table occurrences, but the count calculation field (in People) would look like:

Count ( GroupMembers_GroupID::GroupID )

Put it outside (not in) the portal (in People).

(P.S. You can use the Sum() function to add up number fields, but Count() is easier for what you're doing.)