Forum Discussion

QuincyAdam2's avatar
QuincyAdam2
Qrew Trainee
3 years ago

Count If This Week

Hello:

I am trying to create a view that shows whether staff are placed at each location each day of the week. It would be preferential to see the date vs. Mon, Tues, etc...

I have 3 tables: Staff, Location, Schedule. Staff is a list of employees. Locations are a list of locations. Schedule is where I assign Staff to a Location on a Date, with a Start Time and End Time. I also have a formula field to calculate Total # of Hours.

I have a table to table relationship between all tables, so I'm able to pull in a summary field from Schedule into Locations to see the Total # Hours, however, I need to see whether a location has coverage on each date. Total # Hours won't work since it will be cumulative. I'm not sure how to achieve this through summary field or report. All help is appreciated.

Thanks! 

​​​

------------------------------
Quincy Adam
------------------------------
Formulas and functions

3 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    So you need to know for every location for X number of dates ahead if you have coverage, say for the next two weeks?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • QuincyAdam2's avatar
      QuincyAdam2
      Qrew Trainee
      3 years ago
      Correct

      ------------------------------
      Quincy Adam
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        3 years ago
        I suggest that you make a summary field on the Locations record to total the # of hours where the date is equal to today.

        Get that one working and duplicate it but change the filter to date equal tomorrow. 

        The duplicate that one and change the filer to 
        where
        Date is during the next 3 days
        Date is not during the next 2 days.  (ie think Venn Diagram).

        Then duplicate that  for next 4 days but not during next 3 days .....

        Just keep  going as far out as you like.

        If you want to get fancy after that all working there is a way to auto rename the field each night using Pipelines and API calls to reflect the actual dates and not a generic name like Total hours 3 days from now.


        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------