The issue is I don't need 15 before or 15 after, I need 15 consecutive regardless of where they fall in the calendar.
The days are PTO requested days off, so if someone is going to be out for 15 days or more, I need to notify someone as soon as possible even if the absence is 6 months from now.
Ah – then you'll need to leverage Formula queries in your child table for that since you're evaluating the entire data set. Off the cusp something like this might work:
//formula-checkbox
var date fifteenPrior = WeekDayAdd([Date],-15);
var text query = "{'fid of date field'.OAF.'" & $fifteenPrior & "'}AND{'fid of date field'.OBF.'" & [Date] & "'}AND Insert additional query to check what days apply";
var number consecutiveDays = Size(GetRecords($query));
$consecutiveDays >= 15
The idea is that for each day in your 'days' table you're counting the 15 days prior including itself how many days meet your criteria to be counted. What it will result in is that it will flag all the days that fail your test. So if someone is off for 20 days, days 15-20 would all be checked off as failing this test since you're just counting the prior 15 days as off and each would fail that test.
And the issue with that is that the App is HUGE. So I can't use formula queries without affecting performance. ERG! :)
Hmm. Does modifying the query to cap it at only caring about dates in the next 6 months help at all? Like if you make an if statement that if the date is before this month or after 6 months from now don't do anything so the query is a little lighter on the table?
Otherwise, does it need to be real time or can it be some kind of daily review of your day records? It would be kind of taxing on QB to evaluate the entire subset of days each day so you might need a way to control / limit the results of what you're checking everyday - but you could take the formula query concept and put it into a Pipeline.
So lets say someone enters a new PTO request for 12/21 - you can have a daily Pipeline that runs, or just run based on that PTO event and in the pipeline do the same query using 12/21 as your baseline. If the number of records in your response is 15 - check a box on the day that says it fails the test. This comes with a tax in that you then have to bind it in a way that if the PTO request changes you'll have to undo that check so it's open unless it fails a different test.
If you're able to program and go the custom route you can always just take this out of Quickbase natively and write a script where you can query the days in the future and using whatever programming language you choose have a function do an actual loop and count them up and write back issues to QB. Way more complex in that sense, but figured I'd toss it out.
------------------------------
Chayce Duncan
------------------------------
