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
------------------------------
- MarkShnier__You
Qrew Legend
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.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------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.
------------------------------
JAW WA
------------------------------
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
------------------------------
