Forum Discussion

JAWWA's avatar
JAWWA
Qrew Cadet
2 years ago

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 this out. Thanks !



------------------------------
JAW WA
------------------------------
  • 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's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew 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
      ------------------------------
      • JAWWA's avatar
        JAWWA
        Qrew Cadet

        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
        ------------------------------
    • JAWWA's avatar
      JAWWA
      Qrew Cadet

      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
      ------------------------------
      • JoshHamilton's avatar
        JoshHamilton
        Qrew Trainee

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