Create a Collection Schedule by day of the week

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
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?
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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?
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
How exactly would I set up the formula fields?
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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.
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
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.