Report consecutive dates
I have two tables employees and callouts where employees can report multiple callouts. I need to create a report that shows callouts if they fall within 5 consecutive dates. Need help in figuring thi...
Forum Discussion
Might need some more information on what sort of fields you have in the callouts table and how it's used.
Do you mean that you want a report that lists where an employee has 5 separate callout records assigned to them where I am assuming there is a date field of the callout that is used to see if those 5 are consecutive?
------------------------------
Josh Hamilton
------------------------------
Yes would ideally want a report. What I envision is a formula field that checks if dates are continuous and this checkbox can be used to filter in a report. Any suggestion ?
------------------------------
JAW WA
------------------------------
If you create a formula checkbox field in the callout table and put in this query formula, it will look up the amount of callout records ahead of each record up to 4 days after its date that is related to the same employee.
In this code, 6 = the callout date field id and 7 = the related employee field id so you will need to replace those numbers with the correct field IDs in your table.
var date endDate = [date]+Days(4);var number d = Size(GetRecords("{6.OBF."&$endDate&"} AND {6.AF."&[date]&"} AND {7.EX."&[Related Employee]&"}"));If($d=4, "true", "false")If there are 4 records found, then it will mark the checkbox as true. You can then use a summary field in the relationship to check if there are any callouts with the checkbox marked as true and use that as your filter for a report to show you the employees with 5 consecutive callouts.
------------------------------
Josh Hamilton
------------------------------Thanks, the concept works perfect but when inputted i'm getting the following error:
There are one or more problems with your entryUh-Oh! The way this formula is built will take too long to process. Please try refining your formula.
------------------------------
JAW WA
------------------------------Formula queries are powerful but if there is a lot of data and the formula query has to search every record against every other record, then the number of comparisons can quickly get into the millions.
One thing that might save you is to sequence the order of the filters to filter out the most records possible first. So I suggest first looking for the same employee first. I can't copy paste the picture below to edit but try the related employee part first before the date range check.
var date endDate = [date]+Days(4);var number d = Size(GetRecords("{6.OBF."&$endDate&"} AND {6.AF."&[date]&"} AND {7.EX."&[Related Employee]&"}"));If($d=4, "true", "false")
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
