I have a request from the owner of the company I work for. We have a view that holds phone calls, with date and time etc.
Here is the selector he wants:
When I click on November 14th, for example...he will see the "overnight shift" - ie, only those calls that came in after 10 PM and before 8 AM on the 15th.
Now, I have to assume this is impossible, because if I have already selected the 14th, the 15th is filtered out, by definition. Even if he has to select both the 14 and 15th, that might be acceptable....but then he will get 10PM-midnight on the 14th and 15th and midnight-8AM on the 14th and 15th...when he wants the single overnight period only with one single click. (I even gave him the date/hour combination so he can manually select the overnight hours, but he is convinced something more can be done.)
I am wracking my brains but cannot think of any way to achieve this. Can it even be done?
It is surely possible with set analysis. Your expression for calls would ignore the selection of the date, and use a "calculated selection" which is based on the actiual date selection.
One way to solve this is to create a second calendar (joined to the main one, or kept in another table linked by a key field) with the appropiate offset so as to match your shift times. You need to match each date and shift time period combination to its corresponding date in the calendar.
It may not be easy to understand on the fly. Just try to get the idea and apply it in a test application to see the effect. It worked well for me in a similar case.
Thank you for your response - I learned Calendar items in a training course last summer but have never used one so they did not occur to me. I will definitely explore that option further.
