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