Figure payment due date...

[VanBuskirk, Patricia]

I am trying to figure out how to calculate the next payment date,
particularly for recurring bills (hosting).  It should be the same day
every 3 months.  This is how I have it, but it is not working.  (FMA9 on
WinXP):

If (
  RecurringBill = "No" and BalanceDue > 0; LastPmtDate + 30;
  RecurringBill = "Yes" and (IsEmpty ( Hosting::CancelDate ) or
Hosting::CancelDate > Get ( CurrentDate ));
    Date (
      Month ( LastPmtDate + 3 ) ;
      Day ( Hosting::StartDate ) ;
      Year (
           If (
             Month ( Get ( CurrentDate ) ) = 12;   ( Get ( CurrentDate
)+1 ); ( Get ( CurrentDate )
           )
      )
    )
)
; "")

...also, maybe I shouldn't be using the last payment date, as if they
don't pay on time, that throws off the quarterly schedule.  I am
thinking there is a better way.

Thanks!
Trish
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Tom Elliott]

Trish

I don't know if it's a typo or not but your first 'If (' should be a  
'Case ('
On 20 Jul 2008, at 9:09, VanBuskirk, Patricia wrote:

> I am trying to figure out how to calculate the next payment date,
> particularly for recurring bills (hosting).  It should be the same day
> every 3 months.  This is how I have it, but it is not working.  
> (FMA9 on
> WinXP):
>
> If (
>  RecurringBill = "No" and BalanceDue > 0; LastPmtDate + 30;
>  RecurringBill = "Yes" and (IsEmpty ( Hosting::CancelDate ) or
> Hosting::CancelDate > Get ( CurrentDate ));
>    Date (
>      Month ( LastPmtDate + 3 ) ;
>      Day ( Hosting::StartDate ) ;
>      Year (
>           If (
>             Month ( Get ( CurrentDate ) ) = 12;   ( Get ( CurrentDate
> )+1 ); ( Get ( CurrentDate )
>           )
>      )
>    )
> )
> ; "")
>
> ...also, maybe I shouldn't be using the last payment date, as if they
> don't pay on time, that throws off the quarterly schedule.  I am
> thinking there is a better way.
>
> Thanks!
> Trish
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> _______________________________________________
> FMPexperts mailing list
> FMPexperts@...
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[VanBuskirk, Patricia]

Ah yes, I did change that.  For simplicity on the email, I was
originally going to change it to a simple if statement without the first
statement, then decided to leave it as it was, but I forgot to change
that part back.  Thanks!

-----Original Message-----
From: fmpexperts-bounces@...
[mailto:fmpexperts-bounces@...] On Behalf Of Tom
Elliott
Sent: Sunday, July 20, 2008 4:13 PM
To: fmpexperts@...
Subject: Re: Figure payment due date...

Trish

I don't know if it's a typo or not but your first 'If (' should be a  
'Case ('
On 20 Jul 2008, at 9:09, VanBuskirk, Patricia wrote:

> I am trying to figure out how to calculate the next payment date,
> particularly for recurring bills (hosting).  It should be the same day
> every 3 months.  This is how I have it, but it is not working.  
> (FMA9 on
> WinXP):
>
> If (
>  RecurringBill = "No" and BalanceDue > 0; LastPmtDate + 30;
>  RecurringBill = "Yes" and (IsEmpty ( Hosting::CancelDate ) or
> Hosting::CancelDate > Get ( CurrentDate ));
>    Date (
>      Month ( LastPmtDate + 3 ) ;
>      Day ( Hosting::StartDate ) ;
>      Year (
>           If (
>             Month ( Get ( CurrentDate ) ) = 12;   ( Get ( CurrentDate
> )+1 ); ( Get ( CurrentDate )
>           )
>      )
>    )
> )
> ; "")
>
> ...also, maybe I shouldn't be using the last payment date, as if they
> don't pay on time, that throws off the quarterly schedule.  I am
> thinking there is a better way.
>
> Thanks!
> Trish
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> _______________________________________________
> FMPexperts mailing list
> FMPexperts@...
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Tom Elliott]

Trish

You don't say how it's not working which makes it a little tricky to  
debug but ...

In the 2nd Case you don't need the complicated Year calc; having added  
3  months you should just use the year of LastPmtDate and FMP will  
take care of cases where the resulting date is in the following year

In other words the result should be:

Date ( Month ( LastPmtDate + 3 ) ; Day ( Hosting::StartDate ) ; Year  
( LastPmtDate ) )

