Filter Collection Problem!

[ydar360]

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
------------------------------

[harish.mogulluri]

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
------------------------------

[MichealCharron]

@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
------------------------------

[ydar360]

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
------------------------------

[ydar360]

@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
------------------------------

[harish.mogulluri]

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

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

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

[Mohamad_747]

Hi, Try

"[Haulier] like '%nan%'"

Regards,

Mohamad DAKKOURI 

Tech Lead Rpa

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

[Soumya21]

Hi,

You can give try for this

"Haulier like '%Nan%'"

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

[ydar360 · Accepted solution]

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
------------------------------