how do I create a secondary counter?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

In my order tracking app I have two kinds of orders, regular and special. Regular makes up 80% of the orders, special the remaining 20%. I use the record ID as the order number for all. I need a field that will assign the special orders a unique and sequential number. The result would be thus: Five regular orders come in, the next is a special and the "Special Order Counter Field" assigns "S-00001". Five more regular orders come in, then a special order which is assigned "S-00002", and so on. Is this even possible, and if so, how so? Thanks in advance for your thoughtful assistance.

Photo of Ryan

Ryan

  • 0 Points

Posted 5 years ago

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

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
Use the image on load technique on the add new record form:

Pastie Database

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=291


Supply your (1) dbid, (2) apptoken and (3) fid for the [Special Order Counter Field]. When the form loads an AJAX query will return the "largest" value of the [Special Order Counter Field], increment it and stuff it into the <input> specified by fid.
Photo of Ryan

Ryan

  • 0 Points
see comment below
Photo of Ryan

Ryan

  • 0 Points
This is what I have in a "formula text" field,

var dbid = "bij8c5gkp";
var apptoken = "lkdsl5fa2drtvteby9evbg";
var fid = "262";
$.ajaxSetup({data: {apptoken: apptoken}});
var promise = $.get(dbid, {
  act: "API_DoQuery",
  qid: "1",
  clist: fid,
  slist: fid,
  options:  "num-1.skp-0.sortorder-D",
  fmt: "structured"
});
$.when(promise),then(function(xml) {
  var nextSparkOrder =  (parseInt($("record f#"+ fid, xml)[1], 10) + 1);
  $("#_fid_" + fid).val(nextSparkOrder);
});


AND, this is the error QB returns:

A variable declaration must be followed by a type.

(everything after the top "var" is underlined and in italics)
I am sure I have done something wrong, and would love to be shown what.

BTW, I used a formula text field because that seemed to be the only way
to get the "image on load" field to work. It occurs to me that you may think
that I am a lot smarter than I actually am. Don't worry about my being
offended if you "dumb down" your explanations, I won't be.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
Note: This answer is long, highly formatted and technically detailed so I may well be editing it on an ongoing basis as I wordsmith it or correct typos.

Pastie version of this long answer: https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=294

>I am sure I have done something wrong,

This can be confusing if you don't understand all the technical details. The provided code does not go into a formula - rather it goes into a user defined page (typically named module.js) and a special formula is used to cause that page module.js to automatically load as if it was part of the source of the original form page. This technique is affectionately called the image onload technique because it employs writing a special formula that inserts an invisible image into the page and uses the onload attribute of that image to load the user defined page module.js. Despite the technical details, the procedure I describe below will allow anyone to setup the image onload technique. What follows is the "second" version of the image onload technique which improves on the first version but is functionally equivalent. Also note that this is a workaround to allow you to enhance forms and pages with additional features and capabilities while we lobby for QuickBase to come up with a supported method of allowing an administrator (not a mere user) to specify that a user supplied JavaScript file should be attached to a form/page for unrestricted purposes.

Step 1

Insert two user defined variables into your application named iol and /iol with the following definitions:

iol=

<img qbu='module' src='/i/clear2x2.gif' onload="javascript:if(typeof QBU=='undefined'){QBU={};$.getScript(gReqAppDBID+'?a=dbpage&pagename=

/iol=

&rand='+new Date().getTime())};">

These two variables are named so that when they are used in formulas they are reminiscent of HTML markup (see Step 3).


Step 2

Create a user defined page named module.js (or similar) and initially place in it the following single statement:

alert("module.js is now loaded - I now own your page");

We will replace this page's contents with some (1) boiler plate code and (2) custom code to implement various additional features. We are on training wheels for the time being.
Step 3
In any table where you want to use an image onload field (typically named [-]) create a text formula field with some HTML allowed using this formula:

[iol] & "module.js & [/iol]

This formula uses the two user defined variables [iol] and [/iol] to simplify the formula and allow you to easily remember the formula if you choose to use it in other tables. Once the user defined variables [iol] and [/iol] are set up you can create additional image onload fields in other tables and forms with similar definitions:
[iol] & "moduleJumpTheShark.js" & [/iol]
You will of course have to create a new user defined page named moduleJumpTheShark.js for this additional instance.

Step 4
In the field properties page for the image onload field [-] configure the following properties:

Check:
  • Allow some HTML tags to be inserted in the field (Already set in Step 3)
Uncheck:
  • Include this field when searching/filtering this table
  • Reportable Add this field to all new reports
  • The field may be used in reports
Step 5
Place the image onload field [-] on the form you want to enhance. It does not matter where you put the field because we will make all efforts to insure the the field's inclusion on the form is not visible. Set the field to have Alternate Label text but leave the Alternate Label text empty (so it will not be visible). What we are doing here is merely including an invisible image on the form for the sole purpose on forcing our user defined page module.js to load as if it was originally part of the QuickBase page.

You can additionally control which pages the image onload field is included on by setting the field property Display when this form is used for to one of the following selectable values:

  • edit

  • view

  • add

  • edit or add

  • edit or view

  • add or view

  • edit, add or view