Of course that still leaves your  "throws off the quarterly schedule"  
dilemma. To solve that you need to know how many payments have been  
made (or *should* have been made) - I guess you can glean that from  
existing data (if not, you need to introduce a new field to record  
this) - so let's say you have a field (PmtCount) that gives you this  
figure, then your result for the 2nd Case would be:

Date ( Month ( LastPmtDate + 3 * PmtCount ) ; Day  
( Hosting::StartDate ) ; Year ( LastPmtDate ) )

(I think)

cheers

Tom

On 20 Jul 2008, at 9:23, VanBuskirk, Patricia wrote:

> Ah yes, I did change that.  For simplicity on the email, I was
> originally going to change it to a simple if statement without the  
> first
> statement, then decided to leave it as it was, but I forgot to change
> that part back.  Thanks!
>
> -----Original Message-----
> From: fmpexperts-bounces@...
> [mailto:fmpexperts-bounces@...] On Behalf Of Tom
> Elliott
> Sent: Sunday, July 20, 2008 4:13 PM
> To: fmpexperts@...
> Subject: Re: Figure payment due date...
>
> Trish
>
> I don't know if it's a typo or not but your first 'If (' should be a
> 'Case ('
> On 20 Jul 2008, at 9:09, VanBuskirk, Patricia wrote:
>
>> I am trying to figure out how to calculate the next payment date,
>> particularly for recurring bills (hosting).  It should be the same  
>> day
>> every 3 months.  This is how I have it, but it is not working.
>> (FMA9 on
>> WinXP):
>>
>> If (
>> RecurringBill = "No" and BalanceDue > 0; LastPmtDate + 30;
>> RecurringBill = "Yes" and (IsEmpty ( Hosting::CancelDate ) or
>> Hosting::CancelDate > Get ( CurrentDate ));
>>   Date (
>>     Month ( LastPmtDate + 3 ) ;
>>     Day ( Hosting::StartDate ) ;
>>     Year (
>>          If (
>>            Month ( Get ( CurrentDate ) ) = 12;   ( Get ( CurrentDate
>> )+1 ); ( Get ( CurrentDate )
>>          )
>>     )
>>   )
>> )
>> ; "")
>>
>> ...also, maybe I shouldn't be using the last payment date, as if they
>> don't pay on time, that throws off the quarterly schedule.  I am
>> thinking there is a better way.
>>
>> Thanks!
>> Trish
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> _______________________________________________
>> FMPexperts mailing list
>> FMPexperts@...
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
> _______________________________________________
> FMPexperts mailing list
> FMPexperts@...
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
> _______________________________________________
> FMPexperts mailing list
> FMPexperts@...
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[VanBuskirk, Patricia]

Thanks Tom ... I used your first calculation just to get it working (I
can figure out the other part later).  It is still not working.  Here is
my entire calc:

Case (
  RecurringBill = "No" and BalanceDue > 0; LastPmtDate + 30;
  RecurringBill = "Yes" and (IsEmpty ( Hosting::CancelDate ) or
Hosting::CancelDate > Get ( CurrentDate )) and IsEmpty ( LastPmtDate );
    Date ( Month ( Hosting::StartDate + 3 ) ; Day ( Hosting::StartDate )
; Year ( Hosting::StartDate ));
  RecurringBill = "Yes" and (IsEmpty ( Hosting::CancelDate ) or
Hosting::CancelDate > Get ( CurrentDate )) and not IsEmpty ( LastPmtDate
);
    Date ( Month ( LastPmtDate + 3 ) ; Day ( Hosting::StartDate ) ; Year
( LastPmtDate ) )
;"")

For an example of how is it calculating, I have a record that contains
the following info.  I tried different dates to see the results:

Start Date     LastPmt      NextPmt    CancelDate (empty on both)
 4/10/06       2/8/08       2/10/08
              12/31/07      1/10/07      



-----Original Message-----
From: fmpexperts-bounces@...
[mailto:fmpexperts-bounces@...] On Behalf Of Tom
Elliott
Sent: Sunday, July 20, 2008 5:15 PM
To: fmpexperts@...
Subject: Re: Figure payment due date...

Trish

You don't say how it's not working which makes it a little tricky to  
debug but ...

In the 2nd Case you don't need the complicated Year calc; having added  
3  months you should just use the year of LastPmtDate and FMP will  
take care of cases where the resulting date is in the following year

In other words the result should be:

Date ( Month ( LastPmtDate + 3 ) ; Day ( Hosting::StartDate ) ; Year  
( LastPmtDate ) )

