For some reason we're attempting to use SharePoint for end-to-end job lifecycle management for a construction company (VP has decided that SharePoint must be a better solution than my hand-rolled PHP/MySQL job lifecycle implementation that has been used successfully for over a year). As best I can tell SharePoint was never designed for this, but I have zero experience with it so I could be totally wrong. Problem number 1 of 16 million or so:
The company assigns each job a job number, the format for these is xx-yzzz. XX is a job prefix, 51-64 that determines the type of job (this is related to a Job Prefix list). Y is the last digit of the current year, and ZZZ needs to be auto-incremented from the previous job number with the same XX prefix. Using a relational database this is easy, SELECT jobNumber FROM tblJobNumbers WHERE jobPrefix = "51" ORDER BY jobNumber DESC LIMIT 1; and then add 1 to that result (job number xx-y000 is an internal number, so this won't break on January first).
My understanding from every bit of Google-fu I could muster is that this would be relatively trivial (other than learning ASP, but whatever, know one and know them all right?) if I was running Sharepoint Server and had a .NET IDE. Unfortunately I have neither of these things (also, no InfoPath, but if that's what it takes I might buy it with my own money at this point). Is there a way to do this between Sharepoint 2013 Online and SP2013 Designer on the desktop?
Bonus question: can I then concatenate jobPrefix and JobNumber in another column so it is searchable by job number as a whole?
