Knack Workflow problem

I have a simple workflow, that is used commonly on many databases, but I can't find the right tools on knack to enable it, or maybe I'm missing something.

I have an object, let's call it "OBJ_Company" which has company related fields, one of those fields is a connection to another object, let's call it "OBJ_Certifications".

"OBJ_Certifications" has fields related to the company certification, which is done on a time period basis, for ex:

Date issued
Validity (date)
Issued by
Certificate file

So, every record from the "OBJ_Company" will have over time, several records associated with "OBJ_Certifications", containing the history of all certifications including the actual one, or none.

Now my ISSUE ... how can I get a rule to know the current certification status of a given company, in other words, companies that don't have any connected field with validity date after the present date.

I can't run a simple rule by the connected object to query if the "Validity" field from "OBJ_Certifications", is before today to make the assumption that the company is out of certification because it's a one to many association, and there can be one connect record that falls under that condition, but another one that doesn't (see example A and B at the end).

So the only way I can test it is to know if "ALL" records associated with that company have the validity before today and none has validity after today.

EX A: OBJ_Company -> OBJ_Certifications:

validity: Jan 01, 2019
validity: Jan 01, 2020
validity: Jan 01, 2021

In Ex A the company status would be "Certified", because although it as some associated validity records prior to today it as at least one record that is after today.

EX B: OBJ_Company -> OBJ_Certifications:

validity: Jan 01, 2019
validity: Jan 01, 2020

In Ex B the company status would be "Not Certified", because all associated validity records are before today.

Is there a way to achieve this on knack?

Hi Richard,

Sometimes we get mentally stuck, funny thing is that I had already used that exact count strategy to get numbers for my dashboard view, and that's exactly how to solve this workflow problem with the addition of the formula filter.

Thank you so much for the time you took to write it out.

Hi Bruno,

You can solve this with a Count (Number) field on OBJ_Company.

Create a new Count field on OBJ_Company "Valid Certifications", set the Connection to count to the OBJ_Certifications and save the field. Then click the field again to edit it. Towards the bottom of the window, there is a Formula Filters link. Click the Filter which records the count uses then select the Validity date field and set the operator to "is today or after". 

The Valid Certifications field on the OBJ_Comoany has a value greater than 0 when there are one or more active certifications.  

Hope this helps.