Creating a History Table

[Edgar Aguilar]

What's the best way to keep enrollment history?

 

I would like to track the dates of enrollment, renewals, cancellations, re-enrollments for clients. I created an enrollment table with all these fields, but I'm having problems when I try to generate reports based on the current (active) date.

 

For example: I want export all RE-ENROLLED members to Excel along with their current enrollment date. The problem I'm facing is that it exports the first related record for enrollment date it finds on the enrollment table.

[Inky Phil]

With what little you have given us the best that I can suggest is that rather than trying to export a set of related records, try finding a set of related records and then exporting the found set.

 

Hope that that makes sense

 

Phil

[LingoJango]

How about a calc field in your client table

 

CurrentEnrollmentDate = Max ( EnrollmentTable::EnrollmentDate)

 

and if you need further info from that last entry you can use this field as a key on the left side of the relationship.

[Edgar Aguilar]

Thank you.

 

I created the field you suggested in the client table. Then I created a new table occurrence (sp?) of the history table. I linked the client table and the new TO using the CLIENT ID and the CURRENT EFFECTIVE DATE field.

 

It works.

[Edgar Aguilar]

I have another problem related to this.

 

The solution posted above worked to EXPORT the most recent record from the history table for a chosen client ( or group of clients ).

 

But,

 

I tried placing the fields from the HISTORY table on a layout based on CLIENTS table using a portal. The record displayed is accurate, it displays the most recent entry. But if I try to perform a search on any field from placed in the portal, it gives me the error

 

"This operation cannot be performed because one or more of the relationships between these tables are invalid".

 

Any ideas?

[LingoJango]

Hard to say. Check which table occurrence the fields in the portal are from, as well as which table occurrence the portal itself is linked to.