¿Is it possible to use functions in Filter Collections?

[CristinaBurgos_]

Hi I am trying to convert the column Created to a DateTime and then compare it to I_UltimaRevisionFiltro (DateTime) and L_Today(DateTime). The problem is that it doesn't filter any data and errors don't appear either.

"Convert([Created],'System.DateTime') > "&"'" &[I_UltimaRevisionFiltro]&"' AND Convert([Created],'System.DateTime') < "&"'" &[L_Today]&"'"

[PvD_SE]

Hi Cristina,

Judging by your example, either the filtering doesn't work at all or all row match the filtering criteria. I'm assuming you've checked there are rows that should be filtered away, so we have a look at the filter parameters. 

By the looks of it, you are trying to convert dates to another format with a Convert(). We're at version 6.10 and Convert() is not allowed in our version or previous versions.

Depending on what you are comparing, their formats should be matching. So you either compare two Date data types, or you compare two Text data types. If you want to go to a Date data type, you can use ToDate() instead of Convert().

Happy coding!
---------------
Paul
Sweden

[CristinaBurgos_]

Thank you for your answer Paul, I've tried using ToDate(), but it answers the next error:

Internal: Could not execute code stage because exception thrown by code stage: The expression contains undefined function call ToDate(),.

The same happens when I use ToDateTime().

Maybe I'm using the expression wrong?

"ToDate([Created]) > "&"'" &[I_UltimaRevisionFiltro]&"' AND ToDate([Created]) < "&"'" &[L_Today]&"'"

[PvD_SE]

Hi Cristina,

It looks like your expression is seen as a comment, it starts and ends with a doubleQuote ("). And as such it will be ignored. 

• What format does you date-field in 'Collection In' have?
• Exactly what action do you use for filtering?
• Can you include a screenshot of your Inputs for the filtering action?

Happy coding!
---------------
Paul
Sweden

[CristinaBurgos_]

• What format does you date-field in 'Collection In' have?

• Exactly what action do you use for filtering?
• Can you include a screenshot of your Inputs for the filtering action?

I have changed the Filter Text multiple times.

• "ToDate([Created]) > "&"'" &[I_UltimaRevisionFiltro]&"' AND ToDate([Created]) < "&"'" &[L_Today]&"'"
• "Convert([Created],'System.DateTime') > "&"'" &[I_UltimaRevisionFiltro]&"' AND Convert([Created],'System.DateTime') < "&"'" &[L_Today]&"'"
• ToDate("[Created]") > "'" &[L_UltimaRevisionFormateado]& "AND "ToDate("[Created]") < "'" &[L_TodayFormateado]&
  
  
  

[PvD_SE]

Hi Cristina,

Your expression for filtering against a given date would look something like this:
The fiddling with the double and single quotes is as the date (text) would need to be between single quotes. Your finals expression would end up like:

With some experimenting you can replace the fixed date ('2023-01-02' in my example) with a data item. I'd suggest you start by running the filter with a simple filtering statement first, as above with only one date. Once you get the hang of it and it works well, then you can add the other dates with AND to complete the equation.

Happy coding!
---------------
Paul
Sweden