Script to number sections of a course

[derlandson]

I am having difficulty with coming up with a script that numbers the sections of courses based on the teacher assigned. I need multiple sections of a course taught by a single teacher to have the same number, and each single section to have a unique number.  For example:  

Math Grade 6 has 4 sections

Math 6 assigned to Teacher X

Math 6 assigned to Teacher X

Math 6 assigned to Teacher Y

Math 6 assigned to Teacher Z

 

I want those sections to be numbered as follows

Math 6 assigned to Teacher X, numbered 1

Math 6 assigned to Teacher X, numbered 1

Math 6 assigned to Teacher Y, numbered 2

Math 6 assigned to Teacher Z, numbered 3

 

Each course has a different pattern of teacher(s) assigned to the sections and different  total number of sections.  I have tried using a case statement and got close, but never 100% consistent and correct.  Scripting with a loop and If clauses also never quite gets 100% accurate.  All suggestions are appreciated.  I am running FM 16.   Thanks,

 

dan

 

[comment]

Assuming you have a table of Sections, with fields for CourseID, TeacherID and SectionNumber, you can populate the SectionNumber field using a script that starts by finding the section records you want to (re)number, then does:

# SORT BY CourseID, TeacherID
Sort Records [ Restore; No dialog ]
Go to Record/Request/Page [ First ]
Loop
   Set Variable [ $secNum; Value:Case ( 
Sections::CourseID = $courseID and Sections::TeacherID = $teacherID ; $secNum ; 
Sections::CourseID = $courseID ; $secNum + 1 ;
1
) ]
   Set Variable [ $courseID; Value:Sections::CourseID ] 
   Set Variable [ $teacherID; Value:Sections::TeacherID ] 
   Set Field [ Sections::SectionNumber; $secNum ]
   Go to Record/Request/Page [ Next; Exit after last ]
End Loop
Commit Records/Requests [ No dialog ]

--
P.S. What's up with the bold font?

 

Edited June 1, 2021 by comment

[derlandson]

Sorry about the bold font...wrote the question in a Google doc and pasted it in without noticing the Bold.

The use of Case is similar to what I have tried, but the issue is that it can't default to 1 if the teacherID and courseID aren't the same as the previous record since sometimes the sequence of numbering I need for the sections might be something like:

Health 7 assigned to Teacher A, numbered 1

Health 7 assigned to Teacher B, numbered 2

Health 7 assigned to Teacher B, numbered 2

Health 7 assigned to Teacher C, numbered 3

thanks,

Dan

[comment]

I am afraid I don't see your point. In my script, the numbering does NOT "default to 1 if the teacherID and courseID aren't the same". It resets to 1 when the CourseID changes. 

IOW, if your found set (after sorting) is:

then after running the script you will get: 

How is this result different from the one you expect?

 

[derlandson]

Right!, I misread the Case statement.  Thanks for the example.  I will give it a try.

Appreciate the time in answering the questions.

Dan