Using date fields to make a record a compliant

[Chrism 0]

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!

[comment 1,742]

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?

 

[Chrism 0]

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 by Chrism

[comment 1,742]

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 by comment

[Chrism 0]

Perfect thank you!

[bcooney 142]

I would suggest setting the field in a nightly server script so it is stored. 

[Chrism 0]

Thanks, what does making it stored achieve?