Filter DataTable Activity, Condition Can be "*"?

[u2018dem0528]

Dear All

I have 2 columns in dt_Combined DataTable and need to filter “sales” and “customer”. (dt_Combined DataTable doesn’t have any null or empty values in both columns.)

My conditions may be a bit tricky.
It is not always true that SalesID and CustomerID have values.
Sometimes, either SalesID is empty OR CustomerID is empty.
(Both may not be empty simultaneously.)

If SalesID is empty and CustomerID is “AAA Company”, I need to extract “sales” = all sales and “customer” = “AAA Company”.

If CustomerID is empty and SalesID is “123456”, I need to extract “sales” = “123456” and “customer” = all customer .

However now, if SalesID (or CustomerID) is empty, Filter DataTable returns zero outputs in dt_FilteredTemp. (Probably, Filter DataTable Activity literally searches for empty cell in “sales” column, whereas it is no-null column.) It was just same even if I used IF & ASSIGN Activities to fill asterisk in ID, whenever either of IDs is empty.

Does anyone have any idea how to resolve this?

[ppr (Peter Preuss)]

we can do it with a LINQ or with a dynamic generated Search expression used in the dt_Combined.Select method

[u2018dem0528]

Thanks for answering. However, dt_Combined.Select method doesn’t work well.
This just ends up with “The source contains no DataRows” error.

(For experimenting, I intentionally assign SalesID = “*”)

dt_Combined.Select(“[sales] = '” + SalesID+“’ And [customer] = '”+ CustomerID +“'” ).CopyToDataTable

[ppr (Peter Preuss)]

@u2018dem0528

CopyToDataTable

CopyToDataTable throws an exception when filter result is empty. We can handle this defensive by following pattern:

[u2018dem0528]

Actually returned table must not be empty in my case. It must return DataTable. It returns empty results only because Select [SalesID] = " * " didn’t function as intended.

In a nutshell, neither Filter DataTable Activity nor Select method works in this way, maybe?

[ppr (Peter Preuss)]

@ppr

with a dynamic generated Search expression

this was mentioned

about the wildcard or other functionalities for a search expression you can refer to the official docu:

DataColumn.Expression Property (System.Data)

https://www.csharp-examples.net/dataview-rowfilter/

[u2018dem0528]

I am not trying to search for data with some keyword with wildcard e.g.) ‘Product*’.

SalesID is not provided always, only sometimes. But dt_Combined dataset is always “non-null”. If I try to leave SalesID empty, filtering function tries to search “empty” or null row from dt_Combined, whereas such rows do not exist. Oppositely, if trying to put asterisk in SalesID, filtering function tries to search “asterisk” in dt_Combined, whereas such rows do not exist. It could be because asterisk is considered “one string”.

[ppr (Peter Preuss)]

@u2018dem0528

I am not trying to search for data with some keyword with wildcard e.g.) ‘Product*’.

ok, understood and remembered when later shown the snippets

@u2018dem0528

if trying to put asterisk in SalesID, filtering function tries to search “asterisk” in dt_Combined,

as clearly mentioned in the docu Wildcard search has to be done with like and not with =. Have a look on the samples:

@u2018dem0528

If SalesID is empty and CustomerID is “AAA Company”, I need to extract “ sales ” = all sales and “ customer ” = “AAA Company”.

If CustomerID is empty and SalesID is “123456”, I need to extract “ sales ” = “123456” and “ customer ” = all customer .

multiple times mentioned, a dynamic generated search expression can handle this

@u2018dem0528

filtering function tries to search “empty” or null row from dt_Combined

sounds like an interest on finding rows which are not null or empty

[u2018dem0528]

Thank you for sharing the above. It displays the expected results now.