QuickBase Reaches Escape Velocity

QuickBase Reaches Escape Velocity



I would like to show you a new demo that highlights an amazing new way of introducing script into QuickBase using Rich Text Formula Fields that is (1) self-contained, (2) extremely flexible and (3) simple to use. This demo builds on other recent forum postings and demos but provides a clever way to get around a few technical limitations the current QuickBase platform has. QuickBase could easily correct these limitations in a future version of the product but I will show you how you can apply the technique today. It is great that this demo lands just before the start of the year as I can honestly say QuickBase's capabilities using this and related techniques are going to explode and leave the bounds of this planet in 2019.

For the demo I will show you how to implement the Haversine function - yes the same name that my business is named after. You probably never studied the Haversine function in High School trigonometry class but it is used today to calculate distance between two points on a sphere (eg your planet Earth) and has the following definition. Here is the Haversine function definition:



Clearly this function can't be implemented in the QuickBase formula language because there are no trigonometric functions. It can of course be implement in JavaScript and I will demonstrate how as well as indicate how any JavaScript function can be integrated into the QuickBase formula language.

I have implemented the Haversine function in a ES6 Module saved in a code page:

function haversine(x) {
  return (Math.sin(x/2))**2;
}
export {haversine};

Haversine.js Code Page
https://haversineconsulting.quickbase.com/db/bn9494bih?a=dbpage&pagename=haversine.js

Now this ES6 Module is short and exports only one function but one point of this demo is to show you how an arbitrary long module exporting multiple functions or objects could be saved in a Code Page and used within a specially crafted Rich Text Formula Field.

Now here is the actual Rich Text Formula Field definition written using the new 3Q&S Technique:

"<img src" &
" data-x='" &  & "'" &
" onerror='

(async () => {

  const {haversine} = await importCodePage('bn9tk69aj', 'haversine.js');

  let x = this.dataset.x;
  let hx = haversine(x);
  this.outerHTML = hx;

  //helper function - don't change the code below:
  async function importCodePage(dbid, codepage) {
    const moduleCode = await fetch('${dbid}?a=dbpage&pagename=${codepage}').then(r => r.text());
    const moduleSpecifier = 'data:application/javascript,${encodeURIComponent(moduleCode)}';
    return import(await moduleSpecifier);
  }

})();
'>"

Note everything in this formula is boilerplate except the five lines of code that are highlighted in bold! QuickBase could easily modify their formula editor to enter just the five lines of functional code and supply the remaining boilerplate through some back-end process you would never need to see the details of.

Here is the demo:

Haversine Function ~ ImportCodePage ~ Records List All
https://haversineconsulting.quickbase.com/db/bn94954a4?a=q&qid=1

You can of course create you own record and verify the Haversine formula calculation using Wolfram Alpha:





Here is the [Haversine(x)] field displayed in the formula editor:



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

Notes:

(1) I was stumped for a while while developing this code because I wrote the word ?don't? in a comment and didn't realize that it contained a single quote character. Once I replaced it with a backtick quote ?don't? everything worked as designed.

(2) This demo gets around the fact that code pages containing JavaScript are not served with the correct MIME Type from QuickBase. This is the third method that has been presented to solve the problem and probably is going to be the best method as you don't need to host the ES6 Module from a separate server or employ a Service Worker to correct the MIME Type.

(3) The trick to get this to work is to import a dynamically generated module specifier expressed as a dataURL. The correct MIME Type is introduced through the prefix at the head of the dataURL:

const moduleSpecifier = 'data:application/javascript,${encodeURIComponent(moduleCode)}';

(4) The technique demonstrated in this demo is extremely powerful. There is no limit to what this technique can do as you can import any ES6 Modules you wish (from an external CDN or QuickBase Code Page) and use arbitrary JavaScript within the Rich Text Formula Field to perform calculations on field values or change the page's behavior.