Formula to number child records as related to a parent record, based on date

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered

I have a parent table called "Teachers" and a child table called "Teacher Activities," which tracks a variety of records for each teacher, including the lessons they have taught so far this year. I am wondering if it would be possible to create a field in the "Teacher Activities" field that would number how many lessons that teacher has taught so far this school year, e.g., this record is the first lesson this teacher has taught, the next one would be the second lesson, etc. 

The records in "Teacher Activities" are sometimes uploaded manually and sometimes uploaded with an Excel spreadsheet, and the record IDs are not in order based on when they're taught. 

I do have a "Date" field that tracks when the lessons were taught, and I would want to use this to determine the ordinal number.   

Is this possible? I've found similar solutions, but they rely on the record IDs being in the right order, so they won't work for me. Thank you!
Photo of Laura

Laura

  • 302 Points 250 badge 2x thumb

Posted 5 months ago

  • 0
  • 1
Ordinal numbering would be tough.  But counting the number of lessons would be easy.
Photo of Laura

Laura

  • 302 Points 250 badge 2x thumb
Yes, I have created a summary field in the Teachers table that gives me the total number of lessons taught so far this year for each teacher, but I'm hoping to get the ordinal numbering for each lesson. Do you think there's any way to do that? 

Thank you for taking a look at my question!
There are a few approaches.  How many lessons might a teacher teach?  Is this "per year" or for all time?
Photo of Laura

Laura

  • 302 Points 250 badge 2x thumb
The most lessons a teacher would teach is probably 25. Many teach as few as 1 or 2 lessons, though. This is per school year, so I've set up my "fiscal year" in Quick Base to run from 8/1 to 7/31. 
This solution will work on a going forward basis, not for existing lessons.

You can do the summary of the # of lessons and then look that up down to the Lessons table.  Then use a snapshot field https://help.quickbase.com/user-assistance/setting_up_snapshot_fields.html to freeze that value

The lessons will be numbered.

However, if you delete a lesson record, the numbering will end up double up on an ordinal.  ie this will usually work but is not a perfect solution and also will only work on a going forward basis.
Photo of Eric Mohlman

Eric Mohlman, Employee

  • 500 Points 500 badge 2x thumb
As Mark already identified, this would require a summary field, lookup of the summary field, and a snapshot on the child table to capture the value as each child record gets added. This article describes the steps involved: https://community.quickbase.com/quickbase/topics/how-can-i-set-up-a-numbering-system-to-track-quotes...
Photo of Laura

Laura

  • 302 Points 250 badge 2x thumb
Thank you both so much for taking the time to figure out a solution! I wasn't aware of the snapshot field option, so that's helpful to know.