Discussions

 View Only
Expand all | Collapse all

Auto Numbering unique inventory items by department?

David Hawe

David Hawe10-12-2013 22:07

  • 1.  Auto Numbering unique inventory items by department?

    Posted 10-10-2013 17:42
    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 number
    Expample

    10-000111 would be one department
    20-000234 would be a second department

    The format has been used for many years and it would be difficult to change it. Also, we must start with the most current number for each department.



  • 2.  RE: Auto Numbering unique inventory items by department?

    Posted 10-11-2013 14:36
    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.


  • 3.  RE: Auto Numbering unique inventory items by department?

    Posted 10-11-2013 15:36
    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.


  • 4.  RE: Auto Numbering unique inventory items by department?

    Posted 10-11-2013 15:59
    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?


  • 5.  RE: Auto Numbering unique inventory items by department?

    Posted 10-11-2013 17:31
    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


  • 6.  RE: Auto Numbering unique inventory items by department?

    Posted 10-11-2013 22:05
    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.


  • 7.  RE: Auto Numbering unique inventory items by department?

    Posted 10-12-2013 14:27
    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?


  • 8.  RE: Auto Numbering unique inventory items by department?

    Posted 10-12-2013 18:50
    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)


  • 9.  RE: Auto Numbering unique inventory items by department?

    Posted 11-07-2017 18:43
    Hello, I am trying to do this exact same thing, but the link doesn't work (https://yqc.quickbase.com/db/bigy8ejyu). Would it be possible to see this solution?


  • 10.  RE: Auto Numbering unique inventory items by department?

    Posted 11-07-2017 18:52
    I had a look and that app of mine does not seem to exists any more.


  • 11.  RE: Auto Numbering unique inventory items by department?

    Posted 11-07-2017 20:38
    I got it to work. Posting a picture of the relationships and fields should anyone else run into this


  • 12.  RE: Auto Numbering unique inventory items by department?

    Posted 11-07-2017 20:46
    Thx for posting for the good of the Community.


  • 13.  RE: Auto Numbering unique inventory items by department?

    Posted 11-09-2017 18:31
    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.


  • 14.  RE: Auto Numbering unique inventory items by department?

    Posted 10-12-2013 22:07
    Very helpful Mark.


  • 15.  RE: Auto Numbering unique inventory items by department?

    Posted 10-12-2013 22:47
    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.


  • 16.  RE: Auto Numbering unique inventory items by department?

    Posted 09-30-2014 15:59
    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


  • 17.  RE: Auto Numbering unique inventory items by department?

    Posted 09-30-2014 16:21
    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);
    });


  • 18.  RE: Auto Numbering unique inventory items by department?

    Posted 09-30-2014 16:49
    (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.


  • 19.  RE: Auto Numbering unique inventory items by department?

    Posted 09-30-2014 16:51
    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.


  • 20.  RE: Auto Numbering unique inventory items by department?

    Posted 09-30-2014 16:54
    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


  • 21.  RE: Auto Numbering unique inventory items by department?

    Posted 09-30-2014 18:01
    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."


  • 22.  RE: Auto Numbering unique inventory items by department?

    Posted 09-30-2014 18:06
    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.


  • 23.  RE: Auto Numbering unique inventory items by department?

    Posted 09-30-2014 19:21
    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.