Hi Mark,
I'm racking my brain on how to count consecutive business days. I have a staff table and a days table, where days is a child to staff. I need to know if someone has 15 consecutive business days in the child table.
Easiest method would be to leverage the relationship and figure out the date of 15 business days prior and count how many working days that person is staffed after that date. So basically create a formula in your days table for '15 Day Cutoff' and make it a formula-date that subtracts 15 workdays from Todays date. Then in the relationship - summarize the # of days where that person is staff where the date is on or after that cutoff date. Have a formula then evaluate if that number is equal to 15.
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! :)
