Connection between an item list and a dictionary

0

Hi Darek,

this would be easy, if you could do this check with a "wizard" step. Then you could either use:
- Change item list values, to update all rows using a SQL query
- Use the for each operator introduced with BPS 2022. With this you can select the item list and use the 'change value of single field". In the action you can select a column from the item list and in the SQL query, you can reference the value of the field you are using to verify whether the row exists.

An alternative could be to use the "Form rule invoked after callback" in the configuration tab of item list. You could use the for each row to set a column to a value. The value will be returned by a prepared business rule, which has a parameter. This parameter will be set to the column value which you are using to check whether the row exists.

I haven't tested the callback approach but it could work.

Best regards,
Daniel

I don't have BPS 2022 yet:/
Change item list value would be ok but can I ask you for an example of the SQL query?

Let say that I have an item list with Column A and column B. I also have a dictionary with Column X.

I want "1" in column B whenever value in column A exists in column X

Hi Darek,

I don't have a suitable data at my end, so I will write this query without the option to test it.
DET_ID can be used as the "unique value" to update the rows, and DictionaryEntryExists can be used for your column.

Best regads,
Daniel

select DET_ID,
case when Exists(
select WFD_ID
from WFElements
where WFD_DTYPEID = 0 -- DICTIONARY_FORM_TYPE_ID
and WFD_AttText1 = DET_Att1-- Compare the fields
-- add an optional condition whether the dictionary entry has to be active
-- If you are working with multiple business entities and each has their own dictionary you would need another condition
) then 1
else 0
end as DictionaryEntryExists

from WFElementDetails
where DET_WFDID = CURRENT_WORKFLOW_INSTANCE

Works perfectly. Thank you