Hi Quickbase Magicians,
I would like to be able to set up a recurring filter on my report that only shows forms submitted after 1am on Monday.
I had a fiddle around with a bunch of things and couldn't find a clear cut way to do it. Perhaps you guys can help!
The date is 11/18/15 (a Wednesday)
A form is submitted on 11/14/15 (a Saturday).
This form IS NOT shown on the report, because it was submitted before 1am on the Monday of the current week (11/16/15)
A form is submitted on 11/17/15 (a Tuesday)
This form IS shown on the report, because it was submitted after 1am on the Monday of the current week (11/16/15)
Let me know if you have any further questions. Hopefully this explains it a bit more thoroughly
var date DayStart = FirstDayOfWeek(Today())+Days(1);
var timeofday OneOclockAM = ToTimeOfDay("1:00 am");
var datetime WeekStart = ToTimestamp($DayStart,$OneOclockAM);
If([Date Created] > $WeekStart, true)
I should have explained myself better... My goal is to have the report 'clear itself' on every Monday at 1am.
Because Quickbase sees Sunday as the first day of the week, the formula has now recalculated itself for the current week and has cleared the report prematurely.
Is there a way we can do this?
Can you tell me what day of the week and time of day you looked at the report when it cleared itself prematurely? By the time stamp on your post I'm guessing that it was late Sunday evening, but before 1:00 am Monday morning. Is that right?
Are you using my exact formula?
Just to confirm, you want to base this on the [Date Created] date/time of the record?
Lastly, and this may be the issue. What time zone is your application set to compared to what time zone you are in? You set the application time zone on application properties, Advanced Tab.
I'm not sure the exact time it cleared itself. But I noticed it around 9am Sunday morning.
I am using your exact formula
You are correct in assuming I want to base it on the [Date Created] date/time of the record
The time zone is correct for me: (UTC-08:00) - Pacific Time (US & Canada)
Quickbase see's the first day of the week to be Sunday. So on Saturday the value of FirstDayOfWeek would be 11/15/15, but when it got to Sunday the value changes to 11/22/15.
This will have a knock on effect to the rest of the formula:
var date DayStart = 11/16/15
var timeofday OneOclockAm = 1am
vardatetime WeekStart = 11/16/15 1am
If([Date Created] > 11/16/15 1am, true
At this point all my forms created during the week will be true
var date DayStart = 11/23/15
var timeofday OneOclockAm = 1am
vardatetime WeekStart = 11/23/15 1am
If([Date Created] > 11/23/15 1am, true
At this point none of my forms created during the week will be true
I think that tis is the correction for the first formula variable.
var date DayStart = FirstDayOfWeek(Today()-Days(1))+Days(1);
So lets try it.
It's now last Wednesday Nov 18th as "Today". Last Wednesday - 1 day = Last Tuesday Nov 17. First Day of Week snaps back to last Sunday Nov 15. Add Days 1, and we are at last Monday Nov 16. So all records since last Monday Nov 16 at 1:00 will show and its the 18th so they will show.
Tick tock. It's now Saturday Nov 22nd as Today. Saturday - days 1 = Friday which first day of week snaps back to Sunday Nov 15th. Add days 1 and the cut off is still Monday Nov 16. So we see all records after Nov 16th. Good.
Its now Sunday Nov 23rd as today. Sunday - Days 1 = Saturday. First day of week snaps back to last Sunday Nov15th. Add days 1 and the cut off is still Monday Nov 16. Still good.
Its now Monday Nov 23 at 7:00 am. Monday - Days(1) = Sunday Nov 22nd. First day of week has NO snapback. It calculates to Sunday Nov 22. Add days (1) and we are at Monday. A [Date Created] before Monday 1:00 am will now be OFF the report.
I think that there is still a small flaw in perfection that if a ticket is entered at Monday right after midnight, then it should "snap back" to the previous Sunday 8 days ago, (but it won't). But, I suspect that the 1:00 am was arbitrary on your part and in reality no one is using the system at that time, so I won't hurt my brain to get to perfection on tickets entered in that 1 hour window.