Of course that still leaves your  "throws off the quarterly schedule"  
dilemma. To solve that you need to know how many payments have been  
made (or *should* have been made) - I guess you can glean that from  
existing data (if not, you need to introduce a new field to record  
this) - so let's say you have a field (PmtCount) that gives you this  
figure, then your result for the 2nd Case would be:

Date ( Month ( LastPmtDate + 3 * PmtCount ) ; Day  
( Hosting::StartDate ) ; Year ( LastPmtDate ) )

(I think)

cheers

Tom

On 20 Jul 2008, at 9:23, VanBuskirk, Patricia wrote:

> Ah yes, I did change that.  For simplicity on the email, I was
> originally going to change it to a simple if statement without the  
> first
> statement, then decided to leave it as it was, but I forgot to change
> that part back.  Thanks!
>
> -----Original Message-----
> From: fmpexperts-bounces@...
> [mailto:fmpexperts-bounces@...] On Behalf Of Tom
> Elliott
> Sent: Sunday, July 20, 2008 4:13 PM
> To: fmpexperts@...
> Subject: Re: Figure payment due date...
>
> Trish
>
> I don't know if it's a typo or not but your first 'If (' should be a
> 'Case ('
> On 20 Jul 2008, at 9:09, VanBuskirk, Patricia wrote:
>
>> I am trying to figure out how to calculate the next payment date,
>> particularly for recurring bills (hosting).  It should be the same  
>> day
>> every 3 months.  This is how I have it, but it is not working.
>> (FMA9 on
>> WinXP):
>>
>> If (
>> RecurringBill = "No" and BalanceDue > 0; LastPmtDate + 30;
>> RecurringBill = "Yes" and (IsEmpty ( Hosting::CancelDate ) or
>> Hosting::CancelDate > Get ( CurrentDate ));
>>   Date (
>>     Month ( LastPmtDate + 3 ) ;
>>     Day ( Hosting::StartDate ) ;
>>     Year (
>>          If (
>>            Month ( Get ( CurrentDate ) ) = 12;   ( Get ( CurrentDate
>> )+1 ); ( Get ( CurrentDate )
>>          )
>>     )
>>   )
>> )
>> ; "")
>>
>> ...also, maybe I shouldn't be using the last payment date, as if they
>> don't pay on time, that throws off the quarterly schedule.  I am
>> thinking there is a better way.
>>
>> Thanks!
>> Trish
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> _______________________________________________
>> FMPexperts mailing list
>> FMPexperts@...
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
> _______________________________________________
> FMPexperts mailing list
> FMPexperts@...
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
> _______________________________________________
> FMPexperts mailing list
> FMPexperts@...
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[kazar]

On 07-20-2008 8:02 PM, VanBuskirk, Patricia wrote:

> Thanks Tom ... I used your first calculation just to get it working (I
> can figure out the other part later).  It is still not working.  Here is
> my entire calc:
>
> Case (
>   RecurringBill = "No" and BalanceDue > 0; LastPmtDate + 30;
>   RecurringBill = "Yes" and (IsEmpty ( Hosting::CancelDate ) or
> Hosting::CancelDate > Get ( CurrentDate )) and IsEmpty ( LastPmtDate );
>     Date ( Month ( Hosting::StartDate + 3 ) ; Day ( Hosting::StartDate )
> ; Year ( Hosting::StartDate ));
>   RecurringBill = "Yes" and (IsEmpty ( Hosting::CancelDate ) or
> Hosting::CancelDate > Get ( CurrentDate )) and not IsEmpty ( LastPmtDate
> );
>     Date ( Month ( LastPmtDate + 3 ) ; Day ( Hosting::StartDate ) ; Year
> ( LastPmtDate ) )
> ;"")
>
> For an example of how is it calculating, I have a record that contains
> the following info.  I tried different dates to see the results:
>
> Start Date     LastPmt      NextPmt    CancelDate (empty on both)
>  4/10/06       2/8/08       2/10/08
>               12/31/07      1/10/07      
>  

It looks like in the Specify Calculation window, you need to uncheck the
box that says something like "Do not evaluate if all fields are empty".
I vote for that checkbox as being the most misleading element in all of
FileMaker Pro. If you translate it into: "Do not evaluate if any
referenced field is empty," you will get an idea what it's for. It's
checked by default, but in any circumstance where part of your calc is a
Boolean test to see if a referenced field is empty, it must be unchecked
to work. (There are other times you'll want to uncheck it too, not just
for Empty tests.)

did that help?

kazar

--
Datatude, Ltd. -- http://www.datatude.net/
strategies & solutions

_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au