How have you structured your tables? It would appear you need at least two tables, very possibly more:
Courses (CoursePK, CourseID, CourseName, etc.)
Revisions(CourseFK, Rev#, Hours)
I'm adding a new field CoursePK, as a filemaker controlled auto-entered serial number as I suspect that your course ID's are typed in and that makes them a less than optimum choice for linking your two tables.
Link them like this: Courses::CoursePK = Revisions::CourseFK and enable the "allow creation of records..." for Revisions
Define a calculation field in Courses as cMax : Max ( Revisions::Rev# )
Define a calculation field in Courses as cTotalHours : Sum ( Revisions::Hours )
Define Rev# with an auto-entered calculation as Courses::cMax + 1
Place a portal to revisions on your Courses Layout and when you enter data for a new revision in the bottom blank row of the portal, your revision# field should increment correctly to show the next revision number.
Note: This method can fail if two people try to log a new revision for the same course at the same time if the database is shared over a network.
Thank you very much. I had most of it set up like you suggested so at least I was on the right track. You were correct about the admin typing in the course id #.
I may be confusing myself now but I am still having an issue with it generating the rev #s correctly. It is almost there though. How do I get the rev # to be serial based on the course id #? I need the numbers to automatically start at 1 for each course Id number then count up from there. (ex. NP333 rev 1, NP333 rev 2, then for the next course # NP222 rev 1, NP222 rev 2, etc.)
"How do I get the rev # to be serial based on the course id #? I need the numbers to automatically start at 1 for each course Id number then count up from there."
I've been testing my suggested auto-enter calculation and it's not working like I thought it would. The value in cMax does not appear to update correctly before the calculation grabs and increments it. I'll keep playing with it but perhaps someone else can suggest a fix in the mean time.