I have a requirement to create dictionary to enter form name, Step name, no.of business days. Form name and step name combination need to be unique and a validation error message should show if the user tries to post a entry with the same form and step combination that already exists in the dictionary.
I have 3 different forms with different steps in a flow.
I entered 1st entry as form1, Step1 and 1 day, saved it
second record as form1, step2 and 2 days, saved it.
3rd record as form1 ,step1 and 1 day. when I clicked on save, it should give the a error message as given in the validation error message panel.
The SQL I have on the form validation action on Save path is checking for IF NOT Exists and passing value 1 when the user entry is not a duplicate which means validation is successful and should allow saving the user entry.
When a user entry is a duplicate, SQL NOT EXISTS code returns value of 2 and the validation should fail with a message.
SQl is returning the correct value but the validation action not working as expected.
SELECT CASE
WHEN NOT EXISTS (Select WFD_ATTchoose1(Form name), WFD_ATTChoose4 (Step name) from Wfelements where WFD_DTYPEID='77(Dictionary ID)' AND WFD_ID='Instance ID'
AND WFD_ATTchoose1 = '{Form name}' and WFD_Attchoose4='{Step Name}') THEN 1
ELSE 0
END
Is my SQL query wrong?