Question about formula to generate serial numbers for parts?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have a table named "Parts" (parent) and another named "Serialized Parts" (child).

I need to generate serial numbers on the child table, starting at 0001 (with leading zeros), for each part.

This is how I have it configured currently:
Summary field "# of Serialized Parts" and a lookup field for the same thing on the child table
Snapshot field referencing the lookup field, and a formula text field with this:
Right("0000" & ToText([Snapshot for S/N Formula]), 4)
Which gives me a 4 digit number, starting at 0001 for each Part.

Here are my questions regarding this:
1- I want a Numeric field for the S/N, not text, how would I do that?
2- Is this a bullet-proof method to accomplish what I want with no risk of having duplicate serial number or any other issues in the future? This will be our primary and only solution for tracking serial numbers (aside from backing up QBase). It has to work perfectly.


Thanks.


Photo of Matthew

Matthew

  • 60 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,630 Points 50k badge 2x thumb
You cannot have a number equal to 0001 because numeric fields do not retain leading zeros.

If you want to have another field that is numeric you can have a formula field of
ToNumber(My serial field with leading zeros])

The method should be reliable as long as you disable Anyone's (even the Admin Role) ability to delete a child record.  If you delete any child record you will get duplicate snapshots.  If there is the possibility that you need to delete a child record, instead, have a checkbox field to mark it as delete, but don't actually delete it.
Photo of Matthew

Matthew

  • 60 Points
Okay I figured numeric fields wouldn't allow leading zero's.

Besides relying on the snapshot configuration, is there another way or another field I could use where I wouldn't have to worry about child records being deleted that would give me the same result?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,630 Points 50k badge 2x thumb
Yes, but it gets more complicated to set up.  You can do the initial snapshot.  Then do a summary of the maximum of that snapshot field and look that up down to the details record and snapshot that.  That way if one is deleted, it will not affect the Maximum of the ones that still exists.  I believe that you may have to Add 1 to that second snapshot to get the next number.
Photo of Matthew

Matthew

  • 60 Points
That doesn't sound too hard to do. I'm gonna play around with it and see which works best, maybe just preventing anyone from deleting the records will work well enough. Thank you!