Remove matches between different collections with Except

The except function allows you to filter variables from one collection based on another collection's contents. Specifically, it returns variables from the first collection that are not present in the second collection. In other words, it helps you find the difference between two collections by excluding matching variables.  

Syntax

{#collection 1 | [insert filters] | except:collection 2:'shared unique variable'}{/}

• Colons will separate the except function from the collection and the shared unique variable.  
• The collection does not require single quotes while the shared unique variable does.

This function is especially useful when working with any workflow tasks that have an outgoing section for principals such as organizational or change of principals tasks.

Example 1: Return only the Acting Directors

Looking at the sample data below, the affiliations on the left side of the table are all of the directors of the entity.  On the right, we have the two directors who are leaving and are entered in the task as outgoing.

Desired Result:

According to the directors that were selected in the task as outgoing, Leslie Knope and Ron Swanson must be removed from the list of current directors.

Looking at the table above, we have collection 1 as affiliations and collection 2 as outgoingAffiliations subcollection from the tasks root collection and they share the uniq id variable as affiliation ID.  Also, the affiliations collection holds all the data that exist in the outgoingAffiliations collection.   So in this case, we want to extract the data from the affiliations collection that is not in the outgoingAffiliations collection.  

Code:

{#affiliations | filter:'role':'Director' | status:'!inactive' | uniqBy:'affiliationID' | except:(tasks | grab:1:'outgoingAffiliations'):'affiliationID' | count | gt:1}{/}

This code will loop through the affiliations collection looking for all the directors that do not have the status "inactive" and filter out any duplication using the affiliation ID and then remove any matching outgoing affiliations using the unique common variable, affiliationID. 

I could use the code above to determine whether there is more than one acting director for this entity and make the word "Director" plural. 

Director{affiliations | filter:'role':'Director' | status:'!inactive' | uniqBy:'affiliationID' | except:(tasks | grab:1:'outgoingAffiliations'):'affiliationID' | count | gt:1}s{/}

Removing the count logic, I could also use this coding in a sentence format that could look like:

{affiliations | filter:'role':'Director' | status:'!inactive' | uniqBy:'affiliationID' | except:(tasks | grab:1:'outgoingAffiliations'):'affiliationID' | toSentence:'participant_fullName'} are elected the directors of the corporation for the ensuing year.

Result:

Ben Wyatt, Andy Dwyer and April Ludgate are elected the directors of the corporation for the ensuing year.

Example 2: Count the Number of Current Directors

This code could also be used to return the number of directors of the entity when used in conjunction with the count function.

Desired Result:

Using the data table above, we would like to count the number of current directors of the Corporation.

Code:

The number of directors of the Corporation is fixed at {affiliations | filter:’role’:’Director’ | status:’!inactive’ | uniqBy:’affiliationID’ | except:(tasks | grab:1:‘outgoingAffiliations’):’affiliationID’ | count}.

Result

The number of directors of the Corporation is fixed at 3.

Notes:

• The user will need to add the outgoing directors to the task. 
• This code will remove the directors that are added to the outgoing section of the task, regardless of what their status is in Athennian, from the total directors of the entity.  
• Not necessary to use the uniqBy function, but is recommended.