Seeking membership status tracking method

[gadfly]

I'm working on a membership database. I have three primary tables: ClientList, ClientCards and ClassAttendance. Every time a new class begins, I create a record for that class, lookup clients from ClientList, enter them into the attendance roster for that class and at the same time, lookup information about the current membership status from ClientCards for display. This allows me to see current membership status for each client as they check in for the class. When a card is expired and they buy a new class card, I don't want that card's information to disappear so we can keep track of the history. I'm not sure the best way to handle having multiple card records while only displaying either the active card info or the most recent expired card info. I can think of two possible approaches:

1) Create two separate tables: CurrentClientCards & ArchivedClientCards. When clients check in for class, have the lookup draw from CurrentClientCards. If they are expired, I would create a button with script attached to "Buy a New Card." This script would then export the current client card record to ArchivedClientCards, reset all fields in the current card record to blank or their default values, and then allow me to enter in the new card information. I have no idea is this is possible, or how complicated it would be.

2) Keep all card information (active and expired) in a single table (ClientCards), but only display the information from the most recent card record for each client. Not only do I not know how to do that, I also wonder if it might create other challenges such as, How to deal with multiple records for each client. Would it be possible to allow a client to have multiple records but only allow one of them to have the Status field contain the data, "active"?

I hope this adequately explains what I'm trying to do... Any ideas, comments, suggestions? I'm open to other solutions besides these two.

TIA

[transpower]

I think a better database architecture would be to have these tables: 1) student, 2) class, 3) class date, 4) join (student-class-date). Each student would have a unique ID; each class would have a unique ID; each class date would be unique.

[gadfly]

Transpower,

I'm not sure I follow you. I don't understand why you suggest separate tables for class and class date...

Right now I've got a unique ID for each student, each card and each class (using date plus time to allow multiple classes on the same day). What I'm trying to figure out is how to deal with expiring and new cards. My system works great as long as the card ID remains the same. When I look at my layout in ClientList I want to see the most recently expired card or the active card data displayed in a portal. I can do that fine with my present configuration. What I can't figure out is how to allow each client to be linked to multiple class cards (records in ClientCard) but only display the most recent card in the portal.

Make sense?

Thanks.

[Ender]

gadfly,

I would recommend not splitting your past and current membership cards. It's easier to keep them together in one table. Just change a Status flag when a card is used up or expired. Then use a filtered relationship to the ClientCard table, to show only those that are not expired.

I think what transpower was driving at is creating a table for the Class (Course) info, that doesn't change for each date. This is helpful if you want to pull up the "Basketball" class, and see in a portal, all the Class-Dates (Sections) for that class. You may not need to track this information for your membership cards, but I have attached an ER diagram that may help.

[transpower]

"I'm not sure I follow you. I don't understand why you suggest separate tables for class and class date..."

My assumption is that you have an ongoing class, say for a semester. Is that not correct? If so, then my understanding was that you wanted to track student attendance; you would then have one record per student-class-date in the join file.

Regardless, I agree with Ender that it would be a mistake to split up your past and current membership cards; I take this to be your student ID's and these should be permanent.

[gadfly]

My assumption is that you have an ongoing class, say for a semester. Is that not correct?

Ah, I see. No, that's not correct. I should have given a more detailed description. This is for a yoga studio and the classes work on a drop-in basis so each class is a one time event. I actually use class date and time to create a unique ID for each class.

I've actually made some progress with the task I was attempting to describe and figuring it out was not that difficult (especially with some of the help I've gotten here). I've kept the current and expired cards in a single table and my portal in the ClientList table only displays one record from the ClientCards table - sorting by date allows me to only display the most recent card. That turned out to be a pretty simple solution but I now realize my problem is bigger than that and will be more of a challenge...

Here are the tables:

ClientList

ClientCards

Classes

Attendance

