Auto Numbering unique inventory items by department?

When you say that you want to increase the inventory by 1, do you mean that you are assigning serial numbers to the pieces of inventory and want to increase a serial number wheel by 1 each time?  There is probably a way to do this if that is what you are after.

We sell items including antiques.  Each piece is unique and needs to have a unique inventory number. Also depending on the category
i.e furniture (10), bronze (20), lighting (30) etc. the inventory number would look like
10-000111 for furniture
20-000123 for Bronze
 Since we already have numbers assigned we cannot start at 1 we have to start with the next number in the specific category.
This numbering system has been used for many years and is tied to our accounting system.

I am not sure if I have clarified this or not.

Karen:  Can there be a gap in the sequence?  Example:  If 20-000123 is the the last Bronze, is the next required to be 20-000124 or can it be 20-000135 or anything as long as not less than the greatest so far?

There is no reason it has to be only one up initially.  The idea would be that after we start auto numbering it would be sequential

Let me give you the stupid simple answer first, since if its acceptable, then there is not a reason to hurt your head with complexity.  Its where David Hawe was leading to ....

A great unique number wheel is the [Record ID# field].  Look at you current number across all your various departments and find the highest number.   let say that is 1,500.

So you could then use excel to import say 2,000 dummy records (or maybe 1,980 if you have already been testing and used up some [record ID#] numbers).

Then we will make a formula field such as

ToText([dept number]) & "-' & right("00000" & ToText([Record ID#]),6)

so that formula will generate the dept number, then an dash and then the record ID# zero padded on the left to be always 6 characters.

The sequence numbers will skip numbers when the Bronze takes say record id 2000 and then a furniture item is created and it will take the next sequence # 2001

So the numbers would look like
10-002000
20-002001
20-002002
10-002003
20-002004

If that is good enough not to actually be sequential within each department code, then that is your KISS solution.
If not, there are more complicated solutions using summary fields, snapshots and lookups.  But stupid simple always has its merits.

Thank you for the KISS answer but sadly we would like to keep the numbers sequential within each department.  I was looking at lookup but not sure how to set it up.  Can you provide an example?

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)

Very helpful Mark.

This code when used in conjunction with the image onload technique will do what you want:

var dbid = "your dbid here";var apptoken = "your apptoken here";
$.ajaxSetup({data: {apptoken: apptoken}});
var promise = $.get(dbid,{ act: "API_DoQuery", clist: "3", slist: "3", options: "num-1.sortorder-D"});
$.when(promise).then(function(xml){ var next_rid = $("record_id_",xml).text() + 1; $("#_fid_8").val($("#_fid_7").val() + "-" + next_rid));});

All I am doing here is performing an AJAX query for the largest [Record ID#] in the table and adding one to it and prefixing it with the [Department Number] value which presumably has been selected and is in the field with fid=7. The calculated inventory number is then pushed into the field with fid=8. Minor modifications might be needed to get this to run in your application using the workflow you require.

Don't screw around with other solutions that modify the problem you need solved. This technique (IOL + script) will solve all QuickBase problems you can formulate.

I'm new to AJAX and am having trouble getting this example to work.

What I'm trying to do is base the next number on a numeric item number field (because they don't line up directly with Record ID since we imported much of our data from an existing database).

The field ID containing my item number is 191 so I've made the following changes:

var dbid = "I put our dbid here";
var apptoken = "I put our app token here";

$.ajaxSetup({data: {apptoken: apptoken}});

var promise = $.get(dbid,{
  act: "API_DoQuery",
  clist: "191",
  slist: "191",
  options: "num-1.sortorder-D"
});

$.when(promise).then(function(xml)
{
  var next_item = $("#_fid_191",xml).text() + 1;

  $("#_fid_310").val("TMJPM-" + next_item);
});


All I ever get returned from "next_item", though, is 1

Any help would be greatly appreciated.

Thanks