How can I set up a recurring initial filter on my report than only shows forms submitted after 1am on Monday

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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!


Thanks!

Photo of Barnee

Barnee

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Can you give some examples of records which would meet the test but you're trying to set up on your filter and some examples of records that would not meet the test?

I'm trying to understand what you're asking for.
Photo of Barnee

Barnee

  • 0 Points
No problem.

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
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Try this as a formula checkbox field, called perhaps [This week?] and then apply it as your report filter.



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)
Photo of Barnee

Barnee

  • 0 Points
This has done it! Thank you! Perfect
Photo of Barnee

Barnee

  • 0 Points
Hi again! Unfortunately I have just noticed the formula has not worked as I planned!

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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
That was my intent with the formula. But I must have an "off by one" type logic flaw. I will have a look today sometime.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
I've stared at the formula for a while and it looks right to me, but I have not tried to test it.  

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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
New apps default to Pacific Time zone unless you change the default in your Account settings.
Photo of Barnee

Barnee

  • 0 Points
Hi - thank you for your commitment to helping me! I'll answer all your questions:

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)
Photo of Barnee

Barnee

  • 0 Points
Correct me if I'm wrong, but could the problem not be in the FirstDayOfWeek?

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:

On Saturday:
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

On Sunday:
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
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
But my formula adds one day.
Photo of Barnee

Barnee

  • 0 Points
Correct. So this formula will work 6 days of the week, but the 7th it will not
Photo of Barnee

Barnee

  • 0 Points
Because the value of $WeekStart will be one day in to the future
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Ok, I'm back at my computer now.  I was in transit on a subway with not much internet when I last posted, and  I did not fully read your last posts.  Thank you for working through that example and answering my questions.  Yes I agree that previous formula was less than perfect.  Fortunately for you, these things haunt me.

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.

Tick Tock.
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.

Tick Tick
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.
Photo of Barnee

Barnee

  • 0 Points
Awesome! I've plugged it in and it seems to work! I'll let you know if I notice anything funny

Thanks so much for helping out! I'm trying to learn more about formula writing myself, so this is really helpful for me
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Thx for letting me know.  I assume that being the diligent type that I know you are, you will sign on at 11:59 Sunday night to watch the screen go empty as you hit F5 refresh repeatedly the as the clock counts down to cinderella pumpkin time.
Photo of Barnee

Barnee

  • 0 Points
You bet I will!
Photo of Barnee

Barnee

  • 0 Points
A Sunday night well spent
Photo of Barnee

Barnee

  • 0 Points
It has worked swimmingly.

Thank you!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Ah,  It's Monday again.  Funny how Mondays seem to keep happening each week.  Thx for letting me know.