Solved How to use Filter collection(Utility - collection manipulation)

Within a loop, I want to use filter collection, for every iteration I need to change the filter criteria, please suggest how to achieve this
I have tried all the below combination but its not working :
Collection In [ExcelData]
Filter "Location = 'India'" : its working fine

Collection In [ExcelData]
Filter "Location = '[Data.Location]'" : not working (Data.Location is an another collection)

Collection In [ExcelData]
Filter "Location = [Data.Location]" : not working (Data.Location is an another collection)
 

Attachments

  • Location.PNG
    17.2 KB · Views: 1,850

VJR

Well-Known Member
Yes, it is just like concatenation of variables used in any programming language.
In some languages '+' is used, while in some '&' is used.
In BP, the concatenation operator is '&' .

Also, in this case single quotes is used because it is a Text data item
Else for a Number it would be only-
"Location = " & [Data.NumberDataTypeColumn]
 

Mwacuka

New Member
Hi VJR

I would like to use filter collection (Utility - collection manipulation), I have 2 collections, MainFileData collection that contains entries that need to be filtered based on the unique records stored in the second collection, UniqueManagersCollection. I am looping through the MainFileData collection as shown in my process diagram. I have followed your advice and set the filter as: "Manager ID ='"&[UniqueManagersCollection.ManagerID]&"'", but am getting the following error:
Internal: Could not execute code stage because exception thrown by code stage: Syntax error: Missing operand after "ID" operator.

I have validated my expression and it is valid.
 

Attachments

  • Process Diagram.PNG
    14 KB · Views: 1,187
  • Filter collection action description.PNG
    30 KB · Views: 1,485
  • Error message.PNG
    9.2 KB · Views: 964

geeda

New Member
Hello

I am trying to filter the data in two excel sheets(E1, E2) based on ID, Date and Hours. Please help me with the logic here :

"[EE ID]= "& [Crew_Member_ID]&" AND [Date Absent]=" & [Time_Off_Date] & " AND[Sedgwick Hours]=" & [Units_Requested]
 
Hello All,

How to use 'InStr' method in Filter collection action.

I want to filter 'Images' Collection by 'Name' column with 'SaaSApps' collection by passing 'Image Name' as filter condition. Both column values are not exactly same.

Please find images for reference.

Thanks in advance.
 

Attachments

  • Images Collection.png
    16.1 KB · Views: 779
  • Saas Collection.png
    8.6 KB · Views: 670

tehritarun

New Member
Hi all

I want to use wildcard in filter collection action.

I want all records with vendor name start from FH. as startswith() function doesn't work with filter action. I thought I will use wildcard instead.

So I used filter:
"Vendor = 'FH*'"
And it is not working for me..
Is there any other way to use wildcard or filter likewise condition??
 

nbargaje

Member
Yes, it is just like concatenation of variables used in any programming language.
In some languages '+' is used, while in some '&' is used.
In BP, the concatenation operator is '&' .

Also, in this case single quotes is used because it is a Text data item
Else for a Number it would be only-
"Location = " & [Data.NumberDataTypeColumn]


Hi VJR,

if i need to filter for 2 values, how we can do that.
i tried "[column Name]='"&2"' AND '"&5&"' " but i am getting error at actual execution.

assume 2 and 5 are text data type.

Thanks,
Nitin
 

gil.silva

Active Member
Hi VJR,

if i need to filter for 2 values, how we can do that.
i tried "[column Name]='"&2"' AND '"&5&"' " but i am getting error at actual execution.

assume 2 and 5 are text data type.

Thanks,
Nitin
Hello nbargaje,
The correct syntax would be:
"[column Name] = '&2' AND [column Name]= '&5&' "
 

khalsa96

New Member
one more small help, any idea how can filter using flag data type, want to filter unread emails. i tried below but failed.

1. "[Unread]"=True"
2. "[Unread]"= [data1]---------which has value true.


Thanks,
Nitin


If the column type is bool then you can operate with 1 and 0
 

gediminaskv

New Member
Hi all

I want to use wildcard in filter collection action.

I want all records with vendor name start from FH. as startswith() function doesn't work with filter action. I thought I will use wildcard instead.

So I used filter:
"Vendor = 'FH*'"
And it is not working for me..
Is there any other way to use wildcard or filter likewise condition??

Hi, use LIKE instead of equal sign as mentioned https://www.rpaforum.net/threads/filter-collection.7810/
 

vmaneesh

Member
I have to filter a collection by a column name that has a standard value and a column name that is coming from a text data type.
Ex: Filter by [Col1] = "Hello"] AND [Col2] = "[Hello.Hi]" . Here Hello.hi is a text data item.
 

ppb12345

New Member
Yes, it is just like concatenation of variables used in any programming language.
In some languages '+' is used, while in some '&' is used.
In BP, the concatenation operator is '&' .

Also, in this case single quotes is used because it is a Text data item
Else for a Number it would be only-
"Location = " & [Data.NumberDataTypeColumn]



thank you VJR how to modify this filter criteria to accommodate a string value that starts with certain characters
so for example if there are 3 values in one column (abc, abc1,abc 2) my filter criteria should fetch all 3 values.
please let me know
 

rizwanaanjum

New Member
i need to filter the column nature as Booked and Last Year,used utility -collection manipulation in the input filter AND operation doesn't work as it is text data type

"[Nature] = 'Booked' AND [Nature]= 'Last Year Transcation' "
 
Top