2 Replies Latest reply on Jun 4, 2014 8:00 PM by MikeWilliams

    Validation of membership by date refernce



      Validation of membership by date refernce


           Hi - I'm a newbie and finding FM fun and pretty intuitive, but I'd like some help with one function I'm trying to get right.

           We have a supporter membership database. We record the date a member joins, and the renewal date is automatically calculated (+365) and displayed as part of each record. So a member may join on 2 May 2014 and the renewal shows up as 1 May 2015.

           What I'd like to be able to do is have the status of each record displayed as a result of the information in the renewal field. For example, if the renewal date is later than the current date (whenever the record may be accessed) a message displays to say 'Current' (or maybe a green check mark) ...and conversely 'Overdue' (or a red cross) if the reverse applies and the renewal date has already passed. Objective is to make it easy to spot those who need a gentle reminder.

           Seems to me this should be relatively easy to achieve... Is it?

        • 1. Re: Validation of membership by date refernce

               There are multiple ways to achieve that result, provided that your join date and renewal date fields are both fields of type date and not text fields (or calculation fields that return a text data type.) You can't compare dates to your computer's current date if the value in the field is not of type date.

               If ( YourTable::RenewalDateField > Get ( CurrentDate ) ; "Current" ; "Overdue")

               can be defined in a calculation field with a text result type provided that you also click the storage options button and select the "do not store" option to make it an unstored calculation.

               The Boolean expression used above, YourTable::RenewalDateField > Get ( CurrentDate ) can also be used with conditional format expressions or FileMaker 13's new "Hide object when" setting to make layout objects, such as a graphic or layout text reading "overdue", appear or disappear to correctly show each member's status.

          • 2. Re: Validation of membership by date refernce

                 Thanks PhilModJunk - so simple and elegant. I've gone with the text display for now, but will play around with unveiling a graphic in due course. Really appreciat eyour help.