Jump to content

Using date fields to make a record a compliant


Recommended Posts

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

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 by Chrism
Link to post
Share on other sites

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 by comment
  • Like 1
Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.