can find the status of a date on the calendar of scheduled events?

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

my employee has scheduled :

  • July 1- July 5 as sick time
  • July 14 - July 16 as vacation time.

i've got them displaying nicely on a std. quickbase calendar.

for a different report - how can i grab the status of any date in July?

  • if i ask for July 3, it should return <SICK>
  • if i ask for July 6, it should return <blank>
  • if i ask for July 15, is should return <VACATION>

it would be AWESOME to get it done in one function, but i'll live if it's multiple that i have to lump together for a single status value.

so checkbox values would be fine, too.


Photo of Paul


  • 52 Points

Posted 5 years ago

  • 0
  • 1
Photo of QB_Support_Brian


  • 40 Points
Hi Paul,

You could set up a table report with 2 filters:

(Start Date) (is on or before) (_ask1_)

(End Date) (is on or after) (_ask1_)

However that would be a little clunky as the user would need to enter the date twice, once for each filter. Perhaps one of the developers here has some thoughts on how a custom script could be used to smooth out the process a bit.


Photo of Paul


  • 52 Points
it seems like this only returns records that are actually contained within that date range. it won't look at a single day within the duration and return any information.

how do i see the value of a day WITHIN a duration?
Photo of QB_Support_Brian


  • 40 Points
Hi Paul,

A table report is designed to show specific records, so if you need to see information on a particular day as opposed to that of a time off period, you would need to track each day as a separate record. You can use our recurring records wizard ( to easily create those.

If this is not quite what you're looking for, it would be best for you to go to Help > Manage Support Cases > + New Support Case so one of our customer care specialists can take a look at this with you.

Photo of Paul


  • 52 Points
SOLVED! woo hoo! it's really a bunch of simple ideas that bump an answer forward until you get the result.

first, in the schedule table:

i setup a text field that writes out "all of the days in a scheduled event" by date. i made it look up 32 days after the start date. i got fancy and made it strip out weekends, so only weekdays show up on listing.

If ( ([duration_vac_all_days]>0 ) and ( ToNumber(DayOfWeek([Start Date]))>0) and ( ToNumber(DayOfWeek([Start Date]))<6) , ToText( [Start Date] ) ) ) &
If ( ([duration_vac_all_days]>1 ) and ( ToNumber(DayOfWeek([Start Date] + Days(1) ))>0) and ( ToNumber(DayOfWeek([Start Date] + Days(1)))<6) , ", " & ToText( [Start Date] + Days(1 ) )) &
If ( ([duration_vac_all_days]>2 ) and ( ToNumber(DayOfWeek([Start Date] + Days(2) ))>0) and ( ToNumber(DayOfWeek([Start Date] + Days(2)))<6) , ", " & ToText( [Start Date] + Days(2 ) )) &

If ( ([duration_vac_all_days]>3 ) and ( ToNumber(DayOfWeek([Start Date] + Days(3) ))>0) and ( ToNumber(DayOfWeek([Start Date] + Days(3)))<6) , ", " & ToText( [Start Date] + Days(3 ) )).....................

up to 32. i did an extra day, just because. i don't think i'll ever need it, but. there may be a few extra commas - QB doesn't care, and they don't bother me, either.

it makes a listing that looks like:
01-08-2014, 01-09-2014, 01-10-2014, 01-13-2014, 01-14-2014, 01-15-2014 Jury Duty

second, in the employee table, i setup 365 summary fields that get the TypeOfTimeOff when the value "Day-Month" is contained in the DateListing text field.

1=Vacation, 2=Sick, 3=Jury Duty, etc.

see the summaryfield jpg - it's really not hard, just takes time to make 365 of them.

i also made a 'year master' as a multiple choice in the employee table. i'm only going to display a year at a time, and i don't want to make another 365 fields each year. you do have to be careful on the 20's though - make sure it is 3-20- or it will pick up 1-13-2014 and 3-20-2014 and 23-2014 all as true, adding the minus fixes it.

third, bringin' it all home....

in the Employee table, i have a 'date_chart' - it's formula - text with html.

(i personally keep all of my graphic images on a web server, not in QB - i can change them on the fly, and have other automated toys access the same images, or generate them by name the QB can find with other formulas... but put'em where you want'em.)

my date chart is a bit daunting - until you remember you can copy and paste code to/from a text editor. search-and-replace.... makes life so simple.

this trick works because my graphics are the same sizes, so they are interchangable.

my code has a row of graphics for a blank and the numbers on top - i actually call 31 seperate png files, to make sure the spacing matches in QB, and then the next row is a date image, followed by a whole bunch of if's

If ( IsNull([Day 01-01]), "<img src=\"mt.png\">", "<img src=\""& [Day 01-01]&".png\">" ) &
If ( IsNull([Day 01-02]), "<img src=\"mt.png\">", "<img src=\""& [Day 01-02]&".png\">" ) &
If ( IsNull([Day 01-03]), "<img src=\"mt.png\">", "<img src=\""& [Day 01-03]&".png\">" ) &
If ( IsNull([Day 01-04]), "<img src=\"mt.png\">", "<img src=\""& [Day 01-04]&".png\">" ) &
If ( IsNull([Day 01-05]), "<img src=\"mt.png\">", "<img src=\""& [Day 01-05]&".png\">" ) ...

my image source locations point to my calendar image folder on my web box....

the [Day 01-04] is a # 1-8, so it loads up the image that's shows the vacation type, and if it's nothing, it loads a blank box. put 31 in a row, you've got yourself a month!

after the first month, you and search and replace the 01- with 02- and you've got another month, just that fast.

you do have to take time to get the right numbers of days per month, and then a line break. i went the extra mile and made a little legend at the bottom.

i've got the multiple choice 'year selector' for the employees to pick. you do have to save the record to make all the values update, but most folks probably won't need to switch it more that once a year - which i can do for all employees with a quick grid edit. :)

and before somebody comments - yes, i know it can break - but our employees can only take off one 'type of time' per day. no sick & personal & maternity. :)

see the second image for the glorious solution - i'm pretty proud of it.