Hello
I have created a validation for an Interactive Grid region ensuring an entered key will not collide with it's period (validfrom/validto) of another existing record with same key. Example:
Imagine this record does already exist in the DB:
Key Valid from Valid to
12345 01/01/2020 31/12/2020
Now I enter new entry in the Interactive Grid with these values:
Key Valid from Valid to
12345 05/05/2020 31/12/2020
On insert that new record this Validation will be triggered (No Rows returned) to check if the key to be inserted already exists and overlaps any other key with same key_value
SELECT 1
FROM <target_table>
WHERE rule_id != :RULE_ID -- PK (to exclude existence check against itself)
AND key = :KEY
AND TO_DATE(:VALIDFROM, 'DD/MM/YYYY') BETWEEN validfrom AND validto
On Insert the validation accepts (ignores) there is an existing key with same value (12345) and overlapping period (valid from / valid to) but it should stop and output the entered error message.
So the record will be inserted and no error message popping up. Now I change the valid from of the entered entered and save it again which leads to an update. This time the validation rule fires correctly!
I have played around with the Validation settings:
- Always execute: yes
- Execution Scope: For created and modified rows / for all submitted rows
All over: the validation is working as stated on updates.
Is there anything else I have to consider to make the validation working on insert?