Home > Forum > Data sources > Filtering in the dictionary

Filtering in the dictionary
0

Hey I have two choice fields Product Group and Product Category (groups are within the categories) so I have created filter that when somebody selects certain category they can only see in choice field product group, groups that are within the specified product category based on dictionary.

However now is coming the problem that sometimes business would like to select two or more categories and when they do so the product group is empty. Is there a smart solution for that? Maybe in case of selection of more than 1 product category the filtering will not be applied?

thanks in advance for help.

Hi Jan,
Did you try advanced filter in picker field? I think that basic filter from the filed above works correctly when there is only one value because it shows 1:1 relation (products category matches values with product groups. Every product group has one product category).
Imo, try to change filter to advanced and write filter like "IN" formula in dictionary values collection from database. I think it would be easier than change every value in dictionaries.

Anyone has better solution? :)

MVP
In reply to: Cezary Rzucidło

Hi Jan,
Did you try advanced filter in picker field? I think that basic filter from the filed above works correctly when there is only one value because it shows 1:1 relation (products category matches values with product groups. Every product group has one product category).
Imo, try to change filter to advanced and write filter like "IN" formula in dictionary values collection from database. I think it would be easier than change every value in dictionaries.

Anyone has better solution? :)

Adding to Cezary's answer:

I also would opt for the advanced filter

It would be something along the lines to get the groups matching the categories

and PRODUCT_GROUP_ID /* which should be the same ID column in the dictionary column as well as in the workflow instance */
in (select item from dbo.SplitToTable('FORM_FIELD_VALUE_PRodct_Group_ID_Values',';') /* I'm not sure whether the the delimiter will be , or ; */

In case you want to return all categories the condition could be something like
and ((FORM_FIELD_VALUE_PRODUCT_GROUP_ID_AND_VALUE like '%;%') or /* check whether it's a multi value string */
('FORM_FIELD_VALUE_PRODUCT_GROUP_ID_AND_VALUE' not like '%;%' and PRODUCT_GROUP_ID = 'FORM_FIELD_VALUE_ID')) /* if it's not a multi value string, filter the group field */

In case there's some syntax error with the query:
a) Enable diagnostic mode to get the full query and search for "SplitToTable" to find the condition in the long query.
b) Start SQL Server tracing


Best regards,
Daniel