Jump to content

Multi-part composite serial number for items

This topic is 3112 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Greetings! This is my first post on the forum, and I'm also new to FM.

I have a situation where I have a few ideas, but I was hoping to get better advice and direction from others more experienced with this at the start.

The numbering scheme in use may be crazy, but it is established. I am seeking the better way(s) to handle what's in place.

I have an ITEM table, and the items have a unique identifier key which are part of a serial number. A LOG table records each time that one of the items is used. This "serial number" is kept on the item's logsheet.

In August of 2014 an item with the ID 46 is used. The serial number would be something like (without underscores): 46_14_08_3456.

The next time that item is used, let's say February of 2015, the serial number would be 46_15_02_3457.

So the last part is a number which is auto-incremented, but each item has a different (it's own) sequence.

1) Do away with the single LOG table and create a table based on each item and populate a field using auto-incrementing serial each time it is used. Use another field in this table to store the composite number.

This seems like it would be the easiest, but it would have a lot of overhead with extra tables. There are only about 15 different items for now... this number *might* increase once every 3-5 years at most if ever.

2) Use a field in the product's table to keep the sequence number that was last used for that item and a script for incrementing. Then the composite number could be calculated and stored in the LOG table.

3) Get better ideas, articles, etc. from more experienced users...

I'm leaning towards option two for now...

Thanks, GG

Edited by Gabriel G
Removed the fixed line breaks
Link to comment
Share on other sites

Do you really need the numerator part (the last four digits) to be consecutive per item? This is not easy to implement reliably (without duplicates). It would be much simpler to increment the numerator per record, e.g.:



Link to comment
Share on other sites

Hello comment, thank you for your response and all the help you provide to everyone on these forums. I have learned quite a bit from your contributions here!

Yes, I agree with you. It would be much easier to increment on a single table rather than each product. However, since it is an old system, I am striving just to bring it away from its current paper state. This is mostly to help maintain the history of previous entries as well.

I would also like to refocus my previous question a bit. I am more interested in how you, or anyone else, would handle structuring this unusual numbering scheme the way it is? If you were given this challenge, would you choose either of my considerations- or would you do something even different than what I have listed?

Thanks, GG

Link to comment
Share on other sites

how you, or anyone else, would handle structuring this unusual numbering scheme the way it is?

I don't see anything "unusual" in this numbering scheme. This question comes up quite often: you have a parent-child relationship, and you want to number the children of each parent with its own series (prepending the other digits representing the parent ID and the year/month is trivial).

Usually, the idea is to take the maximum existing value in the series (available either from the point-of-view of the parent, or through a self-join relationship of the child table based on matching parent ID), and add 1 to that. Which will work 99.9% of the time - until two users create records roughly at the same time and end up with duplicate numbers. So the first "challenge" is to prevent such situation - and it could be accomplished by (a) forcing the users to create new child records by script only, and (b) having the script lock the parent record first and, failing to do that, abort the task.

However, the "challenge" doesn't end there: you must also consider what will happen if user decides that the parent selected initially is not the right one and needs to be changed to another. Instead of dealing with this, let me also point out that if any record is deleted, you will have a gap in the series anyway. So the real question I would ask here is this: is the goal worth the means required to achieve it?

  • Like 3
Link to comment
Share on other sites


This is exactly what I was looking for- some insight to the pros and cons of going down this road with a nice scenario of how to address it. It also stresses to me, the importance of insisting on doing it this way up front. I was not completely convinced of this until now.

Thank you kind sir!

Link to comment
Share on other sites

This topic is 3112 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.