I have a database whereas:
Associate 1 – All his case numbers begin with 14-xxx
Associate 2 – All his case numbers begin with 12-xxx
And so on
"Case Number" is the field that all the case numbers are stored in. "Associate" is the field the associates are in.
What I need is the “case number” field to increment by 1 whenever associate 1 is assigned the case. For example, if the
Case is assigned to Associate 1 and his last case number was 14-300 I need the next case number to be 14-301 ;
And if the last case for Associate 2 is 12-3001 and I want to enter a new case and assign it to associate 2 the next case will be 12-3002
The “case number” field should be one field that has both 14 & 12 prefixes.
Please help if possible. And THANKS SO MUCH if you do !!
There are several methods of implementation.
First create serial number global field for each Associate. Then change your Case Number to be a text calculation of the id (12 or 14), the ampersand, a hyphen in quotes, an ampersand, and the serial number. Make this calculation NOT replacing the current value.
As the serial number increments with each new record you'll get a new number following the id and a hyphen.
There are major multi-user problem with relying on serial number fields, but they require complex solutions. If this works, don't sweat it. When your solution is used by multiple people, be prepared to change that serial number field for a REAL unique number generated possibly from a time stamp and a global of the current largest entry and or who knows what. That will PROBABLY work most of the time.