Trying to create a weekend agenda report that has two constraints: The start date OR the end date occurs on the current weekend.

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered
  • 0
  • 1
Report Goal: User inputs the Friday for the weekend they would like to see what items are Starting or Ending during that weekend (Saturday, Sunday, Monday).

So Far: I've set up the <Ask the User> to input both the start/end dates. I've also set up a formula field that looks at the Start and End Dates and assigns the day of the week to them. 

Issue: I cannot find a way to indicate ONLY DURING THE WEEKEND INDICATED between the start and end dates and have it be available any time based on the start and end dates input by the user.

For now, the report is pulling properly if you pull it during the week before the next weekend, but if you were to pull it over the current weekend (say on Sunday), you will not see the items from Saturday. The other issue is that even if I put in the dates from last weekend it still only shows this weekend's information because of the second line in my report criteria (7 days in the future - which always means 7 days from TODAY, not from the date indicated by the user). Is there a way to do + or - 4 days from date indicated?

Thanks for any creative ideas on making this happen!! I've included a screen shot on how I've set up the criteria, I probably would be able to make this happen with one more level down but Quickbase does not want me to go there!

Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
4 years ago
I will answer as a comment for now, but let me know if it works and I will upgrade it to an answer.  i have not hurt my head fully with your details, but you logic is getting very complex and maybe you are passed where you can use an <ask the user. as you need to do calculations off the <ask the user> which you cannot do so easily.

I suggestion to consider is to make a single table called Date Settings with a single record.  Add that 1 record to the table.  Make date fields for any parameters that you like.  It will be [Record ID#] of 1.

Then in your details table make a field called [Link to Dates] and have a numeric field with a formula of 1.  Then make a relationship back to the Date Settings table and bring down your date setting fields as lookups and use them in your report filters. Once you get that working to get the report results you need, you can use the Report Link field in the relationship and put that field on the date settings record and put your report there as an embedded report.  Then when you save the date settings the report will reflect those filters right on that date settings record.make a report link field
4 years ago
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
I think I was making it more complicated than it needed to be, but the logic of the other solution helped immensely. I will post the answer now.
4 years ago
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
To answer my own question for the community:::

And using this solution as a great starting point: https://quickbase-community.intuit.com/questions/1170581-display-todays-people-on-vacation-when-date...

I've created 4 constraints for <ask the user> essentially asking, what is your Start Date Min. Date, Start Date Max. Date, End Date Min. and End Date Max.

Then on the back end I've only included dates that have Sat. Sun. or Mon. as their Day.

The report than shows anything that falls into the Start Date Min-Max OR End Date Min-Max.

Here's what it looks like in the report backend and for the user - not the prettiest, but it is adaptable to select multiple weekends at a glance whenever it is needed (not only showing THIS weekend).
  • 0
  • 1

Powered by Get Satisfaction