Looking to build a list for employee numbers with multiple data points

Question

I need to build a list in SharePoint that auto assigns a sequential number and can associate that auto assigned sequential number to a "smart number" based on the company code.

For Example: ABC company uses smart numbers 23xxx. XYZ company uses smart numbers 567xxx. I need a sequential number that is not specific to company, say 89xxx.

So, we hire someone at ABC company and they need a sequential number 89001 and a smart number 23015. And we hire someone at XYZ the same day, so need a sequential number 89002, and smart number 567456.

Is it possible to have multiple lists generating numbers and somehow tie them together, based on company? Thoughts on this?

Answer 1

You can do that with a SharePoint Designer Workflow running at On Item Added. You create a variable that generates the string you are looking for. such as in a Risk Management System, you might tag it with Risk-15.

You can pull the sequential number from the ID of the added item, then reach to other lists to create or update joining items.

By company you might have a variable ABC-1-Title

The cheap way that I handle it is to treat every custom list and library as a flat data table in a database. Cascading lookups between lists act as rough 'joins'.

If its going to get complicated, i'll start off with my primary table of companies with adjoining metadata, and link everything to that.