NOT IN equivalent in Reporter

Here’s the scenario: I have two tables, Positions and Assignments. Positions is the full inventory of available positions in my organization. Assignments where organization members are tied to their assigned position. Assignments may have a status of either “Queued”, “Current”, or “Closed”. The business rule is that we cannot assign positions that are in either a Queued or Current status.

I am trying to build a report that will display all positions available for assignment. I essentially need to do the Corteza equivalent of this:
SELECT * FROM positions
WHERE recordID NOT IN (SELECT positionID FROM assignments WHERE assignmentStatus = ‘Current’ OR assignmentStatus = ‘Queued’)

Assistance appreciated.

  • You need 2 load datasources (Positions, Assignments)
  • Filter the Assignments so they contain the desired statuses only
  • Join the two datasources based on ID