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 this out. Thanks !
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?
This can be done using Formula Queries. Can you tell us if there could be more than one call out on a particular day? That would affect how the formula query would be written.
I tried using this, only half of my job is accomplished. it does list the dates, but i need to filter in the report as in the report should show only employees that have consecutive call outs.
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 ?
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.
