HOW TO USE FILTER ON A COLLECTION STAGE

[herminio217]

During the course of learning, we always find something interesting and think: "Do other people know about this?". And even if it's something simple, I decided to share it...

HOW TO USE FILTER ON A COLLECTION STAGE (BLUE PRISM)?

1 - Create an Action Step.
2 - In the Business Object select the option "Utility - Collection Manipulation" (dark blue) and in the Action select "Filter Collection" (dark blue).
3 - In Inputs select the Collection you want to filter (light blue).
4 - Create an expression that must be placed between Strings " " (red) as a reference to what will be filtered and from which column. If it is a text, enclose it in single quotes ' ', if it is a number, just insert it.
5 - In Outputs select a Collection where the filtered data will be stored (dark green).

#BPTechTips
------------------------------
Leonardo Hermínio
------------------------------
​

[RobertNew]

Hi Just to add, if the column name is two separate words then it need to be in square brackets, i.e. [Left column]

also; when using variables its important to know the following; (its for text but you can experiment with it)

"[Column Name] = "'&[Variable]&"'"

""&&[Variable column name]&" = 'text'" or  ""&&[Variable column name]&" = '"&[Variable]&"'"

you can also swap out = for, <> or Like etc.

experiment with it, it will be slightly different with numbers only.

------------------------------
Robert New
Senior Mentor / Developer
Smart Automation Services Limited
Europe/London
------------------------------

[EmersonF · Accepted solution]

very good, I've seen countless people asking about the topic, in addition to what Robert said, I think it's as important as mentioning that it can be used in a loop, or even made a series of one-time validations, such as "[Sexy] = 'M' And [Name] like 'John%'

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
If my answer helped you? Mark as useful!
------------------------------

[TobiasArnold]

Hi all,

I recommend reading the general expression guide: https://docs.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=netframework-4.7.2
Here are a few examples (e.g. datetime query, etc.): https://www.csharp-examples.net/dataview-rowfilter/

------------------------------
Tobias Arnold
RPA Developer
ITERGO GmbH
Europe/Duesseldorf
------------------------------

[AlexanderBopp]

Many thanks @Robert New for saving me some time.
Just to give others a further example, this is the expression that worked for me:  "ColumnName = '"&[Data Item Variable]&"'"​
In the Expression Builder click "Validate" to ensure your filter expression syntax is correct.

------------------------------
Alexander Bopp
Business Automation Engineer
Credit Suisse AG
Europe/Zurich
------------------------------

[LinaRocio]

Leonardo como estas?

Estoy realizando la acción de Filter collection, pero me arroja el siguiente error:


Tengo 2 colecciones, la primera es donde esta la información que quiero obtener, por medio de un dato llamado "numero_radicado"

La segunda es donde esta el numero_radicado que es con el que voy a buscar en esa colección:

Agradezco de su ayuda, para solucionar esto.

Gracias!!

------------------------------
Lina Rocio
------------------------------

[PabloSarabia]

Buenas @LinaRocio

Cuando uses la "Filter Collection", la expresión debe indicar directamente el nombre de la colección. 

Te debería de quedar algo como esto:

"[numero_radicado] = '" & [Datos Comunitarios.numero_radicado] & "'"

La expresión, cuando indicas campos de la misma collection, ponlos entre corchetes. Pero si vas a incluir datos externos, como el numero_radicado de la colección Datos Comunitarios, al usar el valor propio, si debes indicarlo de manera exacta.


Espero que esto te ayude.

Nos vemos en la comunidad 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
------------------------------

[LinaRocio]

Muchas Gracias Pablo, ya me funciono.

------------------------------
Lina Rocio
------------------------------

[JagadishPatro]

When filtering a collection in Blue Prism, there are certain characters that may cause issues or behave unexpectedly within the filter expressions. Here are some characters that you should be cautious about when using the filter collection action in Blue Prism:

1. Square brackets: "[" and "]"
2. Asterisk: "*"
3. Question mark: "?"
4. Percentage sign: "%"
5. Forward slash: "/"
6. Backslash: ""
7. Greater than: ">"
8. Less than: "<"
9. Ampersand: "&"
10. Pipe: "|"
11. Equal sign: "="
12. Plus sign: "+"
13. Minus sign: "-"
14. Double quotes: """
15. Single quotes: "'"
16. Colon: ":"
17. Semi-colon: ";"
18. Comma: ","
19. Dot/Period: "."
20. Parentheses: "(" and ")"

These characters are commonly used as operators or have special meanings within filter expressions, which can lead to unexpected results or errors. To handle these characters, you can use escape sequences or alternative filter expressions, as mentioned in the previous response.

It's important to carefully consider the specific characters used in your data and adjust your filter expressions accordingly to ensure accurate and reliable filtering in Blue Prism.

------------------------------
Jagadish Patro
------------------------------

[MikkoKamppila]

Just a warning - if your collection includes in a first column a string that resembles a date, it's possible the order of rows is changed after filtering. Issue is possibly not limited to dates only (maybe it's just the numbers, after all?), but it's the only case I know.

In my demonstration, data is in a collection called "Test" and the result collection populated by a filtering query is "Res".(See the pics.)

If the filtering query is "[A] LIKE '%2%'", the result set contains all the rows in the dataset, which is correct, but as you can see, order of rows has changed. This could have an effect to your automation, if the order of rows in relevant to your use case. 

Only way to circumvent the issue (that I know), is to add a indexing to the dataset and after the filtering, order the rows again by index.

------------------------------
Mikko Kamppila
IT analyst
Kela
Europe/Helsinki
------------------------------