Skip to main content
Question

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

  • March 1, 2019
  • 0 replies
  • 48 views

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!
    This topic has been closed for replies.

    Ordinal numbering would be tough.  But counting the number of lessons would be easy.
    • LauraMcFeely
    • Author
    • Registered
    • March 2, 2019
    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?
    • LauraMcFeely
    • Author
    • Registered
    • March 2, 2019
    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.
    • EricMohlman
    • Quickbase Staff
    • March 4, 2019
    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...
    • LauraMcFeely
    • Author
    • Registered
    • March 4, 2019
    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. 
    Terms & ConditionsAccessibility statement