Prevent duplicate entries

0

Hi Martin,

I would add a business rule, use this in a form rule and execute it on value change. The form rule would empty the field if the value is not unique. So if the field is mandatory this should also have a visible effect. Not sure whether it's possible but you could add an regular expression to check for null and add an appropriate message. I think the RegEx would be something like this ^$ but I haven't tested it.

In addition I would execute the business rule during path transition. Business rule

select
case
when (
select Count(*) from wfelements
where WFD_DTYPEID= {DTYPE_ID}
and WFD_ID <>{WFD_ID}
and WFD_COMID = {COM_ID}
and {WFCONCOL:1723} = '{1723}'
) > 0 then 0
else 1
end as ValueIsUnique.

I tested this with new (unsaved) and saved workflow instances.

I believe calling business rules has been added in version 2020.


Best regards,
Daniel

In addition to what Daniel said I suggest to also define a "validate form" action on save and paths doing the same thing. it might happen that there is a timegap between value change in your field and saving to database (save, next step).

It might happen that 2 users start a workflow at the same time and enter the same value - you wouldnt catch that if you only doublet-check on valuechange. I had the same problem, and ended up validating it in every step.

Also keep in mind that this way you will only find instances that have not been archived.

Good catch Christian. I've forgotten the archive databases. So you would need to modify the business rule to take care of them as well.

Thank you both! That answers saved me a few hours of mental jogging... :)