Re: getting duplicate number is there a

I actually had the same thought (a counter table, I might be able to add
fields to the location table, but we have several applications case is just
an example). I agree that is probably the safest way and it also fixes
another issue I have been having when a user wants to transfer a case to
another location.

I appreciate the ideas, I could probably safely lock the numbering table as
I would be afraid of locking the case table.

Joel Fradkin

I'd be tempted to have a case_numbers table with (year,location,max_num) 
and lock/read/insert to that. Makes everything explicit, and means you 
don't have to mess around with counts/substrings.

--
  Richard Huxton  Archonet Ltd