Forum Discussion

KarenKaren's avatar
KarenKaren
Qrew Trainee
12 years ago

Auto Numbering unique inventory items by department?

As we enter new items into our inventory database we would like it to automatically increase the inventory by 1. We have several departments and each number should increment based on the department n...
Forms
Quickbase Exchange
MarkShnier's avatar
MarkShnier
Qrew Cadet
12 years ago
Have a look at this app.
https://yqc.quickbase.com/db/bigy8ejyu
I gave you Admin access. If you like you can contact me via my profile and I will transfer a copy to you.

Basically the idea is to create a [Sequence #] field which is based on a formula which will start off the numbering by Department at whatever number you want to start at.

var number StartNumber = Case([Department Number],"10",1234,"20",2000);
Max($StartNumber,[Sequence # snapshot])
Then we make a field called [Sequence # plus 1] which is the formula of [Sequence #] +1, and make a summary of the Maximum that field.

and then do a lookup back down to the Antiques Table and then take a snapshot of that field called [Sequence # snapshot]

By doing a snapshot we ensure that the numbers don't all get jiggled down when a record is deleted.

The actual Serial # we display is this formula.

[Department Number] & "-" & Right("00000" & ToText([Sequence #]),6)
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    I had a look and that app of mine does not seem to exists any more.
  • DavidKnight's avatar
    DavidKnight
    Qrew Trainee
    8 years ago
    I got it to work. Posting a picture of the relationships and fields should anyone else run into this
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Thx for posting for the good of the Community.
  • DavidKnight's avatar
    DavidKnight
    Qrew Trainee
    8 years ago
    I took this one step further. The case statement can be cumbersome to manage. As new codes are brought in, a user must remember to update the case statement. I simply added field to the Department Table [Starting Number] and changed [Sequence #] to:

    var number StartNumber = [Department - Starting Number];
    Max($StartNumber,[Sequence # snapshot])

    By making that field required, and annotating in description, users will remember to put a starting number in for new codes directly in the Department table.