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

Question

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!

quickbasecoachd · Answer

Ordinal numbering would be tough.&nbsp; But counting the number of lessons would be easy.

lauramcfeely · Answer

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!

quickbasecoachd · Answer

There are a few approaches.&nbsp; How many lessons might a teacher teach?&nbsp; Is this "per year" or for all time?

lauramcfeely · Answer

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.&nbsp;

quickbasecoachd · Answer

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.

Forum Discussion

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

Related Content

Show related records

Filter out some records on related table

View all Parent records and related Child records in single table view

Relating Multiple Records from another table

Using Grid Edit to Edit Multiple Records at Once