Custom data source advanced filter and "in" operator

0

Hi,

I'm currently trying to figure out a way to filter a Custom data source with an "dynamic" filter.
Here's a rough overview:


#Custom data source
I've created an SDK of type data source which prepares the data

#Filter
I have a filter value which is dynamic for a given workflow instance. Here are two examples:
{COLUMN:ID#ID} in ('KHL_030','KHL_020')
{COLUMN:ID#ID} in ('KHL_001','KHL_010')

#"Dynamic" value
Since there are no options to use business rules or similar in a data source filter of a choose field I'm preparing the filter value on step entry. This is sufficient for this case..

#With a standard SQL data source I would use one of these options. The hard coded values would be replaced by the field value.
{COLUMN:ID#ID} in ('KHL_030','KHL_020')
{COLUMN:ID#ID} in (select item from dbo.SplitToTable('KHL_030,KHL_020',','))*/

#The first approach fails
Field value: 'KHL_B030','KHL_B020','HL_B008'
Filter: {COLUMN:ID#ID} in ({1027})
Expression preview: ID in (''KHL_B030'',''KHL_B020'',''HL_B008'')
The single quotes are escaped and therefore I get an error:
Query test
Error occured during business rule evaluation.
Syntax error: Missing operand after 'KHL_B030' operator.

#The second approach doesn't work either
Field value: KHL_B030,KHL_B020,HL_B008
Filter: {COLUMN:Id#Id} in (select item from dbo.SplitToTable('{1027}',';'))
Expression preview: ID in (''KHL_B030'',''KHL_B020'',''HL_B008'')


Error occured during business rule evaluation.
Syntax error: Missing operand after 'item' operator.
at System.Data.ExpressionParser.Parse()
at System.Data.DataExpression..ctor(DataTable table, String expression, Type type)
at System.Data.Select..ctor(DataTable table, String filterExpression, String sort, DataViewRowState recordStates)
at System.Data.DataTable.Select(String filterExpression)
at WebCon.WorkFlow.BusinessLogic.Connectors.Helpers.SearchFiltersHelper.ApplyFiltersOnDataTableAsync(DataTable dt, SearchConditions searchConditions, Boolean allowEmptyValues, List`1 settings, ITagParser tagParser, IDataSourceFiltersBusinessRulesEngineFactory filterEngineFactory)
at WebCon.WorkFlow.BusinessLogic.Connectors.DataSourceConnectors.CustomDataSourceConnector.<>c__DisplayClass7_0.<<TestQueryAsync>b__1>d.MoveNext()


It seems that's not possible to use any functions from the content database in a Custom data source filter. The filter mode of the data source is set to "Using BPS"

Does anyone have an idea?
I would prefer that the data source definition is not linked to the process in which it is used. If I don't find another way, I would extend the SDK with the filter option but this would also mean, that I need to create a data source which is linked to the process.

Best regards,
Daniel