Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Please can someone assist?
I have an event table with 2 date fields – dStart and dEnd. I want to select all the records where dEnd-dStart >180 (i.e. events that took longer than 180 days) in a way that high-level users can access simply (like a bookmark).
I don't really want to create an extra ‘claim duration’ field at the outset (I know this would be an answer, but I'm trying not to bloat the field list too much as this confuses the users). I’ve been looking into adding a button as that gives me the ability to set up a whole ‘report scenario’ including the duration limitation.
The ‘Select in Field’ action lets me define an expression:
=interval(dEnd-dStart,'D')
But a Search String:
>180
Apparently does nothing. Is this just a syntax issue, or is there a fundamental reason why this doesn't work? I've been trawling the discussions and can't find a match for this scenario...
Thanks for any help you can give!
Hi,
Did you try to use the '$' Symbol (if dEnd and dStart is a variable), Like
=interval($(dEnd)-$(dStart),'D')
or
=interval(num(dEnd)-num(dStart),'D')
or Directly you will get difference value, if it is a date format..
=num(dEnd-dStart)
Hi. Thanks for the input, but I got the answer from a colleague in the end. For the benefit of anyone else who's a bit of an amateur like me :-), I'll explain...
I was thinking of the FIELD as being the data I was comparing (one or both of the dates), as opposed to the unique data I could use to identify the records I wanted to select. The solution was simply a Button with a Select in Field action as follows:
FIELD: event_id
SEARCH STRING: ='=IF(interval(dEnd-dStart,"D")>180,event_id)'
Sorted. Regards to all!