conditional auto enter field

[smith]

I have a report card database for 6 schools - the Report Card (RC) ID is slightly different for each school - A-RC0506-1, C-RC0506-1, etc. (The first letter of the RC ID is based on the first letter of the respective school.)

How do I set conditions in an "Add a New Record" script that auto enters the RC ID based on the last record for that specific school?

For example, the Day School has 403 records, each RC ID increases by 1, so the last record is D-RC0506-403. How do I write the new record request for adding a new student for the Day School comes in at D-0506-404? Is it defined in the RC field as a calculated field, or is it defined at the "New Record Request" level? And what are the steps? Thanks.

[Oldfogey]

Create separate fields for School ID and Student Number.

Create a self-join relationship called 'School' = School ID::School ID.

When you create a new record (using a script, I hope) make sure you have entered the School ID then add the following

Set Field(Student Number, max(School::Student Number) + 1)