Custom Serialization & Value List Sorting
So I'm trying to find a better way to do this but I'm coming up short. For the life of this business, they have come up with Job Numbers based on the 2 digit year, month, then an up counting serial from 001, an example for a new job opening today would be:
In my db, I don't use this is my PK, I have another system for that... however, I would love that when they create new jobs, it would automatically be able to assign the number to the job so I don't have to worry about job number duplication. Any way I can do this?
On a similar note, I have imported all of our jobs from 1995 on. Problem is this, if I want to see the jobs from most recent to least recent, doing a decending sort by job number will result in jobs from 1999 down to 1995 (when the business started) coming before ones from 2012:
99-01-001 comes before
I tried creating a value list that could be used in the sort but for some reason, the best it will do is count up from 2000 on (00-01-xxx)
Any ideas on how I can switch this?