Filtering a collection on multiple criteria

[AdamMacgregor-S]

Hi, I have a collection that I need to filter on multiple criteria, so it would need filtering on Criteria 1 then Criteria 2 then Criteria 3. I cannot seem to get the Syntax to do this in one filter so Curently am having to do Filter 1 then Filter 2 then Filter 3.   Just wondering if you could do it in one long expression

[John__Carter]

[Field 1]='a' AND Field2>0 AND [Field 3]=False

[Ivan_FernandoZa]

Hi John, is there a reference guide for the filter syntax anywhere?

[DaveMorris]

Hi fernand-7, I'm not sure if BP has a guide on this or not, but the Filter input seems to be equivalent to a SQL WHERE clause. For example, if you're working with comparing dates, you need to put # around the dates such as ""[dateField]>#9/27/2018#"". And if you need to use a wildcard, you can use LIKE such as (and no I'm not Miss SC): ""[textField] LIKE '%some text%'"". w3schools is always a good place to start: https://www.w3schools.com/sql/sql_where.asp Respectfully, Dave

[AndreyKudinov]

It is mostly SQL-like syntax, but not exactly... for example LIKE '%this%one%' not allowed, you can get almost same result with two likes. Here is the docs for DataTable.Select() method syntax : https://docs.microsoft.com/en-us/dotnet/api/system.data.datatable.selec… http://www.csharp-examples.net/dataview-rowfilter/

[MatthewBlease]

​I know this thread is a bit old now but have not found a decent answer on here to what I believe is a fairly common problem so here goes...
For inputs:
Collection In  = The collection to be filtered
Filter = "[ColumnXFromCollectionIn]="&"'"&[Data Item 1]&"' AND [ColumnYFromCollectionIn]="&"'"&[Data Item 2]&"' AND " [ColumnZFromCollectionIn]="&"'"&[Data Item 3]&"'"

The above would allow you to filter 3 different columns from the 'Collection In' against 3 separate Data Items.  If you wanted to hardcode the filter criteria you could use:

Filter = "[ColumnXFromCollectionIn]='Apples' AND [ColumnYFromCollectionIn]='Bananas' AND [ColumnZFromCollectionIn]='Oranges'"

You could even filter one collection against another:
Filter = "[ColumnXFromCollectionIn]="&"'"&[Coll.Col1]&"' AND [ColumnYFromCollectionIn]="&"'"&[Coll.Col2]&"' AND " [ColumnZFromCollectionIn]="&"'"&[Coll.Col3]&"'"

Hope this helps


------------------------------
Matthew Blease
RPA Developer
Lloyds Banking Group
Europe/London
------------------------------