This control including the image onload field on a {add, view, edit} form is a secondary mechanism to control when the image onload field is called into action to enhance the form's behavior. You can also include logic in the user defined page module.js to control the enhance form's behavior. As it stands now the user defined page merely includes an alert() statement to get you familiar with how the image onload technique works. Step 6

Now visit a form that has been configured to include the image onload field. What you should observe that the form loads in its normal fashion but automatically an alert displays. You now have control of your page and can customize and enhance it. This is where the creative juices will flow so it is time for a celebratory song:

http://goo.gl/xtWtnE


Step 7

Now that you have the image onload technique setup in your application you can modify the code in module.js to detect what type of page you are on and supply additional custom logic. The following is a generic template for module.js which can be used to detect which type of page is being displayed:

Pastie Database

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=293


Note that the logic in this generic template is only evaluated if you have configured the image onload field to be included on the form in in Step 5. Step 8

When I answer questions in the forum where I make reference to using the image onload technique all of the steps above are assumed to be undertaken and I typically only post the essential code that would be needed within any one branch of the generic template. This approach allows me to quickly develop "state of the art" customization techniques as questions come up without getting bogged down in endlessly repeating predicate information. Also you may well have to do some additional work or hire a developer if you want a production solution that meets your exact needs.

Step 9

The setup of the image onload technique can be automated. For details see this entry:

Is there a new "image onload technique" in town?


https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=242


Notes

  1. The use of user defined variables in formulas eliminates some tricky escaping you would otherwise have to do in writing formulas.
  2. The customary names used for the user defined page module.js and the image onload field [-] are not significant.
  3. The attribute qbu='module' added to the image onload field is actually not needed and is more of a historical artifact used during development. I retain it mostly to avoid coming up with new versions of the technique.
  4. The testing for the existence of QBU is only needed to prevent repeated triggers of module.js when the [-] field is included on reports and grid edits. I retain it mostly to avoid coming up with new versions of the technique.
  5. The inclusion of the &rand parameter is currently not needed and was included as a cache busting technique. QuickBase never explicitly states their caching policy so I thought it wise to include it. I retain it mostly to avoid coming up with new versions of the technique.
  6. The generic template uses an Immediately Invoked Function Expression (IIFE) - Google it if you want the details. In practical terms what this means is the variables you use within the IFFE will not interfere with QuickBase's global variables. However, you are free to dip into QuickBase's global name space and user their global variables (with great caution). I sometimes use the QuickBase global variable kRid which holds the decimal value of the [Record ID#] on View or Edit pages. Use this approach carefully as there is no guarantee that QuickBase will continue to use any global variables.
  7. If you inject anything in a QuickBase authored page you have to insure you do not conflict with QuickBase global variables. This includes element IDs, CSS classes, as well as JavaScript variables. The safest approach is to always prefix such constructs with the prefix QBU.
  8. Other stuff as I think of it ... Enjoy
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
FWIW, you have left out the code that appends the prefix "S-". Compare:

var nextSpecialOrder = "S-" + (parseInt($("record f#"+ fid, xml).text().split("-")[1], 10) + 1);
Photo of Ryan

Ryan

  • 0 Points
Alright and many thanks for taking the time to lay this out in a highly comprehendible way. I have a fairly firm grasp on the basic mechanics now. Getting back to the secondary counter, the prefix "S" in my original Q was just to assist the communication, I didn't really want it in the final product. If you could assist me just a bit more, I want to be able to use that first script you wrote to generate the special order counter. It seems that the script goes into the module.js page, correct?  I need to know how to get the number to display in a field. Preferably when my special order formula checkbox has been triggered (which already works now). What should I do now?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
This statement in the original code is responsible for stuffing a value (nextSparkOrder) into the field identified by fid:

$("#_fid_" + fid).val(nextSparkOrder);

>highly comprehendible way

It took a long time with all the formatting so I am going to shut down on QuickBase work for the day.
Photo of Chris

Chris

  • 0 Points
Dan, thanks for your post...elegant method as usual !

As I understand, this method creates an event (control xfer to a js page) when the field is encountered on a form.

In my case, I have a simple number-formula field. I need to write a formula to return a numeric value based on rather tedious analysis of a  text paragraph contained in the text field, [some text]. It looks like it would be a mess to perform that analysis within the confines of the usual QB formula functions. Therefore, I'm trying to figure out if there is a way to create a number-formula field, [calc. number], that passes a field value, [some text], to a js page that calculates the numeric result and passes it back to the [calc. number] as its calculated value. Any thoughts? Thanks.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
> Any thoughts?

Adding the image onload field [-] to a form or report causes a user defined page (typically named module.js) to immediately load as if that JavaScript file was part of the original page QuickBase served. The code in module.js can determine what type of page you are on and listen for events (using jQuery) and respond to them. So in your case if a user enters some blob of text you can listen for a blur event (input looses focus) and then read the text, modify the text and do something with the text - like stuff the modified text back into the original field or parse it out and stuff the parts into other fields. Some simple applications might be to automatically capitalize whatever the user entered, strip redundant white space, substitute abbreviations or expand short codes. See this entry and the application within for example of expanding a military time shortcode:

https://quickbase-community.intuit.com/questions/1191193

Once your JavaScrpt  is in control you can do anything you want. This is not an exaggeration. Start a new thread if you have a specific question so I can score some more forum points and win a free trip to Brazil for the Mas Que Nada QuickBase User Group Conference.