Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Membership Expiration


doughemi
 Share

Recommended Posts

I have a table of Members and a table of Payments, related by MemberID. The Payments table contains a calculated field ExpirationDate. ExpirationDate must be calculated because the date depends on which quarter the member's dues are due, and whether the payment was a dues payment or some other sort. I have an isCurrent field in Members calculated by Max(Members::ExpirationDate) ≥ Get(CurrentDate). isCurrent cannot be stored, because it contains a reference to another table, and to an unstored calc.

 

At startup, I want to show only current members. The find script step, using isCurrent, in the startup script takes about 10-15 seconds to execute because FM needs to recalculate a series of unstored calcs in about 10,000 Member records. This was unacceptable to the user, so I added a number field CurrentStored in which isCurrent is autoentered, overwriting existing value. Now the find operation is superquick, but today I found that CurrentStored did not update at the end of the quarter (some members had failed to renew, and so were no longer current).

 

How can I quickly retrieve a correct found set of only current members?

 

--Doug

Link to comment
Share on other sites

You can't store a field that references Get(CurrentDate). Or, rather, you can but it doens't notice when the date changes at midnight. It will retain 10/19/2010 as the current date (if that's when the field was first stored, for instance) until the end of time.

 

You can however create a relationship from a global date field (let's call it g.Today) to Members::Expiration Date, like so

 

 

AnyTable.......Members

_____________________

g.Today

 

 

 

Then this script, executed from an AnyTable layout:

 

If [Count (Members::Member ID)= 0

.. Exit Script

End If

Go to Related Records [show only related; Members; using layout Members Screen]

Link to comment
Share on other sites

I'm still wrestling with this. Your suggestion is great but Members::ExpirationDate can't be indexed because it is a calculation based on another table (Payments) and so the relationship can't be made. I tried to create a date field Members::MemberExpiration . I set it up to autoenter the calculated expiration date, but it doesn't update when the calc is changed. I tried both Members::ExpirationDate and Payments::CurrentEndDate as the autoentered value.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use