Discussions

Expand all | Collapse all

how to automate assigning case numbers with each new record starting with 19001

  • 1.  how to automate assigning case numbers with each new record starting with 19001

     
    Posted 12-06-2018 14:28
    When creating a new record, I would like QB to automatically assign the next sequential number.  There are several users and each lab/webinar we do, must be assigned a C-XXXXX number.  Currently we access an excel spreadsheet visible to all so we now what number is up next.  I need to start the new year with C-19001.......  How do I go about automating sequential numbering.


  • 2.  RE: how to automate assigning case numbers with each new record starting with 19001

    Top
    Contributor
    Posted 12-06-2018 18:54
    I created a demo for you so you can bumpit, bumpit good:



    Bump It - Bump It Good ~ Add New Record
    https://haversineconsulting.quickbase.com/db/bn8cncrym?a=nwr

    This demo uses the newly announced Three Quotes and a Script (3Q&S) technique and can easily be adopted to bump other counters and a wide range of fashionable coiffures :
    "<img src onerror='
    var dbid = 'bn8cncc8q';
    var dbidRecords = 'bn8cncrym';
    var apptoken = 'xmcr2wc48de5cb6kr2p4dht2vyu';
    $.ajaxSetup({data: {apptoken}});
    var fidCounter = '6';
    $.get(dbidRecords, {
      act: 'API_DoQuery',
      qid: '1',
      clist: fidCounter,
      slist: fidCounter,
      options: 'num-1.sortorder-D',
      fmt: 'structured'
    }).then(function(xml) {
      var counter = parseInt($('records record f[id=${fidCounter}]', xml).text().split('-')[1]);
      window['_fid_${fidCounter}'].value = 'C-${counter + 1}';
    });
    '>"

    Pastie Database
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=701


  • 3.  RE: how to automate assigning case numbers with each new record starting with 19001

     
    Posted 12-06-2018 19:38
    Thanks Dan...this is perfect!


  • 4.  RE: how to automate assigning case numbers with each new record starting with 19001

    Top
    Contributor
    Posted 12-06-2018 19:48
    Glad you like it - more to come. That code should be wrapped in an IEFE so as to avoid clobbering a QuickBase global variable:
    "<img src onerror='

    (function() {

      var dbid = 'bn8cncc8q';
      var dbidRecords = 'bn8cncrym';
      var apptoken = 'xmcr2wc48de5cb6kr2p4dht2vyu';
      $.ajaxSetup({data: {apptoken}});

      var fidCounter = '6';

      $.get(dbidRecords, {
        act: 'API_DoQuery',
        qid: '1',
        clist: fidCounter,
        slist: fidCounter,
        options: 'num-1.sortorder-D',
        fmt: 'structured'
      }).then(function(xml) {
        var counter = parseInt($('records record f[id=${fidCounter}]', xml).text().split('-')[1]);
        window['_fid_${fidCounter}'].value = 'C-${counter + 1}';
      });

    })();

    '>"


  • 5.  RE: how to automate assigning case numbers with each new record starting with 19001

    Top
    Contributor
    Posted 12-06-2018 19:27
    This may screenshot may help understand what's going on:



    In a nutshell we are using a native RichText Formula field consisting of an <img> tag without specifying an image. Since no image is specified a loading error is generated resulting in the execution the onerror handler. The formula and onerror handler is specially crafted using three different quote characters which allows us to write our JavaScript in a readable fashion by simply using backtick quotes (ie ') in the JavaScript. What could be easier?


  • 6.  RE: how to automate assigning case numbers with each new record starting with 19001

     
    Posted 12-06-2018 19:38
    Great.  This is what I needed. 


  • 7.  RE: how to automate assigning case numbers with each new record starting with 19001

    Posted 12-06-2018 19:46
    Deb,
    Another solution is to simply use the [Record ID#] to make a formula field to make a sequential number based off the Record ID# field.

    ie "C-" & ToText([Record ID#])


    It will not start with the Year number. You can import from excel and delete enough records to roll the record ID# ahead to say 30,000 and just count forward from there.

    You may want to number based on the year, but there may really not be a good reason to do that, other than that is always how you used to do it.

    So, I'm just offering up an alternative solution.


  • 8.  RE: how to automate assigning case numbers with each new record starting with 19001

    Bronze
    Contributor
    Posted 12-07-2018 00:46
    You could as well do arithmetic to the Record ID# before converting to text, no?


  • 9.  RE: how to automate assigning case numbers with each new record starting with 19001

    Posted 12-07-2018 00:50
    Y es, but in these situations I always feel better if the [Record ID#] is the Order number or claim number, whatever the table represents. I just think it�s worth taking the five or 10 minutes to load up an Excel sheet import a bunch a records and delete them. It is like once in your life that you spend is 10 minutes and then for the eternity there�s no confusion over the difference between those two numbers. For example maybe the user is doing a search on order number 12345. This way they will only get one yet unless that number happened to appear in some other field.


  • 10.  RE: how to automate assigning case numbers with each new record starting with 19001

    Bronze
    Contributor
    Posted 12-07-2018 00:51
    I understand. Good point. Thanks for the answer.