Chrism 0 Posted September 6, 2020 Share Posted September 6, 2020 Hi, Similar my post on Friday regarding renewal dates, which was very kindly answered. Thank you! I want to be able to automatically flag companies compliant / non-compliant based on the expiry dates of their qualifications / certifications. For a company to be compliant they need to meet meet 3 x criteria. I want to create some simple Calculation fields which I think is the best way to Achieve this? Criteria 1 calculation - They all have valid expiry dates for ALL of Renewal1, Renewal2 and Renewal3, is this true then is puts say '1' in that Calculation field? (If any of the expiry dates are blank then they are non compliant) Criteria 2 Calculation- They need to have either a valid Expiry date in Renewal4 OR Renewal5, if this is true then is pits a "1" in that calculation field. (If any of the expiry dates are blank then they are non compliant) Criteria 3 Calculation- They need to have either a valid Expiry date in Renewal6 OR Renewal7, if this is true then is pits a "1" in that calculation field. (If any of the expiry dates are blank then they are non compliant) Then a final Calculation that displays Compliant if all the above calculations are '1'. And if they do not all contain '1', marked as non-compliant Hope this makes sense! Link to post Share on other sites
comment 1,775 Posted September 6, 2020 Share Posted September 6, 2020 This all seems quite simple, except for one thing: what is a "valid" expiry date? Under what circumstances would a field contain an "invalid" expiry date? Link to post Share on other sites
Chrism 0 Posted September 6, 2020 Author Share Posted September 6, 2020 (edited) Hi, An invalid Expiry date would be anything older than todays date or the field is blank! A Valid Expiry is anything todays date and newer! Edited September 6, 2020 by Chrism Link to post Share on other sites
comment 1,775 Posted September 6, 2020 Share Posted September 6, 2020 (edited) Well, then you could write it all in a single calculation field = // Criteria 1 Renewal1 ≥ Get (CurrentDate) and Renewal2 ≥ Get (CurrentDate) and Renewal3 ≥ Get (CurrentDate) and // Criteria 2 ( Renewal4 ≥ Get (CurrentDate) or Renewal5 ≥ Get (CurrentDate) ) and // Criteria 3 ( Renewal6 ≥ Get (CurrentDate) or Renewal7 ≥ Get (CurrentDate) ) If you like, you could make it a bit more readable: Let ( [ today = Get (CurrentDate) ; criteria1 = Renewal1 ≥ today and Renewal2 ≥ today and Renewal3 ≥ today ; criteria2 = Renewal4 ≥ today or Renewal5 ≥ today ; criteria3 = Renewal6 ≥ today or Renewal7 ≥ today ] ; criteria1 and criteria2 and criteria3 ) This will return a result of 1 (True) when all criteria are met, 0 (False) otherwise. You could make it return a text result by changing: criteria1 and criteria2 and criteria3 to: If ( criteria1 and criteria2 and criteria3 ; "Compliant" ) However, it is much more convenient to leave such fields as Boolean 1/0 and either format them as Boolean on the layout or use them to conditionally format or hide a layout object (unfortunately, the string "Compliant" is too long for Filemaker's Boolean field format). Important: As any calculation field that references the current date, this field must be unstored. --- Caveat: untested, may contain typos. Edited September 6, 2020 by comment 1 Link to post Share on other sites
Chrism 0 Posted September 6, 2020 Author Share Posted September 6, 2020 Perfect thank you! Link to post Share on other sites
bcooney 143 Posted September 7, 2020 Share Posted September 7, 2020 I would suggest setting the field in a nightly server script so it is stored. Link to post Share on other sites
Chrism 0 Posted September 8, 2020 Author Share Posted September 8, 2020 Thanks, what does making it stored achieve? Link to post Share on other sites
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now