Forcing a calculation to refresh

[RWX]

A little calculation-related issue has me stumped. I have two related tables in my solution. The first is Users, the second Tasks. I have a "To Do List" view for the Users table with two portals. One shows "Open" tasks, the other "Overdue" tasks.

The relationship between the two tables is determined by a filter. The user is able to set a date range for tasks to view. This information is stored in globals in the Users table. The relationship for the "Open" tasks portal is set up as follows:

Users::UserID = Tasks::rUserID

Users::gStartDate =< Tasks::DueDate

Users::gEndDate >= Tasks::DueDate

Users::gOverdue != Tasks::cStatus

The relationship for the "Overdue" portal is simpler:

Users::UserID = Tasks::rUserID

Users::gOverdue = Tasks::cStatus

Users::gOverdue is a field that contains the word "Overdue". The user may filter his or her "Open" tasks, but ALL "Overdue" tasks should always be visible.

Tasks::cStatus is a calculated status field based on Tasks::DueDate and Tasks::CompleteDate. If DueDate >= Today, the task is "Open". If the DueDate < Today, the task is "Overdue" and if CompleteDate contains a valid date, the task is "Complete".

Since I'm using globals on the User side, I need to be able to index the Tasks side. The problem I'm having is that "Overdue" is not calculating properly since nothing occurs to refresh the calculation after it has been stored and indexed. When marking a task "Complete", Tasks::cStatus updates correctly to "Complete", but if a task simply becomes "Overdue" (ie: the user doesn't mark it complete before or on the due date), the status calculation isn't refreshed.

I'm using globals on the Users side so that multiple users can view each other's To Do lists. I could change the filter dates in Users to regular indexable fields, but that may cause problems if more than one user tries to open the same user's To Do list. There will certainly be an issue when setting filter dates.

Before I go that route, I was wondering if anyone had thoughts on refreshing the status calculation. I suppose I could run a script at login that automatically refreshes the field, but this seems rather time consuming. Another thought I had was to look into the availability of a (server-side) plugin that might let me trigger a refresh script to run every morning at 4am or something. Does such a plugin exist? But perhaps I'm missing the most obvious solution of them all???

Thanks very much for your thoughts and time!

-Rob

[comment]

What if the relationship for the "Overdue" portal would be:

Users::UserID = Tasks::cCondUserID

AND

Users::cToday > Tasks::DueDate

cCondUserID =

Case ( IsEmpty ( CompleteDate ; rUserID )

cToday (unstored) =

Get (CurrentDate)

[RWX]

In a word, duh! :-)

Thanks for the help. I think that should do it.

-Rob

[RWX]

Okay, more calc refresh problems. Let's see what you think of this situation:

My client sells a "Membership" that is date sensitive. Each Membership has a start and end date. I need to flag each Membership record as either "Past" (ie: End Date < Today), "Active" (ie: Start Date =< Today and End Date >= Today), and "Future" (ie: Start Date > Today).

I could set up the calc as follows:

cPast = Case ( Membership::EndDate < Get ( CurrentDate ); 1; 0 )

However, this will cause the same problem I had earlier. With nothing to force the calculation to update/refresh, Membership status won't automatically update from day to day.

I need to relate Student records (in my Students table) to their Membership table records. My current relationships are set up as follows:

Students::StudentID = Membership::rStudentID

AND

Students::gTrueFlag = Membership::cPastFlag

Active and Future reference their respective flags. gTrueFlag is a global with a value of 1. cPastFlag is calculated and stored. This works fine, but what happens when an Active Membership should roll over and become a Past Membership? Since there is nothing to update the calculation, it won't happen, right?

Any thoughts on how to address this issue?

Thanks!

-Rob

[comment]

I am afraid I don't quite see what is your requirement here.

IIUC, each Student can have several Memberships. So let's say the relationship is:

Students::StudentID = Membership::rStudentID

which shows all Memberships of this Student.

Now, if you want to flag Memberships as Past/Active/Future, that's an "internal matter" in the Memberships table. It is not affecting the relationship.

The question is what do you want to show in the Students table. I would think the only matter of interest is the status of the latest Membership, or more precisely, the latest expiry date?

If the above relationship is sorted by date, descending, the latest membership becomes the first related record. Therefore, if you place the flag field and/or the EndDate field from Memberships on the Student layout, it will show the status and/or the expiry date of the latest membership of this Student.

You could even create a calc of:

Membership::EndDate - Get ( CurrentDate )

to provide a "count-down" to membership expiry.

Is there any other information from the Membership table that you need to pull into Students?

[RWX]

My requirement...good question! This project has been a work-in-progress for over a year, largely because no one in the company will define requirements, but I'm not complaining...

I need to flag Membership as Past, Active, and Future for several reasons. At least, I see several reasons, but I'm open to other thoughts and opinions on this matter. An "Active" Membership may be "frozen" (either for a fixed length of time or indefinitely), thus resulting in a change in expiration date. This needs to be calculated.

I'm also using these flags in several additional relationships. For example, there are levels of Membership and Membership may be upgraded. Only an Active or Future Membership may be upgraded. So, using the calculated flags helps me (in theory) quickly go to correct Membership.

I can check to see if the Active Membership relationship is valid, then upgrade the Membership. If there's no Active one, I can quickly see if a Future Membership exists. I could use scripts to run through the related records and determine whether or not an Active or Future Membership exists, but I thought the calculated flags might be more efficient.

I can also use the Active Membership relationship when freezing a Membership. If the relationship is valid and the Membership isn't frozen, allow the user to freeze it, otherwise report an error. Again, this could happen with scripts, but that seems more time-consuming and, well, less elegant. Of course, if I can't get my calculated flags working correctly, who cares about elegance, right???

I hope that sheds a little more light on my requirements. Any and all thoughts are much appreciated!

-Rob

[comment]

It looks like you expect to be able to create relationships to Students, filtered by the membership status. This is of course never going to work, unless the data is somehow duplicated in Students.

But there is no need for that: the information about membership lives in the Memberships table, so any other table that needs to use this information should link to it directly.

For example, to show all students with active memberships, you would create a portal into the Memberships table (filtered by membership status). Fields from the Students table placed in the same portal can supply the student's details.

[RWX]

Hmmm...maybe I'm not explaining this well???

Essentially I want to see if a Student has an Active and/or Future Membership on file. Past Memberships aren't really important. Just historical data.

I need to know if the Student is an Active Member when attempting to freeze his/her Membership or when selling him/her another Membership. If the student is Active, the user may freeze the Membership. If the student is Active and tries to buy another Membership, the user is prevented from entering a start date that falls before the Active expiration date.

I also need to check for Future Membership for several reasons. If the user freezes an Active Membership, I need to automatically adjust the Start Date of the Future Membership (if there is one) so that there is no overlap.

And, as I mentioned in my previous post, I need to check for Active and/or Future Membership when selling an upgrade. If there's no Membership, the upgrade may not be sold.

This information needs to be accessed from several locations. The Student record is one place. An Invoice (related to Student, of course) is another.

The only things that affect Membership Status are freezes and today's date. So long as today's date is within the Start-Expiration date range for a Membership record, the Membership is considered active. Freezes (in a separate table related to the Membership record) act to adjust the Membership expiration date. This, in turn, affects whether or not the Membership is active.

As I said before, I was hoping that I could use a calculation to determine status, but I guess that's not possible in FileMaker. So, what are my options? Run some sort of Check Membership Status script each time I access a Membership record before allowing the user to perform any actions on it? What about a daily maintenance script that evaluates each Membership record's status? That would solve all of my problems.

Thanks for the continued dialogue. You've been a great help!

-Rob

[comment]

I am not sure I follow you, esp. on the freeze issue. Let me put it this way:

Just about any information regarding the student's memberships can be DISPLAYED on the student's layout, either directly from the Memberships table, or as an UNSTORED calculation. IOW, you can see it, you can enable/prevent actions based on its content - the only thing you cannot do is link to it from another table.

Let's take as an example the status thing (I think I can understand that, at least). I gather you need to know (a) if there is an active membership and (: if there are future memberships. See if the attached demo solves the problem.

futureMemberships.fp7.zip

[RWX]

Thanks very much for putting the sample file together. Much appreciated. I see where you're going with it.

The freeze issue is still a problem for me, though. Basically Members are allowed to "freeze" their Membership for a duration of time. For example, John buys a one year Membership on June 1, 2004. His expiration date is June 1, 2005. On March 1, 2005, John freezes his Membership for three months, thus pushing out his expiration date until September 1, 2005.

John could also choose not to specify a return date, thus creating an "open freeze."

So, somehow I have to track this information too. I need to link Freeze records to Membership records and have the Membership Expiration Date adjusted based on the freeze info. I suppose I could store this information in the Membership table and update it (via a script) whenever the freeze is acted upon in some way. Every action in my solution is scripted, so it wouldn't be a stretch to do this.

Any further thoughts?

Once again, thanks very much for all your help!!!

-Rob

[RWX]

One more thing...

Membership Upgrades also effect the End Date (and Status) of the Membership record. When a student buys an upgrade, it is applied toward his/her Active (or Future) Membership. The Membership End Date is adjusted, as is the Membership Status.

Much like the freeze issue, this requires that I store all of this information in the Membership record, correct? Or I won't be able to index and relate to the End Date field...

-Rob