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

Debug your code:

$.when(promise).then(function(xml) {
  console.dirxml(xml);
  console.log($("#_fid_191",xml).text());
  var next_item = $("#_fid_191",xml).text() + 1;
  $("#_fid_310").val("TMJPM-" + next_item);
});

(Thank you for such a fast response!)

If I'm debugging correctly this is the response I'm getting:

'Attr.nodeValue' is deprecated. Please use 'value' instead.

There is no XML response showing up in your post. The string "'Attr.nodeValue' is deprecated. Please use 'value' instead." is a diagnostic message to the console dealing with some aspect of the code you wrote or is possible the request of QuickBase's code.

Agreed.
After responding, I added some console.log("test"); lines before and after the

console.log($("#_fid_191",xml).text());

statement and it's showing empty row

Update: It does actually have the XML response in the console.

This is the errtext I received:

"This operation is only supported on individual tables and not on the application itself. Please check the URL."

You are probably specifying the application dbid instead of the table id on this line of code:

var dbid = "your dbid here";

I sometimes deviate from my own coding practices to save time. I would adopt the convention of writing the dbid's as follows:

var dbid = "your application dbid";
var dbidTable1 = "your Table1 dbid"
var dbidTable2 = "your Table2 dbid"

Also, it would help if you posted a new question when the thread is so old (a year in this case). It becomes diffiucult to read through the whole thread and parse out what the new issue is. Just concentrate on the unique aspects of your question.

Success!!

I did pull the application dbid by mistake.

Thank you very much for your help and feedback.

I will be sure to post as new questions in the future.