12 Replies Latest reply on May 2, 2011 5:59 AM by JeremyHill

    Membership Expiration Date 1 Month Advanced Sorting

    JeremyHill

      Title

      Membership Expiration Date 1 Month Advanced Sorting

      Post

      Hi, so I'm new to this whole filemaker system, not properly trained but have managed to get to where I feel comfortable changing some values. I work at a local gym, and I was curious if there is indeed a way to show membership expiration dates a month ahead of them expiring as well as the ones that have expired from the current days date. 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. Can anyone help me out here?

        • 1. Re: Membership Expiration Date 1 Month Advanced Sorting
          LaRetta_1

          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.

          • 2. Re: Membership Expiration Date 1 Month Advanced Sorting
            philmodjunk

            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.

            • 3. Re: Membership Expiration Date 1 Month Advanced Sorting
              LaRetta_1

              "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?

              • 4. Re: Membership Expiration Date 1 Month Advanced Sorting
                JeremyHill

                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.

                • 5. Re: Membership Expiration Date 1 Month Advanced Sorting
                  JeremyHill

                  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?

                  • 6. Re: Membership Expiration Date 1 Month Advanced Sorting
                    LaRetta_1

                    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.  Laughing

                    • 7. Re: Membership Expiration Date 1 Month Advanced Sorting
                      JeremyHill

                      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?

                      • 8. Re: Membership Expiration Date 1 Month Advanced Sorting
                        LaRetta_1

                        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.

                        • 9. Re: Membership Expiration Date 1 Month Advanced Sorting
                          LaurenceHarrould

                          Hi LaRetta

                          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?

                          Thanks
                          Laurence 

                          • 10. Re: Membership Expiration Date 1 Month Advanced Sorting
                            LaRetta_1

                            Try:

                            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:

                            10/1/2011..12/1/2011

                            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 ) )
                            )

                            • 11. Re: Membership Expiration Date 1 Month Advanced Sorting
                              LaurenceHarrould

                              Thanks LaRetta - that's awesome - works brilliantly.

                              • 12. Re: Membership Expiration Date 1 Month Advanced Sorting
                                JeremyHill

                                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