Our path department uses case numbers for procedures. They take this format: 1999b013. First 4 digits are year, 'b' stands for biopsy and '013' means it was the 13th of the year. The last part auto increments upward. There are 3 allowable types (a,b,e), although in theory that might grow. Each type has its own numeric series (ie. 1999b01, 1999b02,.. along with 1999a01, 1999a02, etc). I dont particularly like this system, but I need to support it in some form.

I'm trying to figure out how to best do that in labkey. My questions are:

1. I am assuming I have a field called 'caseno' in the table that actually stores this string. the alternative is to calculate it using SQL and join it. is one approach better?

2. I am assuming that I would calculate the next caseno when the user loads the import form using javascript. To do this, the form code needs to run a query to identify the next number in the series for that procedure type. The alternative is to try to calculate that in a validation script, but the validation script cannot run sql. is that the best place to calculate it?

3. Should I explicitly store the procedure number in it's own column (either in addition to the caseno string or as a substitute)? this would be an integer column that i'd need to manually auto-increment by procedure type and year. This makes identifying the next procedure number in a series somewhat simpler, but also requires manually maintaining this new column.

Thanks for the help and let me know if any clarification would help.