topic RE: Filter Collection Problem! in Product Forum

Filter Collection Problem!

ydar360 — Fri, 18 Aug 2023 18:13:00 GMT

Given, for example, the following Collection named [Filtered Data]:

Date

Shipment Number

Haulier

8/18/2023

10101223

Fanny

8/19/2023

10101224

Nancy

8/20/2023

10101225

Funmi

8/21/2023

10101226

Nancy

8/22/2023

10101227

Samson

8/23/2023

10101228

Bayonan

8/24/2023

10101229

Nancy

What is the script to write in Collections Manipulation VBO Filter Action to extract only the Hauliers that contain "Nan"?

I have tried the above and many others. But every single script I have written does not work to filter what I want.

Can someone clarify what I am doing wrong and provide me with the correct way to do this?

------------------------------
Yinka Daramola
------------------------------

RE: Filter Collection Problem!

harish.mogulluri — Fri, 18 Aug 2023 19:29:00 GMT

Hi Yinka Daramola,

Try some thing like below. Assuming there are no space before the word "Nan'

"[Haulier] like '"&"Nan*"&"'"

When you step in to the collection action the text should pass to Utility collection - Filter action should be like this [Haulier] like 'Nan*'

Your filter looks like it has single quote before like which is not required also before Haulier column Name there is one more single quote.

https://learn.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=netframework-4.7.2
https://www.csharp-examples.net/dataview-rowfilter/

------------------------------
-----------------------
If I answered your query. Please mark it as the "Best Answer"

Harish Mogulluri
Lead developer
America/New_York TX
------------------------------

RE: Filter Collection Problem!

MichealCharron — Fri, 18 Aug 2023 20:47:00 GMT

@Yinka Daramola

In your question you said you were looking for a filter expression to find Hauliers that contain "nan". That is important here because it the placement of the "*" changes what is filtered.

If you were looking for Hauliers that contain "nan", you would use (4 rows returned):

"[Haulier] LIKE '*nan*'"

If you were looking for Hauliers that start with "nan", you would use (3 rows returned):

"[Haulier] LIKE 'nan*'"

If you were looking for Hauliers that end with "nan", you would use (1 row returned):

"[Haulier] LIKE '*nan'"

When you use the "Filter Collection" action, you are passing the filter string to a DataTable.Select() method. Think of it like a SQL WHERE clause, you specify your column name (the square brackets are not really needed unless you have a space in the column name but it does not hurt to have them there), your operator and then your string to compare to each row value in that column (strings have to be contained in single quotes).

Even the "*" wildcard is interchangeable with "%" like an SQL WHERE clause.

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

RE: Filter Collection Problem!

ydar360 — Fri, 18 Aug 2023 21:35:00 GMT

Hi @Harish Mogulluri. Thank you for taking the time to answer this. But the script you supplied does not work. Unfortunately, Blue Prism just evaluates the expression like text and filters nothing.

------------------------------
Yinka Daramola
------------------------------

RE: Filter Collection Problem!

ydar360 — Fri, 18 Aug 2023 21:51:00 GMT

@Michael Charron. Thank you very much for your detailed answer. I tried running the script as you supplied it on Blue Prism, but it did not work. I had to adjust the script slightly as below before Blue Prism would evaluate it as code rather than text.

I subsequently used a decision stage to test the accuracy, but it does not appear to give the expected outcome.

I wonder what could be wrong?

------------------------------
Yinka Daramola
------------------------------

RE: Filter Collection Problem!

harish.mogulluri — Fri, 18 Aug 2023 22:09:00 GMT

Hi Yinka Daramola,

Not sure why it is not working on your end. Probably need to tweak the condition as Michael Charron mentioned

Check below screenshots
1) Created temp collection
2) Added filter condition as mentioned in my above post
Filter condition used - "[Haulier] like '"&"Nan*"&"'"

3) got the rows "Nan"

The decision stage will treat "*" as text it will not work as wild card that's why you are getting False when you try to compare

RE: Filter Collection Problem!

Mohamad_Dakkouri — Wed, 23 Aug 2023 22:01:00 GMT

Hi, Try

"[Haulier] like '%nan%'"

Regards,

Mohamad DAKKOURI

Tech Lead Rpa

------------------------------
Mohamad DAKKOURI
------------------------------

RE: Filter Collection Problem!

Soumya21 — Thu, 24 Aug 2023 06:49:00 GMT

Hi,

You can give try for this

"Haulier like '%Nan%'"

------------------------------
Soumya k RPA Developer
RPA Developer
Qualesce India Private Limited
Bangalore
------------------------------

RE: Filter Collection Problem!

ydar360 — Wed, 27 Sep 2023 19:13:00 GMT

I set this aside for a while because I was frustrated that it did not work. I resolved the problem using another technique.

I looked at it again today and approached it from another perspective.

It appears that all I really needed to do was remove the square brackets around the column name to get the expression to work.

In other words: "Haulier like '"& [Name]&"'" will do the trick, if the search variable is placed in a data item called Name.

------------------------------
Yinka Daramola
------------------------------