3 Replies Latest reply on Jan 27, 2015 5:59 PM by philmodjunk

    Automatically update a field based on time elapsed since a particular date

    MauraDoherty

      Title

      Automatically update a field based on time elapsed since a particular date

      Post

      I have two tables in my FM13 database - "Individuals" and "Donations". I have a portal set up in "Individuals" that displays all the donations (ordered by ascending "Disbursement Date") that a person has made (connected with a primary key). I also have a "Donor Status" field in my Individuals table: a drop-down menu with options like Current, New, Lapsed, Non-donor). I want to set it up so that if an individual's Donor Status = "Current Donor" but they haven't made a donation in more than 18 months, Filemaker will automatically update their Donor Status to "Lapsed Donor". Is this possible? I started trying to set up a nested If calculation to achieve this but I've only been working with Filemaker for a couple of weeks so I don't understand calculations and scripts very well yet and I'm not sure that's the most elegant way to do this. Any help is appreciated.

        • 1. Re: Automatically update a field based on time elapsed since a particular date
          philmodjunk

          Much depends on when you want FileMaker to check this info and update the donor status. How you define "18 months" can also make a difference in how you define the calculation. I'll define 18 months as 30 multiplied by 18 = 540 days as that makes for a simpler calculation.

          One Option: a calculation field defined in Donors and separate from the text field used with your drop down list could be defined as follows:

          If ( Max ( Donations::DisbursementDate ) + 540 < Get ( CurrentDate ) ; "Lapsed Donor" ; YourDropDownFieldHere )

          Select Text as the result type.

          This calculation returns the text from your drop down field unless no donation has been recorded in the past 18 months as estimated in days.
           

          • 2. Re: Automatically update a field based on time elapsed since a particular date
            MauraDoherty

            Hi Phil, thank you! I tried that and it seems be working, however it seems to be pulling in people who have never donated and calling them "Lapsed Donors" when really they're "Non-donors". Is there a way to qualify the If calculation so that only people with a donation entry in the portal (aka people whose donor status was "current donor" until the calculation was run) are affected?

            Your first point had occurred to me as well - ideally I'd like Filemaker to update the donor status every time the database file is opened/accessed. I just set up an OnLayoutEnter script trigger to run the calculation and that seems to be working as well - is there a more ideal way to do that? Is that going to slow down my loading times?

            • 3. Re: Automatically update a field based on time elapsed since a particular date
              philmodjunk

              What have posted here does not need any script or script trigger at all. I have described an unstored calculation field that should update on it's own automatically.

              If ( YourDropDownFieldHere ≠ "Non-donor" and Max ( Donations::DisbursementDate ) + 540 < Get ( CurrentDate ) ;
                    "Lapsed Donor" ;
                    YourDropDownFieldHere )