Skip to main content
Question

Create a Collection Schedule by day of the week

  • August 28, 2017
  • 0 replies
  • 67 views
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?
      This topic has been closed for replies.

      • MCFNeil
      • Registered
      • August 28, 2017
      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
      • Registered
      • August 28, 2017
      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.
      Terms & ConditionsAccessibility statement