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

  • 0
  • 1
  • Question
  • Updated 3 days ago
  • In Progress
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.
Photo of Deb

Deb

  • 92 Points 75 badge 2x thumb

Posted 3 days ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,694 Points 20k badge 2x thumb
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
(Edited)
Photo of Deb

Deb

  • 92 Points 75 badge 2x thumb
Thanks Dan...this is perfect!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,694 Points 20k badge 2x thumb
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}`;
  });

})();

'>"
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,694 Points 20k badge 2x thumb
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?
(Edited)
Photo of Deb

Deb

  • 92 Points 75 badge 2x thumb
Great.  This is what I needed. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
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.
You could as well do arithmetic to the Record ID# before converting to text, no?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
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.
I understand. Good point. Thanks for the answer.