cancel
Showing results for 
Search instead for 
Did you mean: 

Filter Collection Problem!

ydar360
Level 3

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

24503.png

24504.png

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
------------------------------
1 BEST ANSWER

Helpful Answers

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

View answer in original post

8 REPLIES 8

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
------------------------------
-----------------------
If I answered your query. Please mark it as the Best Answer

Harish Mogulluri

MichealCharron
Level 8

@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
------------------------------
Micheal Charron
RBC
Toronto, Ontario
Canada

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.

24463.png



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

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

24466.png



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

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
24473.png2) Added filter condition  as mentioned in my above post
 Filter condition  used - "[Haulier] like '"&"Nan*"&"'"
24474.png

3)  got the rows  "Nan"

24475.png



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
------------------------------
-----------------------
If I answered your query. Please mark it as the Best Answer

Harish Mogulluri

Hi, Try

"[Haulier] like '%nan%'"

Regards,

Mohamad DAKKOURI 

Tech Lead Rpa



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

Soumya21
Level 6

Hi,

You can give try for this

"Haulier like '%Nan%'"



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

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