Calculate end of month

[Matt Leach]

In our database, we have a fied that determines when our client is paid through, field name is SW PD 2. Once the paid through date comes up, the client is also given a one month grace period which if they do not pay by that time, they are marked as off maintenance. We also have a check box that will override everything and put a client as off maintenance (last script step)

This is my current script that runs on record load:

If [ (TechSupport::SW PD 2 < Get ( CurrentDate))and (Get ( CurrentDate) < TechSupport::SW PD 2 + 31) ]

   Set Field [ TechSupport::Maint_Status; "GRACE PERIOD!" ]

   Commit Records/Requests [ Skip data entry validation; No dialog ]

Else If [ TechSupport::SW PD 2 < Get ( CurrentDate ) ]

   Set Field [ TechSupport::Maint_Status; "OFF MAINTENANCE!" ]

   Commit Records/Requests [ Skip data entry validation; No dialog ]

Else If [ TechSupport::SW PD 2 > Get ( CurrentDate ) + 31 ]

   Clear [ TechSupport::Maint_Status ]

   Commit Records/Requests [ Skip data entry validation; No dialog ]

End If

If [ IsEmpty ( TechSupport::Maint ) ]

   Set Field [ TechSupport::Maint_Status; "OFF MAINTENANCE!" ]

   Commit Records/Requests [ Skip data entry validation; No dialog ]

End If

This script does well, but the problem i am having is that i need to edit each month depending on how many days there are in the month. If there a way to have the script automatically determine the last day of the current month and set the fields based on that?

[comment]

The last day of the current month is the zeroth day of next month.

[Matt Leach]

To work it into my script would i have to pull the month from the SW PD 2 field and then compare it to current month and add 1?

[comment]

I don't know. I can't see the connection between:

Once the paid through date comes up, the client is also given a one month grace period

and:

determine the last day of the current month

I'd think the month grace period ends on =

Date ( Month ( SW PD 2 ) + 1 ; Day ( SW PD 2 ) ; Year ( SW PD 2 ) )

regardless of the current date.

[Matt Leach]

Hopefully i can clarify a little.

Lets say that the SW PD 2 date is 12/31/2010.

From 1/1/2011 - 1/31/2011, the client is in a "Grace Period" as noted in the record

Once 2/1/2011 is reached, if the SW PD 2 date has not been changed (i.e., the client hasnt paid) then the record is marked Off Maintenance.

If the SW PD 2 date is 1/31/2011

2/1/2011 - 2/28/2011 would be the grace period

and so on..

Hope that clarifies my intentions.

[comment]

So what exactly is the purpose of your script? Why not make the client's status a calculation field?

Once 2/1/2011 is reached, if the SW PD 2 date has not been changed (i.e., the client hasnt paid)

I don't see the big picture here, but that doesn't seem like good practice. Once a client has paid for a maintenance period, that period has been paid for - and should remain as such forever. If they pay for another period, that's another period - i.e. another record.

[Keith LaMarre]

Its pretty easy to determine the first day of the next month - but, in all of your examples the user is paying on the last day of the month - I guess the question is - are you always offering a grace period of 31 days - or the next month (which may turn out to be 60 days if they happen to pay on the 1st or second of the current month) - or what?

This is how comment's calculation works - it simply advances the month by 1. if they pay on the 15th - their grace period goes to the 15th of the next month...(could be 28 days - could be 31)

To determine the first day of the next month (regardless of how many days in the current month) - Date(Month(SW PD 2)+1;1;Year(SW PD 2))

So, the last day of the current month is :Date(Month(SW PD 2)+1;1;Year(SW PD 2))-1

[Matt Leach]

So what exactly is the purpose of your script? Why not make the client's status a calculation field?

I don't see the big picture here, but that doesn't seem like good practice. Once a client has paid for a maintenance period, that period has been paid for - and should remain as such forever. If they pay for another period, that's another period - i.e. another record.

I guess im still not being clear on how this works.

I understand this most likely is not the best way to handle the situation, i would love nothing more than to change it but this is how our old software works and our accounting dept (who this is mainly for) is dead set against change.

Our clients pay for support on a yearly or quarterly basis. The SW PD 2 field contains the date that the client is currently paid through. The Maint_Status field contains the current status of the client. Lets say that the SW PD 2 date is set for 1/31/2011. If the client does not make any payments leading up to or after 1/31/2011, then are put in a grace period. This period is for one month following the date in the SW PD 2 field, so using this example, they would be on a grace period until 2/28/2011 and the Maint_Status field would be marked "GRACE PERIOD". If by 2/28/2011 no payment is made, the Maint_Status field would be changed from "GRACE PERIOD" to "OFF MAINTENANCE".

Lets use the same example of SW PD 2 date of 1/31/2011. If the client calls up before or even after 1/31/2011 and makes a payment for the year, the date in the SW PD 2 field would be changed from 1/31/2011 to 1/31/2012 and the Maint_Status field would remain empty or be changed from "GRACE PERIOD" to empty.

Obviously, if the payment was for a quarter, the SW PD 2 date would be changed to 3 months forward.

[Matt Leach]

Just a follow up, i think i may have come to a solution by setting the Maint_Status field as a calculation field using the following calculation:

Case (



SW PD 2 < Get ( CurrentDate ) and Get ( CurrentDate ) < Date(Month(SW PD 2)+2;1;Year(SW PD 2));

"GRACE PERIOD!";



SW PD 2 < Get ( CurrentDate ) and Get ( CurrentDate ) ≥  Date(Month(SW PD 2)+2;1;Year(SW PD 2));

"OFF MAINTENANCE!";



)

[comment]

I believe your calculation could be simplified to:

Let ( [

today = Get ( CurrentDate )

] ;

Case ( 

today > Date ( Month ( EndDate ) + 1 ; 0 ; Year ( EndDate ) ) ; "OFF MAINTENANCE!" ;

today > EndDate ; "GRACE PERIOD!"

)

However, this assumes EndDate is ALWAYS at the end of a month - otherwise the grace period can be significantly longer than a month.

our accounting dept (who this is mainly for) is dead set against change.

Your accounting should be URGING you to make this change. Your method overwrites data and keeps no history. If a customer ever disputes a previous period, you haven't got a leg to stand on.