Creating a CAD system for EMS agency; sequential CAD number solution?

Questions
1

Hey all,
I’m working on a basic cad system for our EMS agency. I have a basic system in place importing calls via an email parser, however I’m a little stumped on generating incremental CAD numbers for each incident. We format our CAD numbers in the format of 2023-xxxx. I’d like to be able to start with a specific number (2023-3205) and do a +1 with each new call generated.

What would be the best approach to this?

2

I would store the Year (as a number) and ID separately and display them together. When you create a new incident ID, you want to do a search for Incidents CAD YEAR filtered by current year, sort by CAD ID Ascending First Item, then add 1 . Create your seed CAD incident with the 2023-3205 ID and create a new incident and it should work. and on Jan first 2024 it should start from 2024-1

3

@nfisher nice solution!

Can you improve on it to prevent (or repair) duplicate IDs being created by different users at almost the same time?

(Assuming duplicates are undesirable in this app)

4

Could be of interest here…