topic RE: HOW TO USE FILTER ON A COLLECTION STAGE in Product Forum

HOW TO USE FILTER ON A COLLECTION STAGE

herminio217 — Fri, 04 Jun 2021 10:56:00 GMT

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

RE: HOW TO USE FILTER ON A COLLECTION STAGE

RobertNew — Fri, 04 Jun 2021 12:27:00 GMT

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

RE: HOW TO USE FILTER ON A COLLECTION STAGE

EmersonF — Fri, 04 Jun 2021 12:48:00 GMT

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

RE: HOW TO USE FILTER ON A COLLECTION STAGE

TobiasArnold — Fri, 04 Jun 2021 12:50:00 GMT

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

RE: HOW TO USE FILTER ON A COLLECTION STAGE

AlexanderBopp — Tue, 08 Feb 2022 21:37:00 GMT

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

RE: HOW TO USE FILTER ON A COLLECTION STAGE

LinaRocio — Thu, 24 Mar 2022 17:38:00 GMT

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

RE: HOW TO USE FILTER ON A COLLECTION STAGE

PabloSarabia — Thu, 24 Mar 2022 17:42:00 GMT

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

RE: HOW TO USE FILTER ON A COLLECTION STAGE

LinaRocio — Thu, 24 Mar 2022 17:54:00 GMT

Muchas Gracias Pablo, ya me funciono.

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

RE: HOW TO USE FILTER ON A COLLECTION STAGE

JagadishPatro — Thu, 18 May 2023 10:38:00 GMT

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:

Square brackets: "[" and "]"
Asterisk: "*"
Question mark: "?"
Percentage sign: "%"
Forward slash: "/"
Backslash: ""
Greater than: ">"
Less than: "<"
Ampersand: "&"
Pipe: "|"
Equal sign: "="
Plus sign: "+"
Minus sign: "-"
Double quotes: """
Single quotes: "'"
Colon: ":"
Semi-colon: ";"
Comma: ","
Dot/Period: "."
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
------------------------------

RE: HOW TO USE FILTER ON A COLLECTION STAGE

MikkoKamppila — Thu, 01 Jun 2023 11:03:00 GMT

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

Re: RE: HOW TO USE FILTER ON A COLLECTION STAGE

blazino17 — Thu, 07 Nov 2024 14:07:20 GMT

How would i Filter this collection for a Posted date range from 1st sept2024 to 30 sept 2024 and also how would i filter the debit column for > 10000 and < 10000.. I have tried what i can but it is not working . please assist.

Re: RE: HOW TO USE FILTER ON A COLLECTION STAGE

MichealCharron — Fri, 08 Nov 2024 19:47:30 GMT

@blazino17
You might have to deal with two different issues here. If you are looking for the rows within all days within a specific month, you can simply use the text in the Posted Date column to look up a specific month as with the following:

[Posted Date] LIKE '*-Jul-24'

The Debit has to handled a little more carefully because you have those "----" characters in that column. You'll have to convert the text to number but convert the "----" characters to zeros like so:

CONVERT(IIF([Debit]='----', 0, [Debit]), System.Decimal)<10000

So putting it all together you would have the following in your filter parameter:

"[Posted Date] LIKE '*-Jul-24' AND CONVERT(IIF([Debit]='----', 0, [Debit]), System.Decimal)<10000"

If you are looking to go between two specific dates it gets a little more complicated. If your regional date format is "dd-mm-yyyy", as shows in your collection you could use:

"CONVERT(Posted Date], System.DateTime) >='" & [Start Date] & "' AND CONVERT([Posted Date], System.DateTime) <='" & [End Date] & "' AND CONVERT(IIF([Debit]='----', 0, [Debit]), System.Decimal)<=10000"

But if you are unlucky as I am, you would have to do some rearranging of the date to match the regional format (my regional format is "yyyy-mm-dd"). I would have to use the following:

"CONVERT(IIF(LEN([Posted Date])=9, SUBSTRING([Posted Date],8,2) + '-' + SUBSTRING([Posted Date],4,3) + '-' + SUBSTRING([Posted Date],1,2),SUBSTRING([Posted Date],7,2) + '-' + SUBSTRING([Posted Date],3,3) + '-' + SUBSTRING([Posted Date],1,1)), System.DateTime) >='" & [Start Date] & "' AND CONVERT(IIF(LEN([Posted Date])=9, SUBSTRING([Posted Date],8,2) + '-' + SUBSTRING([Posted Date],4,3) + '-' + SUBSTRING([Posted Date],1,2),SUBSTRING([Posted Date],7,2) + '-' + SUBSTRING([Posted Date],3,3) + '-' + SUBSTRING([Posted Date],1,1)), System.DateTime) <='" & [End Date] & "' AND CONVERT(IIF([Debit]='----', 0, [Debit]), System.Decimal)<=10000"

It gets a lot more complicated because the day is not zero padded in the text.