Need help on countdown calc (newbie)

[rcorbitt]

I'm working on creating a subscription management solution for my publication, and need some help pointing me in the right direction. (Maybe this isn't even the right forum...)

I've created a table for customers, subscription offers (number of issues for $), and an order table.

In the order table I have field for the date of the first issue (dateSubBegin), the number of issues to mail (numIssues), and a date of the last issue (dateSubEnds). This assumes that the subscription is mailed each month, without stopping.

I need a calcuation that will keep count of the number of issues remaining on the subscription each month. Each issue will publish on the 15th of the month, so I suppose the Get(Current Date) could be the trigger ...

But most importantly, I need to be able to suspend the subscription should the customer be out of town for an extended period.

I think I'll need to add a status field to the customer table that will reflect "active" or "suspend" so that the calc will know to subtract 1 or zero from the total remaining. (Could this value also be changed to "expired" when the number of subscriptions remaining equals zero?)

Am I on the right track? Any help would be greatly, greatly appreciated. Being a newbie is tough. You know it can be done, but don't know where to look or even how to ask for help!

Thanks!

Rick

Edited October 2, 2007 by Guest

[Delta Tango]

Hi this sounds workable, however I personally like to work with related records.

I would create a table called order_issues (or whatever) that would be 1 record for each issue sent out. Then I would link them based on the order number to the orders table.

That way, every time you issue a subscription, you can create a record in this child table.

What this means is you can use the Count(field...) calculation to make a field called "IssuesSent" in the orders table to do something like this:

(you can count any field that is not empty)

Count(order_issues::date)

What this means is that if 7 issues have been issued, there will be 7 dates fields in 7 records in the related order_issues table. Then make another calculation called "isuesLeft" like: numIssues - issuesSent, which will tell you how many are left.

Then, if you want to get real fancy, you can change the issuesLeft field to be conditional by adding the status field you mentioned and changing issuesleft calculation to this:

If(

status = "active";

numIssues - issuesSent;

0

)

I attached a sample file for you to look at if this doesn't make sense.

Hope this helps!!!

let me know if you need any other help.

Example.fp7.zip

[rcorbitt]

This will do the trick.

Thanks!