Oracle Apex - Date effective non-overlapping dates via validation and interactive grid - How to refer to interactive grid current row values

Question

I'm trying to create a validation on create and update of an interactive editable grid in Oracle Apex 5.1. I right click the effective_start_date column and then choose Create Validation. From here I then set Type to No Rows returned with the idea of doing a select where there are other rows for the same date range as the current row being created or updated.

I have the following query:

select *
from   my_table c
where  c.salary_band = :salary_band
and    c.id != :id
and    c.effective_start_date <= :effective_end_date
and    c.effective_end_date   >= :effective_start_date

However this does not work as expected (error when there are overlapping dates for a salary band). Even when I comment out the last two lines it does not error on a duplicated salary band value. My assumption is that I am not referring to the current row values of the interactive grid entered during the create or update incorrectly although no error is thrown when I create the validation. How do I refer to the current row values entered in the interactive grid or what am I doing wrong here?

Answer 1

Turned out to be the :id that does not exist when creating, the other values work fine.