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

[blazino17]

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.

[MichealCharron]

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