Count of records, matching a criterion, for a group

Topic Labels: Formulas
576 2
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

I have a table named Licenses with a Current? field [Yes|No].  The table is linked to the Employees table by the Employee # field. 

For each Employee, there could be a License record with:

  • zero or many rows where Current? is No
  • zero or one row where Current? is Yes

I need a formula, which will I will use as a filter, that returns a Boolean if the Employee doesn't have a Current? = Yes row. 

Essentially, I need a list of Employees that don't have a current license.

How should I approach this?

2 Replies 2

In your 'Employee', create a "Count" field and give it a conditional of "Current = 'Yes'".  This'll output the number of Liceneses with the value "Yes", then you can filter by this field to get the employees that don't have a current license

I actually need this filter in the Licenses table.  Can your approach be modified to do so?