(I've attached a diagram to illustrate the relationships.)

The problem occurs when a client attends a class. The Name field in the Attendance portal is a Value List based on the Name field in ClientList. When I enter a name here, Attendance looks up ClientID from ClientList and CardID from ClientCards. I am then able to count how many times a particular Card has been used (from Attendance) so that when the 5 Class Card has been used 5 times the card status changes to "expired." All well and good. I can also count how many times a Client's Name appears in Attendance, allowing me to track the total number of times they have attended a class. When a Card is expired, it needs to be replaced with a new Card. Now the challenge is how to have the Attendance portal in Classes pull up the new CardID instead of the expired one? I hope the attached diagram makes this clear.

This is why I was originally thinking I might need to create a script to export expired cards to another table, reset the fields to allow me to then modify the existing record with new values. As long as each Client only has one class card with a unique ID, the counting works. If I keep the current and expired cards (records) in ClientCards, how does the Attendance portal lookup the correct card ID?

[transpower]

Curious--I go through a Hatha Yoga routine every night....

Your diagram helps. Add a field to ClientCard which concatenates the status field (active or expired) with the CardId, and then connect that with a similar concatenated field in Attendance ("Active" + CardID).

[gadfly]

Greetings fellow yogini...

I'm still stuck... I've done what you suggested but I still have a problem. I think this would work if I was using the ClientCard ID to add names to Classes. Unfortunately, the "cards" are virtual - there is no physical card and I don't want to have to pay attention to the CardID. I want to use the Name field as my primary key. Each client (Name) will have multiple records in ClientCards. Ideally, only one of them would be allowed to contain the status, "active" and the other records would all be "expired." I want to be able to enter names into Classes and have only the most recent card information looked up - whether it is active or expired.

I've attached another diagram to illustrate - this one is of the actual tables in FMPro.

Thanks again.

[transpower]

The status field for ClientCard should be calculated, so that when the number of classes is up, the status field changes to "Expired." Another field, also calculated, stores a concatenation of the status field and the CardID; this is the field which would be related to the similar field in Attendance or Classes--so when you do a lookup only records which have active status would match.

[Ender]

I don't think this will work. If the Status field is a calculation based on a relationship, it cannot be stored, and therefore cannot be used in the match side of a relationship

[gadfly]

The status field *is* a calculated field. I've also already got a concatenated field combining CardID and status. The problem is that I want to do a lookup using Name as the key. As long as I have multiple records containing the same name I don't see how it's possible to link to only one of them. I think this is possible because I use a proprietary windows program that does exactly this. Unfortunately, it's a discontinued program that is no longer supported and I don't have access to the source code to see how it was done. I'm trying to create my own version of this software using FMPro. I actually spent a few weeks trying to solve this last year -- even paid someone a hefty consultant fee to help me figure it out -- but ended up giving up in frustration. It seems like it ought to be possible but the solution escapes me.

Given the difficulties, does my idea about splitting up the class cards into two tables sound more feasible? If I have a table that only contains one Card record per name then looking up the current data is simple. The problem then becomes one of how to keep data from and maintain access to past cards once they are no longer current. Hence my question about whether or not it's possible to create a script that...

exports a single record to another database;

resets all fields in that record to their default values (or blank);

auto-generates a new, unique Card ID in the appropriate field.

I also wonder if the difficulty lies more in my inability to accurately describe what it is I want to do...

Thanks.

[Ender]

There may be trick possible with the new Lookup functions? But otherwise, I'd use a script to either update a Count of Visits number field or update a Status text field to Expired. This way you would avoid the unstored calc problems, and the relationships would be pretty straight forward. A script to change one related field like this is fairly simple, and cleaner than exporting to another table.

[gadfly]

I tried using the new lookup functions to no avail.

I don't quite follow your other suggestions. I really am a novice. Although I've been using FMPro since version 3, I have never really done much with scripting, relationships (beyond simple lookups) or complex calculations.

I've attached an archive of the files (stripped down) in case anyone wants to take a look and see what I'm trying to do. The question still to be answered is simple - alas, a solution may not be.

You will see that mister jones has three class cards, two of them expired and one active. When entering mister jones into the Classes database, how would it be possible to have the CardID of the "active" record looked up and not any of the others?

It's a stumper...

yogatables.zip

[gadfly]

I'd use a script to either update a Count of Visits number field or update a Status text field to Expired. This way you would avoid the unstored calc problems, and the relationships would be pretty straight forward. A script to change one related field like this is fairly simple, and cleaner than exporting to another table.

Transpower & Ender (and anyone else who might be reading)

I wonder if you could elaborate on your previous comments/suggestions.

I think I understand the problem with the unstored calculation but suppose the Status field were not a calculation... Are you suggesting that it's possible to do some kind of lookup that references the Name field AND the Status field?

Also, I don't understand the suggestion about the Count of visits number field. I've got the Count of visits field in place as a way to determine when a card has been used up but I don't see how that field could have anything to do with establishing a relationship between classes and cards.

I hope I'm not coming across as so dense that I'm looking for someone to do my work for me. I'm trying to learn from your suggestions but I'm pretty green, I guess. This is probably the most important aspect of my database. I can't imagine how to continue until I get this figured out. I suppose I may have to find a consultant again but I'm hesitant because the last time I hired someone I spent a lot of money and ended up exactly where I started.

Thanks again.

yogatables.zip

[Ender]

If the Count of Visits is calculated by a script and inserted into a number field instead of being an unstored calculation, it can be used as a match key in a relationship. Same with Status, but it's not necessary to script both. Let's keep Count of Visits a calculation = Count(Attendance::RecordID), and make Status a regular text field.

Now everytime a Membership card is issued, the Status for the new card is set via a script step:

#Starting from a layout based on the MembershipCard table.

Set Field [ MembershipCard::Status ; "Active" ]

Or "Active" could be auto-entered into the Status field when the record is created.

When someone shows up for class, you will have a process script that checks to see if Count of Visits > Visits Purchased.

If [ MembershipCardActive::Count of Visits > MembershipCardActive::Visits Purchased ]

#Card used up

Set Field [ MembershipCardActive::Status ; "Inactive" ]

#Notify user there are no active Membership Cards

End If

If it's possible for a Member to have more than one active Membership Card, then you may need to put some more logic into that script to loop through all active Cards until one of them can be used.

The MembershipCardActive relationship above is defined as

Member::MemberID = MembershipCard::MemberID

AND Member::gStatusActive = MembershipCard::Status

This 'complex' relationship can be created by dragging both keys across on the Table Occurance graph, or by double clicking the relationship line between the two table occurances. Notice I have added a global text field, gStatusActive, that contains the text "Active". This adds the condition to the relationship that we only want to see those MembershipCards whose Status is "Active".

So the process that goes along with the structure above, is that after finding the member, you hit a button for marking the member as having attended that day. It checks that they have credits on their membership card, then goes into the Attendance table and adds a record for that member. If there is only one active MembershipCard, then the CardID can be a lookup from MembershipCardActive.

You could also show the Count of Visits for the active Membership Card right on the Member layout by dropping on the related field 'MembershipCardActive::Count of Visits'. (Or you could show the number of visits remaining.)

[gadfly]

Wow. That was quite an elaboration. I'm going to spend the next few hours trying to work through it all. I do see a continuing problem but I'm going to attempt to implement your suggestion if for no other reason than as a learning exercise. Thank you so much for taking the time to explain that to me. It is much appreciated.

The continuing problem is that I also need to check card expiration dates in addition to number of classes allowed. For example, a Monthly Card allows someone to attend as many classes as they want in a 30 day period (I also have 3 Month, 6 Month and 1 year Cards). That's why I have been using a calculation to determine the active/expired status. The calculation not only compares the number of classes attended versus number allowed on the card, but it also needs to compare the class date to the card's expiration date. The numbered class cards also have expiration dates. A 5 Class Card must be used within 30 days -- it will expire once 5 classes are attended OR if 30 days have passed since it was purchased, regardless of how many classes were attended.

Many thanks.

[Ender]

So check the expiration date too in the script that sets the Status field.

[gadfly]

So check the expiration date too in the script that sets the Status field.

All right, I'll try that. I got sidetracked and won't be able to work on this for a day or two. I'll give you an update when I get a chance. I much appreciate your efforts and I will do my best to learn what I can from them.

Thanks.

[gadfly]

I've made some progress using your suggestions. Things don't work for me exactly as you suggested but I managed to get close on some of it.

For example, you suggested a syntax for scripting with Set Field that I am unable to replicate:

Set Field [ MembershipCard::Status ; "Active" ] and...

Set Field [ MembershipCardActive::Status ; "Inactive" ]

but I don't see how to create such a script. Scripting for me is limited to selecting items from a list of commands and none of allow the option shown above. Instead I had to specify a calculation:

Set Field [clientcards:::status; If (clientcards::ends < Get(CurrentDate) ; "expired" ; ( If (clientcards::cardcount ? clientcards::classes ; "expired" ; "active")))

I think mine works as you intended (though I still have a lot to learn about when and how I can trigger a script) but I'm a little confused that I couldn't format exactly as you suggested. The only way I can use Set Field is by specifying a calculation. Is there a way to write free form scripts? I seem to be limited to choosing options from the scripting dialog window.

Unfortunately, I haven't been able to get this next suggestion to work:

The MembershipCardActive relationship above is defined as

Member::MemberID = MembershipCard::MemberID

AND Member::gStatusActive = MembershipCard::Status

I'm a little confused about all the different relationships between the various tables. If I set up a relationship in one table, is that same relationship included in the relationships diagram for both databases (I've got each table set up as it's own database for no other reason than that's the way I originally set this up when I was working in FMPro 5)? The global filed is set up and seems to work fine and the relationship as described above exists but I still see the Attendance join file pulling up the first record in the table, regardless of status contents.

I'll keep playing around with this last part and if I continue to have no luck I'll try to post a more clear description of how I've got things configured.

Thanks.

[gadfly]

The MembershipCardActive relationship above is defined as

Member::MemberID = MembershipCard::MemberID

AND Member::gStatusActive = MembershipCard::Status

I'm ecstatic! The relationship worked but it needed to be between Attendance and MembershipCards:

Attendance::Name = MembershipCard::Name

AND Attendance::gStatusActive = MembershipCard::Status

does exactly what I want!

Thank you so much. I know that I have much to learn and the help I received here has gotten me started. My next thread will have a much easier and more general question...