Not sure if you are using a portal, a calculation, searching or what but here is the general calc:
To find all Expiration Dates one month before today and backwards, even those that have expired, you can use:
ExpirationDate < Get ( CurrentDate ) + 30
This will return a boolean true statement of 1.
And a script can enter "< " & Get ( CurrentDate ) + 30
as find criteria and that avoids the need for a calculation field if all you need to do is use a find to pull up the records.
"I've managed to get it to show the past expired dates, but can't for the life of me figure out how to get it to show a month in advanceds expirations."
This pointed to calculation, Phil, but I suspect you are going in the right direction. Thanks for bringing up the concept of searching. The question is ... how should this information be displayed and what will be done with these records?
It's for notices for staff on members whom let their membership expire and for a month advanced notice for those whose membership is about to expire. I simply added a button that did a find function and gave me the members whose membership had expired already, just wasnt able to get it to show a months in advanced so that we know who is going to need a new membership. Like I said I'm not properly trained in this sort of thing.
So under "Button Setup" I choose "Found Sets" and then "Perfom Search..." and specified a search of "membersmain::renewal_date_next<=//" so that I would get the dates before the current date that had not been renewed. Am I going about this wrong?
You can display on the layout, a message to the User but only to them - a private message telling them that they have " " days until their membership expires or that it has expired.
My questions surrounded the audience who should see the message (whether a weekly, monthly report where someone on your staff can follow up and send emails to the members that they are close to expiring) or whether it should pop up on all layouts for the current User to see that they are expired or getting closer to expiring, something like:
If Bill is viewing a layout and Bill's membership will expire in 14 days, the message says, "Bill, your membership will expire in 14 days." ... and it counts down as a reminder; or it says "Bill, your membership has expired!". If a find and report serves the purpose then we are pleased that you have what you needed.
I understand that but, members aren't using the program just staff, and we print off a sheet listing the members who have expired and are soon to expire. I am just the most computer literate person on staff, and I am looking into to making things more stream line. since I have no proper training is there anyway you could or someone else could point me in the direction or write a tutorial as to how to do this?
Well no then, you aren't doing it wrong in your approach but "membersmain::renewal_date_next<=//" will not work if you used the = between the M and //. Regardless, it will only find Expiration Dates older than today and you want to find future Expiration Dates 30 days ahead, right? If so, you cannot use a stored find. Stored finds can only be used for static data and not calculations although in vs. 11, you could now set a variable with the calculation then use the variable in the stored find. I suggest a script such as:
Go to Layout [ list layout based upon Memberships ]
Enter Find Mode [ uncheck pause ]
Set Field [ ExpirationDate ; "< " & Get ( CurrentDate ) + 30 ] ... this is Phil's suggestion
Set Error Capture [ On ] ... this suppresses the FM message if no records are found and allows you to handle it yourself
Perform Find [ ]
Now you have your list.
I have a similar question (but extended).
I want to filter using a date range ie to select membership renewals which are due between 5-7 months from current date.
The solution you provided above works to get dates after 5 months but I can't work out how to limit the other end.
I want dates which are > CurrentDate + 150 and < CurrentDate + 210
How to do that?
Let ( d = Get ( CurrentDate ) ;
Date ( Month ( d ) + 5 ; Day ( 1 ) ; Year ( d ) ) & ".." &
Date ( Month ( d ) + 7 ; Day ( 1 ) ; Year ( d ) )
Note that I'm not using days because if you do then your results will be strange, such as:
If today is 5/1/2011, your range would be 9/28/2011..11/27/2011. By incrementing the month it would be:
The range you requested is inclusive. If you want to start one day greater than 5 months and end one day less than 7 months, try:
Let ( d = Get ( CurrentDate ) ;
Date ( Month ( d ) + 5 ; Day ( 2 ) ; Year ( d ) ) & "..." &
Date ( Month ( d ) + 7 ; 0 ; Year ( d ) )
Thanks LaRetta - that's awesome - works brilliantly.
Thanks LaRetta, thats exactlly what I needed got confused later on the Set Field part but managed to get it to work properly, thanks again, this will definetly save alot of headaches for the staff and save time as well :D