Can I Change the Reporting Criteria for Clicking on A Date In A Calendar Report?

  • 0
  • 2
  • Question
  • Updated 2 years ago
  • In Progress
We use QB to track events and the scheduled days for those events. On the admin homepage, there is a calendar report based on the "start" and "end" dates for each event, showing the current month of events. However, as events are not always on concurrent days, the "end" date does not necessarily represent every day of the event (I do not want to use the last scheduled date as the "end date" because then an event on the calendar will show as spanning all those days, whether the event is actually on that day or not).
The problem comes in when one of my users clicks a date on the calendar to display all events on that date (using the start and end dates) but this does not include any events with non-concurrent dates (because they cannot be part of the start-end duration). Is there a way to change these reporting criteria? (similar to a drilldown report for a graph or chart, I think). I want to be able to go through all the scheduled days and see if one lands on the chosen date.
Photo of rhuffman

rhuffman

  • 328 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 2
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Do you already create 2 separate events when there is a day 'off' during the event?

If not, I do not know of a way to indicate that it is NOT scheduled for those days and in affect showing on any report filtering.
Photo of rhuffman

rhuffman

  • 328 Points 250 badge 2x thumb
No, we do not split the event up. The "event days" table (children to an "event") handle the specific days of the event. I originally split the events and event days up like that because in my limited experience with Quickbase, that seemed to be the only way to have non-concurrent days to a single event and now I've run into this different issue with reporting.
Unless you build your calendar report on the child table and create individual records for each day, for each event, even those that are only one day, quickbase does not have a native way of doing this.

We have a scheduling / calendar tool that gives you a much better and more powerful calendar interface and could handle these sort of events.

If you would like to see a demo and/or discuss, my contact info can be found in my profile.

Thanks,

Eric
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
Here is an idea.

Make a new table with one record in it. It will be [Record ID#] of 1. Put fields in that record for the start and end date. This will be the date selector table.

Make a field in each detailed record of the event dates (those child recO do) and have the formula calculate to 1.

Then make a relationship back to the date selector table.

Lookup the start and end dates from the date selector table to the child table. And make a formula which calculates to true if the dates on the child record falls within the dates selected.

Make a summary field on the main event echoed of its number of children dates which fall between the start and end dates.

So now you can go to the date selector record and enter your dates and get a report of qualifying events. That report can even be embedded in the date selector so that as soon as you save the start and end dates, then the qualifying events will be right in that record as an embedded report.