Help with counting records

[msylvester]

I posted this in the Relationship forum a couple weeks back, but I think it may really belong here.

My company organizes group tours. I have a FM 5.5 database that I set up in 2001 and it has always had some deficiencies. Now I am re-writing it in FM9. I'm doing pretty well, but one thing has me totally stumped.

I have a table that lists all of our groups (Groups), then a table that lists all of the clients in each group (Clients). These tables are related by the field "Group Name." The Clients table has a field for noting each individual client's account status (Active, Canceled, or Incomplete). I have no trouble getting a count of the total number of records for each group (which appears then on the Group layout), but I cannot figure out how to get a count of all of the Canceled accounts for a Group, or all of only the Active accounts in each group, etc. I need this information to update dynamically as the records are added or modified, if possible.

This would seem to be a simple matter of a calculation field, but I can see no way to do this.

Any help would be very welcome!

Thanks,

Michael

[Fitch]

I recently posted two examples of how you might approach this.

[msylvester]

Thank you, Fitch. This looks very promising.

Using your example file, since I would have different "college courses" that I would want to find out how many freshmen, sophomores, etc are in each course, would I use a second relationship (courses) added to each of the college level tables?

Are these "self join" relationships? Because I only find one table defined in your example (college) and yet you have that table related to four other tables in the Relationship map.

Thanks for explaining this to a newbie!

Michael

[Matthew F]

I cannot figure out how to get a count of all of the Canceled accounts for a Group, or all of only the Active accounts in each group, etc

Create two calculated fields in the 'Clients' table. Have them return '1' if the status field reads, 'canceled' or 'active', respectively. For example create a calculated field called 'canceledNum':

If ( status = "canceled" ; 1 ; 0 )

Then create two summary field called 'canceledSum' or 'activeSum' on the 'Clients' table that display the total of 'canceledNum' and 'activeNum', respectively. When you display

canceledSum on the Groups layout it will give the total number of cancelled clients in that group.

[msylvester]

mfero, you are the man!

I had tried this approach and it did not work, since I had selected "running total" and I also I had not tried putting the Clients field on the Group layout - since I could not get it to work on the Clients layout using the running total. Putting it on the Group layout and deselecting "running total" works perfectly (so far!)

Thank you, thank you, thank you! I have labored over this for about 10 days (off and on) and it had caused progress on the project to come to a screeching halt!

I am in your debt.

Michael

[bcooney]

Not to rain on your parade, but don't you need a third table, a join table btw Groups and Clients: "Membership"? You do if a client can (over time, perhaps) be a member of more than one group.

[Fitch]

Did you look at the 2nd example file? It shows the solution that Matthew suggested. But you need to be aware, as I pointed out in that post, it relies on the found set.

Barbara also makes a good point.

[msylvester]

Barbara,

In our particular situation, a client would almost never be a member of two (or more) groups. First of all, we start with a "fresh" database each year, and secondly, even if a client were registered twice, it would be with two different groups and each person registering gets a unique account number each time and it is the account number that identifies each person. If one had a different situation, I can see what you mean, but that would not happen in our database. But I will watch that as we test it and use it, just to be certain.

I do appreciate the comment though, because since I am just learning FM, it gave me cause to understand this better. To be honest, I'm not yet real clear on what a join table does exactly. I understand what it is, not so well what it does. That is for another time, though.

Thank you all for you help.

Michael

[bcooney]

A join table simply resolves a many-to-many relationship by storing combinations. Your system with Clients and Groups often needs a join table. A client can be in many groups (if not at once, over time). A Group can have many clients.

So, how to keep track of the combinations-a join table, "Membership." Join tables are sometimes named for each of their parent tables, ClientGroup would also work.

In the join table you would have an unique ID field for the record itself, a ClientID, a GroupID, and any other data fields that pertain to this particular combo. For example, dates of membership, status of membership (!), payment info...

I hate throwing out data (you mention starting fresh each year). By using a join table, you'll be able to retain all history of a client's membership. You can easily filter/find this year's data in the Membership table.

Also, don't use any meaningful numbers or text for your relationship matches. You mention account numbers. How are these numbers created? I would still have an underlying meaningless serial number to identify each client (and group!).

hth,

Barbara

[msylvester]

Thank you, Barbara, for explaining a Join Table. I see how that can be useful. I appreciate your sentiment about throwing out data. Actually we do not throw it out, we archive it in case we need to go back and look at it at a later date.

The way our business operates - we arrange concert tours for choirs (mostly) - we rarely have groups repeat from year to year. Usually it will be 2-3 years or more before they travel again. And when they do, the members will be different (mostly) and the tour will be different and options will be different. So keeping all of those records in our database from year to year just clogs things up and makes dealing with the data more cumbersome. Even when one year ends and the new one begins, if I am slow to get the database archived, my employees start gently reminding me to get it done, because it makes their use of the database a bit more awkward.

Account numbers are assigned by FM at record creation. They are generated starting at a certain number and incremented by 1 for each subsequent record and they must be unique. That number is used to create relationships in many cases. I think that satisfies your suggestion of a unique serial number. Do you see any issues that I do not? I think using a unique number for each group sounds like a good idea. Of course, each person has a unique number, but groups are unique only in having different "group names" (which we create - we use the last name of the group leader and some other descriptive terms) and we did have a situation a few years ago where we had two groups with similar names. It created problems in finds until we renamed one of them. That is an unusual problem though and we know now how to avoid it. Still, a unique number for each group would be useful.

Thanks again for explaining join tables. These forums are really great and the people that visit them are helpful and considerate!

Michael

[bcooney]

You're welcome.

Sounds like you're good. Definitely have a unique serial for a group -- don't relate by the name. Why burden yourself policing the name's uniqueness, when a serial number takes care of it. Then, you can rename groups without worry.