I need to assign a number to each record that is entered during a week and the next week have that number reset to zero

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
Photo of Bert

Bert

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
Here would be the steps

Create a table with a date field as the Key field.

Use excel to load it up with up all the Sundays for the next 10 years.

On the details record table create a field with the formula FirstDayOfWeek(Today())

Use that to create a relationship to the Sundays table.

Make a Summary field of the count of the records (ie the # of records for that Sunday)

Look that up down to the details table.

Make a snapshot field of that lookup.

Y'ur done!
Photo of Bert

Bert

  • 0 Points
Got a few more questions? The Field with FirstDayofWeek formula is that a Date - Formula Type? I'm up to the point where I created the Relationship but it will not let me make a summary field of the count of the Records.I'm getting this Error message We are sorry -- at this time, you cannot use this formula and have a summary field using this relationship. Also what if the first day of the week is a Saturday will that make a difference? I'm doing it off a fiscal year that the weeks start on a Saturday.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
Yes, I recall now that they do not let you build a relationship based on the Today() function.

Can you see if it allows this

FirstDayofWeek(ToDate([Date Created]))

but then we need to deal with the Saturday issue.

Try this

FirstDayofWeek(ToDate([Date Created])-Days(1)
Photo of Bert

Bert

  • 0 Points
It wouldn't take that formula either got the same error message.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
OK,I think I vaguely knew that too.
OK, so make a date field and set the default date to today, call it [Date Link to Weekly Count].
It does not need to clutter up your form.

FirstDayofWeek([Date Link to Weekly Count])-Days(1)
Photo of Bert

Bert

  • 0 Points
That worked I really appreciate your help. Is there possibly a way for it start counting at 1? It's starting at 0. If not I don't think it will be a big deal.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
I am very surprised that it is counting starting at 0.  If that is true for sure, than just make a new field to be exposed to users with a formula of that snapshot field plus 1.
Photo of Bert

Bert

  • 0 Points
It was showing 0 before I saved the record it showed 1 after the record was saved. Really appreciate the help.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
The lookup field is obviously a step behind until you actually choose to save the record. If you wanted to show the user the probable value you could have a field to only show in add mode which adds 2 to the lookup value. Then in edit and view modes show the frozen snapshot value.