topic RE: Filtering a collection by date in Product Forum

Filtering a collection by date

johwes — Tue, 06 Aug 2019 11:11:00 GMT

Hi.

I need to filter a collection using today's date. The collection consists of files, all filenames contain month and year but not date. But the create date (with date and time) is listed in a column in the collection. I know which date I want, but the time can be different from time to time, obviously. .
So I created this expression in the filter of the Filter Collection action in Utility - Collection Manipulation:
StartsWith([Files found.Created]; FormatDate(Today(); "dd.MM.yyyy"))

When I evaluate this expression it works perfectly by giving me False on a file from yesterday and True on a file from today. But when running the collection through a loop it ends up writing both files to the Output collection.

Someone then told me that the filter has a specific syntax, but I can't find any information about it. You'd think there was some sort of guide somewhere but apparently not.

So, any good ideas? I gotta say I thought something as simple as searching for a file based on date could be found in the File Management utility but nope. No such luck.

------------------------------
Johanna Westlund
RPA manager
MAIK AS
------------------------------

RE: Filtering a collection by date

rajeshkumarmuthineni — Tue, 06 Aug 2019 13:43:00 GMT

Hi Johanna,

Here are couple of examples to Filter collection which can be found in Process Examples - Collections

"[Product Type]='Vegetable'"

"[Product Name] LIKE 'P%'"

Thank you,
Raj

------------------------------
Rajesh kumar Muthineni
RPA Developer
Highmark Health
America/Chicago
------------------------------

RE: Filtering a collection by date

johwes — Wed, 07 Aug 2019 06:26:00 GMT

Thank you for the examples, I appreciate it. But can you give me an idea of what it would look like if you need to filter a specific column in the collection according to a data item?
This works: "[Created]='05.08.2019 13.20.28'" It finds the one row that has this exact date and time in it. But I need it to filter according to the value in a another data item containing today's date.
But if it try "[Created]=[Todays date]" It tells me it can't find the column "Todays date" and if I try "[Created]='[Todays date]'" I get:"ERROR: Internal : Could not execute code stage because exception thrown by code stage: Cannot perform '=' operation on System.DateTime and System.String."

Is there any way around this?

------------------------------
Johanna Westlund
RPA manager
MAIK AS
------------------------------

RE: Filtering a collection by date

GopalBhaire — Wed, 07 Aug 2019 07:07:00 GMT

From what I understand from your question you're trying to filter out Datetime if so try this in filter, Data 2 is Datetime from and Date 3 is Datetime to.

"Created >'"&[Data2] &"' and Created<'"&[Data3] &"'"

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

RE: Filtering a collection by date

rajeshkumarmuthineni — Wed, 07 Aug 2019 13:24:00 GMT

Also Try this one "[Date] LIKE "&"'"&[Today]&"%'". Tested its working

Collection name - COL and Fieldname - Date

------------------------------
Rajesh kumar Muthineni
RPA Developer
Highmark Health
America/Chicago
------------------------------

RE: Filtering a collection by date

SandeepNalla — Wed, 07 Aug 2019 16:16:00 GMT

Set Date1 to Today's Date
Set Date2 to Tomorrow's Date

Set Date1: FormatDateTime(Today()&" "&LocalTime(),"MM/dd/yyyy")

Set Date2: FormatDateTime(DateAdd("9", "1", Today())&" "&LocalTime(),"MM/dd/yyyy")

Use this in the filter condition

Filer Condition: "[Created] > "&"'" &[Date1]&"' and [Created] < "&"'" &[Date2]&"'"

This filter the files which are created Today. If you want to filter the files with different dates, set Date 1 and Date 2 to those dates.

Hope this helps.

------------------------------
Sandeep Nalla
Senior Consultant
Lateetud Inc
America/New_York
------------------------------

RE: Filtering a collection by date

johwes — Wed, 14 Aug 2019 12:31:00 GMT

Thank you @SandeepNalla. I tried this but get the following error: "Could not execute code stage because exception thrown by code stage: Cannot perform '<' operation on System.DateTime and System.String."

------------------------------
Johanna Westlund
RPA manager
MAIK AS
------------------------------

RE: Filtering a collection by date

SandeepNalla — Wed, 14 Aug 2019 13:03:00 GMT

Date1 and Date2 data items should be of type "date time". I think you have them as text.

------------------------------
Sandeep Nalla
Senior Consultant
Lateetud Inc
America/New_York
------------------------------

RE: Filtering a collection by date

johwes — Wed, 14 Aug 2019 13:04:00 GMT

Hi @Rajesh Kumar. I get this error message. Any idea?

------------------------------
Johanna Westlund
RPA manager
MAIK AS
------------------------------