# Using date fields to make a record a compliant

This topic is 1000 days old. Please don't post here. Open a new topic instead.

## 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!

##### Share on other sites

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?

##### 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
##### 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
• 1
##### Share on other sites

Perfect thank you!

##### Share on other sites

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

##### Share on other sites

Thanks, what does making it stored achieve?

##### Share on other sites

This topic is 1000 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account