Forum Discussion

DebLeli's avatar
DebLeli
Qrew Trainee
6 years ago

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

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.
App builders
reports and charts

9 Replies

Sort By
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    6 years ago
    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
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      6 years ago
      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}';
        });

      })();

      '>"
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    6 years ago
    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?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    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.
    • AlexCertificati's avatar
      AlexCertificati
      Qrew Cadet
      6 years ago
      You could as well do arithmetic to the Record ID# before converting to text, no?
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      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.