Forum Discussion

AndreonnaGarret's avatar
AndreonnaGarret
Qrew Assistant Captain
8 years ago

Create a Collection Schedule by day of the week

I am trying to build a table for a specific task that I complete. I collect a set of data from some customers once or twice a week. Some are on Monday and Friday, some are Monday/Thursday, some are just Monday. So I have set up a multi select text field called [Collection Days] that I can select the collection schedule using that. 

What I need beyond that is a way to know if the data has been collected. So on Monday morning, I start with a list of all of the customers I need to collect the date from. As I enter the data they would fall off of the list. This way I know who I still need to follow up with. 

I was thinking of using a [Last Collected] field and a [Next Collection] field. Then if the [Next Collection] date is equal to Today a checkbox will be checked. That checkbox will uncheck itself once I enter because the next collection date would change. 

However, I can't figure out how to set up the formula in the [Next Collection] field. Can someone please advise the best method to reflect the [Next Collection] based on the information in the [Last Collected] and [Collection Days] fields?
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    Andreonna,

    How are you?  

    If I were to do this, I'd create some formula date fields to identify the date for the collection days.  You would need 5 or 6 (one for each day of the week).

    Then you would need to take the summary field of the [Last Collection] and compare that to the Minimum date that is after your [last collection] date.  Thus, giving you the "next" date.

    Does that make sense?  Do you need more details?
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    There are a few parts to this, or for each formula.  This is assuming that is a week to week thing, and old dates for 'estimates' are not needed.  You will also want this to be a rolling weekly, so on friday, you see next mondays date if needed.

    QB has a formula that will give you the next date on the given day
    NextDayOfWeek (Date d, Number n)
    (Sunday=0, Monday=1, etc.)

    So for your Monday field it would be [Monday Collection Date]

    NextDayOfWeek([Last Collection Date], 1)

    Then account for if the Monday date is needed;

    If(Contains([Collection Days], "Monday"), 
    NextDayOfWeek([Last Collection Date], 1), 
    null)

    ....repeat for all the days that collections are possible (mon-fri)....

    Then have a master field for [Next Collection]

    Min([Monday Collection Date], [Tues Collection Date], ....)

    ...I'm thinking we will need some more logic to account for times that the 'last' collection date is over a week ago...  i.e. if you miss collection days.
    • AndreonnaGarret's avatar
      AndreonnaGarret
      Qrew Assistant Captain
      8 years ago
      This worked perfectly! Thank you. 

      Yes you are correct as well that there would need to be additional logic if a date is missed. 

      For now I can just filter my report to only show those that have a [Next Collection Date] on or before today. Then I can follow up on ones missed, and once I've collected for the day they will fall off of the report.