- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
18-08-23 07:13 PM
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
------------------------------
Answered! Go to Answer.
Helpful Answers
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
27-09-23 08:13 PM
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
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
18-08-23 08:29 PM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
18-08-23 09:47 PM
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
------------------------------
RBC
Toronto, Ontario
Canada
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
18-08-23 10:35 PM
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
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
18-08-23 10:51 PM
@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
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
18-08-23 11:09 PM
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
------------------------------
If I answered your query. Please mark it as the Best Answer
Harish Mogulluri
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
23-08-23 11:01 PM
Hi, Try
"[Haulier] like '%nan%'"
Regards,
Mohamad DAKKOURI
Tech Lead Rpa
------------------------------
Mohamad DAKKOURI
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
24-08-23 07:49 AM
Hi,
You can give try for this
"Haulier like '%Nan%'"
------------------------------
Soumya k RPA Developer
RPA Developer
Qualesce India Private Limited
Bangalore
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
27-09-23 08:13 PM
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
------------------------------