Dictionary update - Unique steps of workflow- Validation error

0

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

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

You mean as in the attached screenshot?

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?

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

Thanks Daniel.
You mean as below. I modified as below but not working:
SELECT CASE

WHEN NOT EXISTS (

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

dbo.ClearWFElemIDAdv(WFD_AttChoose1)='9'

AND isnull(dbo.ClearWFElemAdv(WFD_AttChoose2),'') = '15'

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

THEN 1

ELSE 0

END

Hi kaluser,


it seems that I can't help without seeing the data.
This would be my approach in your case check...
which data is returned by the inner select statement?
Which data is returned without the AttChoose conditions?
when does the statement fail
Include the WFD_ID as well as the DTYPE just in case.

Best regards,
Daniel

I appreciate all your help Daniel. Attached the modified code which is working now.