If you can pull up all your students to be promoted and only the students to be promoted into your found set. Replace field Contents can be used to promote your students. This will be easiest to do if you can perform a find for all students to be promoted to one specific color. Then you can simply select the next color for one student and use Replace Field Contents to update all the others to match.
Finding all students of all levels that have qualified for promotion and then promoting them will be more complex and assigning a number to each level would make this process simpler as the replace field contents calculation can then just be a calculation that adds one to this number field.
Note: Replace Field Contents can modify literally every record in one table all in one go so it can be a very good idea to make a back up copy of your file just before experimenting with this option to learn how it works just in case you get an unexpected result.
Thanks for the quick reply. If I try option 2 and do assign a number to each level, can I still have the drop down list show just the colour names, or will it be the assigned number + colour name (i.e. 1_white, 2_yellow, 3_green, etc)? Or just the number itself?
If you change it to a popup menu and set up a table where you have one record for each level, one field in that table can be the number and the other field can be a text field with the color.
Then you can set up your value list to enter the number, but display the text for the color name.
In Manage | value lists
create a value list and select the "use values from a field" option.
Select the number value for field 1, select the "also display values from" option and select the text field for field 2.
Click the "show only values from the second field" option.
As soon as I posted the question, I figured the answer would be more or less what you described. I guess that means I'm learning. ;)
Everything's working great so far. Now, if I have my number values from 1 to 24, is there a way for my replace field contents calculation to exclude the numbers 18-24? Right now, the calculation is:
BeltID + 1
I'm not sure that I understand. Doesn't that just limit your values to 1-17 instead of 1-24?
Yes. 1-17 are the non-black belt ranks who all get promoted at the same time. 18-24 represent the black belts, who test at different times, so they won't get promoted with the non-black belts. So this global change would really only affect 1-17, hence my need to be able to change the field for those records at once.
So is there a more elegant solution to do this, apart from splitting the two levels of belts? I would prefer to keep them in the same field.
If you review my original posts, I said "if you pull up all the records for students that need to be promoted in a found set...."
You shouldn't be pulling up black belt level students in your found set in the first place and thus they will not be promoted by the Replace Field Contents action.
You could use a calculation option with If or case to keep from changing the value when it's a black belt student record, but that seems unnecessary as you should be able to easily exclude those records from your found set.
So what would be the best way to find all the records for the students ranked 1-17 at once?
A script could do it like this:
Enter Find Mode  --> clear the pause check box
Set Field [Students::Rank ; "<18" ]
Set error capture [on]
Perform Find 
But I don't know enough about your school and your database to know what additional criteria might be needed to omit records for students below rank 18 that should not be promoted.
You could also do this manually if you added a copy of the rank field to your layout without the value list formatting so that you can enter <18 manually after entering found mode.
I think I might be better off just adding an "eligible for promotion" check box to the layout. That way, I can just check off all the students that are ready to be promoted, then do a find for that check box and use the original script for replace field contents.
But I'll keep this other script in mind too just in case. Thanks for all your help.