# Discussions

View Only

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

• #### 1.  Formula to number child records as related to a parent record, based on date

Posted 03-01-2019 18:26

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!

• #### 2.  RE: Formula to number child records as related to a parent record, based on date

Top
Contributor
Posted 03-01-2019 18:47
Ordinal numbering would be tough.  But counting the number of lessons would be easy.

• #### 3.  RE: Formula to number child records as related to a parent record, based on date

Posted 03-01-2019 19:04
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!

• #### 4.  RE: Formula to number child records as related to a parent record, based on date

Top
Contributor
Posted 03-01-2019 19:19
There are a few approaches.  How many lessons might a teacher teach?  Is this "per year" or for all time?

• #### 5.  RE: Formula to number child records as related to a parent record, based on date

Posted 03-01-2019 19:25
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.

• #### 6.  RE: Formula to number child records as related to a parent record, based on date

Top
Contributor
Posted 03-02-2019 14:18
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.

• #### 7.  RE: Formula to number child records as related to a parent record, based on date

Posted 03-04-2019 15:19
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...

• #### 8.  RE: Formula to number child records as related to a parent record, based on date

Posted 03-04-2019 15:55
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.