I'm using the "maximum" option for a summary field I called Max_CM to calculate the largest number in a field called "CM_Number" in my database. I then use this field in a script which responds to a checkbox being checked. The field runs a script which takes the Max_CM, adds +1 to it, and fills the new number into the CM_Number field.
This has been working so far because I've mainly used it for new records.
Just now I've realized there's a problem in case I am trying to use my checkbox when in a found set.
If I check the box inside of a found set, the script apparently runs on just the found set. Then the Max_CM isn't representative of the entire database, but just of the found set. The number that ends up in the CM_Number field of this record is not the whole DB's maximum, but just that of the found set.
I'm looking to make sure this CM_Number field is filled out consecutively, according to the chronology of checking that box.
Does anyone have any idea how that can be accomplished?
EDIT: Some of you asked for some more details - this DB is a kind of amalgamation of two older ones. Each of the old DBs had its own ID# system, and we've decided to stick with it. So, when a new object is recorded, it can have a CM number, an AARS number or both.
We were looking for a system that frees the user from the need to keep track of what the latest CM# or AARS# is. That's why we used the summary field. Upon the user's indicating which number is appropriate for the new record, the script kicks in, checks the maximum CM# or AARS#, adds one, and populates the appropriate field (CM# or AARS#) of the new record with the result.
This works perfectly. But sometimes we're required to add a CM# to a record that already exists, which means it's definitely not the last record in the DB. I'd have to use *some* search criterion to pull up the right record, so there's going to be a found set anyway. If I check the CM box this time, the CM# field will be populated with whatever is the last CM# in the found set +1.
Our CM# field is unique, so we do get an error message as soon as we try to leave the record. But that doesn't really solve the problem as it still requires that we check the latest CM# and populate the field manually.
I hope that helps? Is it clear enough?
Thanks for responding guys!