Discussions

 View Only
  • 1.  Report consecutive dates

     
    Posted 05-23-2023 10:34

    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 !



    ------------------------------
    JAW WA
    ------------------------------


  • 2.  RE: Report consecutive dates

    Posted 05-29-2023 00:24

    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
    ------------------------------



  • 3.  RE: Report consecutive dates

    Posted 05-29-2023 12:47

    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
    ------------------------------



  • 4.  RE: Report consecutive dates

     
    Posted 06-27-2023 13:41

    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
    ------------------------------



  • 5.  RE: Report consecutive dates

     
    Posted 06-27-2023 13:39

    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
    ------------------------------



  • 6.  RE: Report consecutive dates

    Posted 06-27-2023 20:54

    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
    ------------------------------



  • 7.  RE: Report consecutive dates

     
    Posted 07-10-2023 09:09

    Thanks, the concept works perfect but when inputted i'm getting the following error: 

    There are one or more problems with your entry

    Uh-Oh! The way this formula is built will take too long to process. Please try refining your formula.



    ------------------------------
    JAW WA
    ------------------------------



  • 8.  RE: Report consecutive dates

    Posted 07-10-2023 14:00

    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
    ------------------------------