Home > Forum > Processes > Dictionary update - Unique steps of workflow- Validation error

Dictionary update - Unique steps of workflow- Validation error
0

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?

MVP

Hi kaluser,

the choose value in the database contains the id as well as the name. Therefore the where condition will always be false if you compare only the name.
The functions are below, but you can also select the correct one from the objects tab in the expression editor.
dbo.ClearWFElemIDAdv(WFD_AttChoose1)
dbo.ClearWFElemAdv(WFD_AttChoose1)

I would opt for the Id instead of the name. You may change the form type name in the future but all existing entries won't be updated automatically. Therefore the query may fail again.

Best regards,
Daniel

MVP
In reply to: Webcon_kaluser

I have replaced as you have suggested but still not working. Please advice.

Hi kaluser,

it think you would see the issue, if you would display the results of the inner select without the AttChoose conditions. These four values would be part of the selected columns.

I just noticed another potential issue.

What you want to do is:
Check whether another workflow, using the same form type, (77 Dictionary), uses the values of the current element in a choose column:

So we have three conditions
same form type: WFD_DTYPEID='77'
another workflow: WFD_ID <> 'Instance ID'
Same form field value: dbo.ClearWFElemIDAdv(WFD_AttChoose1) = 'ID part of field value selected form the values tab.'

Best regards,
Daniel

In reply to: Webcon_kaluser

Thank you So much Daniel.
It is working. Attached the updated query.

Daniel,
I need add one more condition now.

There is another column WFD_Attchoose2 should also be considered now. Because for few dictionary entries WFD_Attchoose2 (Subform) stays empty or NULL, for few records there will be value.
Earlier query is working when WFD_Attchoose2 is null but when There is value in the WFD_Attchoose2, I am having issues. I listed various scenarios where the query is working and not working
Example:
Scenario1: Form1 Subform(null) Step1--- Able to save- working as expected
Scenario2: Form1 Subform(null) Step2-----Able to save- working as expected
Scenario3: Form1 Subform (null) Step1--- Not able to save- working as expected

Scenario4: Form2 Subform1(Value exists) Step1 - able to save- working as expected
Scenario5: Form2 Subform1(Value exists) Step2 -- Able to save- working as expected
Scenario6: Form2 Subform1(value exists) Step1--- Not able to save-- working as expected

Scenario7: Form2 Subform1(value exists) step1- not able to save - It is an issue

So to make Scenario7 work, I added WFD_Attchoose2 to the query now:

SELECT CASE

WHEN NOT EXISTS (Select WFD_ATTchoose1, WFD_ATTchoose2,WFD_ATTChoose4 from Wfelements where WFD_DTYPEID='77' AND WFD_ID <> '4355' AND

dbo.ClearWFElemIDAdv(WFD_AttChoose1)='10' AND

dbo.ClearWFElemIDAdv(WFD_AttChoose2)=''

AND dbo.ClearWFElemIDAdv(WFD_AttChoose4)='25') THEN 1

ELSE 0

END

Now above listed scenario3 is failing. I was able to add same/Existing step as a new record.
Any suggestions on how can I modify the query?

In reply to: Webcon_kaluser

Daniel,
I need add one more condition now.

There is another column WFD_Attchoose2 should also be considered now. Because for few dictionary entries WFD_Attchoose2 (Subform) stays empty or NULL, for few records there will be value.
Earlier query is working when WFD_Attchoose2 is null but when There is value in the WFD_Attchoose2, I am having issues. I listed various scenarios where the query is working and not working
Example:
Scenario1: Form1 Subform(null) Step1--- Able to save- working as expected
Scenario2: Form1 Subform(null) Step2-----Able to save- working as expected
Scenario3: Form1 Subform (null) Step1--- Not able to save- working as expected

Scenario4: Form2 Subform1(Value exists) Step1 - able to save- working as expected
Scenario5: Form2 Subform1(Value exists) Step2 -- Able to save- working as expected
Scenario6: Form2 Subform1(value exists) Step1--- Not able to save-- working as expected

Scenario7: Form2 Subform1(value exists) step1- not able to save - It is an issue

So to make Scenario7 work, I added WFD_Attchoose2 to the query now:

SELECT CASE

WHEN NOT EXISTS (Select WFD_ATTchoose1, WFD_ATTchoose2,WFD_ATTChoose4 from Wfelements where WFD_DTYPEID='77' AND WFD_ID <> '4355' AND

dbo.ClearWFElemIDAdv(WFD_AttChoose1)='10' AND

dbo.ClearWFElemIDAdv(WFD_AttChoose2)=''

AND dbo.ClearWFElemIDAdv(WFD_AttChoose4)='25') THEN 1

ELSE 0

END

Now above listed scenario3 is failing. I was able to add same/Existing step as a new record.
Any suggestions on how can I modify the query?

Modified as below but not working:
SELECT CASE

WHEN NOT EXISTS ( If

WFD_Attchoose2 IS NULL

( Select WFD_ATTchoose1,WFD_ATTChoose4 from Wfelements where WFD_DTYPEID='77' AND WFD_ID <> '4353' AND

dbo.ClearWFElemIDAdv(WFD_AttChoose1)='9'

AND dbo.ClearWFElemIDAdv(WFD_AttChoose4)='10' )

Else

( Select WFD_ATTchoose1, WFD_ATTchoose2,WFD_ATTChoose4 from Wfelements where WFD_DTYPEID='77' AND WFD_ID <> '4353' AND

dbo.ClearWFElemIDAdv(WFD_AttChoose1)='9' AND

dbo.ClearWFElemIDAdv(WFD_AttChoose2)='17' AND

dbo.ClearWFElemIDAdv(WFD_AttChoose4)='10')

) THEN 1

ELSE 0

END

MVP
In reply to: Webcon_kaluser

Modified as below but not working:
SELECT CASE

WHEN NOT EXISTS ( If

WFD_Attchoose2 IS NULL

( Select WFD_ATTchoose1,WFD_ATTChoose4 from Wfelements where WFD_DTYPEID='77' AND WFD_ID <> '4353' AND

dbo.ClearWFElemIDAdv(WFD_AttChoose1)='9'

AND dbo.ClearWFElemIDAdv(WFD_AttChoose4)='10' )

Else

( Select WFD_ATTchoose1, WFD_ATTchoose2,WFD_ATTChoose4 from Wfelements where WFD_DTYPEID='77' AND WFD_ID <> '4353' AND

dbo.ClearWFElemIDAdv(WFD_AttChoose1)='9' AND

dbo.ClearWFElemIDAdv(WFD_AttChoose2)='17' AND

dbo.ClearWFElemIDAdv(WFD_AttChoose4)='10')

) THEN 1

ELSE 0

END

Hi kaluser,

if I understand it correctly the workflow can only be saved if the combination of all three columns hasn't been used in another workflow.
But in this case I don't understand why is a problem.
Scenario7: Form2 Subform1(value exists) step1- not able to save - It is an issue

Or should the scenario 7 be
Form___1___ Subform1(value exists) step1- not able to save - It is an issue

Nevermind this question, but the potential issue could be here:
dbo.ClearWFElemIDAdv(WFD_AttChoose2)=''
If WFD_AttChoose2 is null, than the resulting condition would be
null = ''
You can use isnull to fix this
isnull(dbo.ClearWFElemAdv(WFD_AttChoose2),'') = 'FIELD VALUE'
If WFD_AttChoose2 is null this will return
'' = 'FIELD VALUE'
otherwise it may be
'23' = 'FIELD VALUE'

Best regards,
Daniel