Advanced Search - Dynamic Date Filtering

#1

Hello - I have been playing with trying to do an offset of sorts when we are using dates in the advanced search feature. What I am trying to do is say if an order was entered older than 20 days than show me those orders. I can do this by manually changing the date every time I log into the report, but I was hoping there is a way to get this date filter to change dynamically.

Nick Deccio

0 Likes

#2

I am fairly certain this was discussed in another thread on the forum, the answer was quite technical if I recall correctly.

0 Likes

#3

I ended up figuring out a work around. I went into the SQL on the backend and added sysdate and another instance of the the entered date. I then used an expression in the DB Designer and subtracted sysdate and enterdt. I then used the expression as a property and converted it to a number and basically filtered by the moving difference between sysdate and in my case the entered date. Definitely not pretty, but effective.

1 Like

#4

Hi @ndeccio. This is a slightly more complex issue, as from what you’ve already gathered, the value must exist in order to reference/calculate from it. It sounds as though you’ve found a way to do this, but if it helps at all, this may be how I would have approached it.

The easiest way to do as you’re asking is probably to either include an additional calculated field on the data extract (ie SQL query) or add a transform column within the database design in order to generate the required value (assuming you have access to designer - if not you may need to arrange Phocas to undertake such a change for you)

For example within the design you could add a transform column that effectively ran a case statement over the date and evaluated against your criteria. This does assume you are evaluating a date type field, as otherwise you would need to convert before evaluating. You would end up with something along the lines of;

CASE WHEN [DateEntered] < dateadd(d, -20, Getdate()) THEN 1 ELSE 0 END

This would show all records with a date entered greater than 20 days ago as having a 1. Everything newer would be 0.

If however you wanted to evaluate every date so you could reference the age as a property, then you could do the following;

DATEDIFF(day,[DateEntered],getdate())

This could then be mapped to a property and formatted as a number so you could use in calculations,. You should then be able to use this within your search.

I hope this helps!

Regards,

Lee Konrath

1 Like

#5

That works beautifully. Thank you Lee.

0 Likes