Filtering a collection by date

[johwes]

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

[rajeshkumarmuthineni]

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

[johwes]

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

[GopalBhaire]

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

[rajeshkumarmuthineni]

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

[SandeepNalla]

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

[johwes]

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

[SandeepNalla]

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

[johwes]

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

​

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