NOT IN equivalent in Reporter

đŸ§© Low-Code Development
#1

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.

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

Unfortunately, this solution won’t work. I need to get the positions that either don’t have Current or Queued matches in the Assignments table, or else don’t appear at all (have never been assigned).

If we can’t do this with NOT IN, I think we need the equivalent of a Left Outer Join (the current Join functionality only seems to do Inner Joins). Then I suppose the query would look like:

SELECT DISTINCT positionTitle
FROM
(SELECT positions.positionTitle AS positionTitle, assignments.assignmentStatus AS assignmentStatus FROM positions
LEFT OUTER JOIN assignments ON positions.ID = assignments.positionID) AS joinPositionAssignment
WHERE assignmentStatus != ‘Current’ AND assignmentStatus != ‘Queued’

I want the query to return positions that have either NULL or ‘Closed’ for assignmentStatus.

So, it looks like I would need LEFT OUTER JOIN and a switch for SELECT DISTINCT.