Formula Parser In JavaScript

  • 1
  • 2
  • Question
  • Updated 2 years ago
  • Doesn't Need an Answer
THERE IS AN ASK OF YOU AT THE BOTTOM OF THIS POST - SKIP THE DETAILS IF YOU DON'T UNDERSTAND MY TECHNICAL RATIONALE

As some of you may be aware I am deep into creating a QuickBase Formula Parer in JavaScript that will parse and evaluate a QuickBase formula in client-side JavaScript. So a random formula (taken from a community posting) such as this:
If([TASK COMPLETE] and IsNull([COMPLETE DATE]) = "",  Today(),
  [COMPLETE DATE]
)
... will get converted into a JSON syntax tree such as the following (which is essential for the evaluation of the formula inside the browser):
//Input parsed successfully.
[
   {
      "type": "Call",
      "name": "If",
      "arguments": [
         {
            "type": "binop",
            "operator": "and",
            "args": [
               {
                  "type": "Field",
                  "name": "TASK COMPLETE"
               },
               {
                  "type": "binop",
                  "operator": "=",
                  "args": [
                     {
                        "type": "Call",
                        "name": "IsNull",
                        "arguments": [
                           {
                              "type": "Field",
                              "name": "COMPLETE DATE"
                           }
                        ]
                     },
                     {
                        "type": "binop",
                        "operator": ",",
                        "args": [
                           {
                              "type": "Literal",
                              "value": "\"\""
                           },
                           {
                              "type": "binop",
                              "operator": ",",
                              "args": [
                                 {
                                    "type": "Call",
                                    "name": "Today",
                                    "arguments": null
                                 },
                                 {
                                    "type": "Field",
                                    "name": "COMPLETE DATE"
                                 }
                              ]
                           }
                        ]
                     }
                  ]
               }
            ]
         }
      ]
   }
]
Everything is going swimmingly but there is a massive amount of testing that has to be automated and one weird problem has reared its head. Surprisingly this issue deals with what are valid formula labels not the syntax of the formula language itself,

QuickBase allows you to create formulas labels with both the characters "[" and "]" in the label and allowing the "]" character in a field makes the parsing difficult. More importantly, you can't use such a field in the formula language without generating a parsing error in QuickBase's own formula parser. So the simple advice is never use "[" or "]' in your formula labels. But beyond that I need an understanding of what other characters are used in the wild in your QuickBase formula labels.

THE ASK:

You can help me out by running a very short script from the browser console on any of your large application and send me the output. All this script does is run through every field in your application and enumerate all the letters used in your field names.

Here is the script and the attached screenshot shows me running it against the QuickBase Support Center application:
var names = "";_.each(gFI, function(table, index) {
  _.each(table, function(field, index) {
    names += field.name;
  });
});
var counts = _.countBy(names, function(letter) {
  return letter;
});
console.log(JSON.stringify(counts, null, "  "));


Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,884 Points 20k badge 2x thumb

Posted 2 years ago

  • 1
  • 2
Photo of IGL

IGL

  • 20 Points
Like this?
{  "1": 1,
  "2": 1,
  "C": 19,
  "l": 35,
  "i": 30,
  "e": 83,
  "n": 28,
  "t": 62,
  "R": 16,
  "c": 21,
  "o": 33,
  "r": 16,
  "d": 26,
  " ": 51,
  "I": 10,
  "D": 23,
  "#": 7,
  "N": 6,
  "a": 37,
  "m": 18,
  "P": 5,
  "h": 3,
  "E": 2,
  "A": 5,
  "v": 2,
  "y": 5,
  "T": 8,
  "p": 6,
  "s": 11,
  "S": 5,
  "u": 11,
  "F": 2,
  "b": 11,
  "j": 1,
  "M": 3,
  "f": 2,
  "g": 2,
  "/": 2,
  "O": 1,
  "(": 1,
  ")": 1,
  "L": 1,
  "H": 1,
  "Q": 2
}
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,694 Points 20k badge 2x thumb
Yes but email it to me next time to not clog the forum. Most of the output will be pretty boring as I am looking for strange characters.
Photo of IGL

IGL

  • 20 Points
Does this help?
{  "2": 1,
  "R": 35,
  "e": 124,
  "c": 33,
  "o": 49,
  "r": 57,
  "d": 38,
  " ": 65,
  "I": 15,
  "D": 20,
  "#": 15,
  "C": 17,
  "m": 27,
  "p": 18,
  "a": 61,
  "n": 23,
  "y": 12,
  "N": 10,
  "F": 1,
  "u": 14,
  "l": 32,
  "O": 3,
  "t": 68,
  "i": 14,
  "P": 16,
  "j": 14,
  "E": 5,
  "s": 18,
  "S": 12,
  "M": 4,
  "g": 2,
  "T": 12,
  "H": 1,
  "A": 2,
  "f": 2,
  "b": 5,
  "k": 6,
  "_": 2,
  "B": 1,
  "U": 5,
  "-": 1,
  "x": 2,
  "L": 1,
  "v": 1,
  "W": 2,
  "h": 2
}
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,694 Points 20k badge 2x thumb
Thanks to all how sent me a reply. I have the issue resolved now for all practical purposes.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,884 Points 20k badge 2x thumb
NEW ASK

I want your most complex formulas for testing my parser. I don't need any field values or other information just the raw formula as pasted into the textarea of the field editor page. Feel free to include formula variables, comments, poor formatting or even a formula that does not work.

You can enter your formulas here:

https://haversineconsulting.quickbase.com/db/bmn6xsqv2?a=nwr

Please only contribute formulas that are not confidential as at times others may view the submissions and I will be using them for testing with other developers. 

I have added all the formulas listed in QuickBase's formula reference table but these formulas are rather simple. When entering your contributed formuls please selected the option "Contributed" for the Source field.

As this project progresses I will provide a script to extract all formulas from an application or perhaps even an account if you want to contribute to my testing efforts on a larger scale.

Thanks in advance.
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,884 Points 20k badge 2x thumb
FWIW, my parser now works on all the examples in the formula function table

https://login.quickbase.com/db/6ewwzuuj?a=td

I only had two small errors dealing with unary operators and numbers starting with a leading period which were quickly corrected.

On the other hand I found at least four typos in QuickBase's documentation:

Nz([Mon]) + Nz([Tues] + Nz([Wed]) + Nz([Thurs]) + Nz([Fri])
https://login.quickbase.com/db/6ewwzuuj?a=dr&r=p

To WeekdayN([Order Date])
https://login.quickbase.com/db/6ewwzuuj?a=dr&r=e3

Nz([Length of Project], [Estimated Length of Project]
https://login.quickbase.com/db/6ewwzuuj?a=dr&r=fa

Month(ToDate("Jan 10, 2000") 
https://login.quickbase.com/db/6ewwzuuj?a=dr&r=9

Can you spot the errors?

ASK: Please Give Me Your Formulas
https://community.quickbase.com/quickbase/topics/formula-parser-in-javascript#reply_18555784
(Edited)
Photo of Drew

Drew

  • 432 Points 250 badge 2x thumb
formula added. 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,884 Points 20k badge 2x thumb
Thanks - this is extremely helpful to sound out errors and helps me refine the grammar.

Object nesting exceeded jsDump.maxDepth (100)
https://haversineconsulting.quickbase.com/db/bmn6xsqv2?a=dr&rid=332

FWIW, I think your formula could probably be naively improved.

And I am curious: are the hyphens in your formula labels being introduced by QuickBase relationships or did you explicitly name these formulas with hypens?

Either way, the first thing I would do to improve your formula natively would be so simplify the names so I could hold it in my head all at once. In this an other areas of QuickBase there is a lot of noise being generated in the naming of fields either due to tables having 1000s of fields orautomatically naming fields associated with relationships.
(Edited)
Photo of Drew

Drew

  • 432 Points 250 badge 2x thumb
For full disclosure this formula was created a number of years ago before I had integrated MS Sql Server into our processes - I've since removed spaces from names.  Those hyphens were not due to relationship naming, they were entered that way.  I typically rename all relationship lookup fields from what Quickbase creates.  The process that this particular formula is involved in has 1000+ fields involved, some of which contain values entered / calculated in quickbase, some with values from our legacy system etc.  Was my first run at that type of thing and had to come up with a naming convention to keep it all straight once in quickbase.  I do it somewhat differently now.    
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,884 Points 20k badge 2x thumb
New ASK:

Formula Extraction Script

I have a project underway that will allow QuickBase formulas to be converted to JavaScript. The project involves writing a grammar for QuickBase's formula language and parsing them using the parser library PEGjs (https://pegjs.org/). The grammar is written and I am now moving on to other aspects of the project which includes a massive amount of testing using a formal test framework. I need to test both valid and invalid formulas written with best practices as well as sloppily written formulas. I need thousands of formulas from a variety of sources for my testing.

You can assist my efforts if you wish by running a short script that will extract all the formulas from your application and after review send them to me as a CSV file. I do not need any table data for my testing or knowledge of your field types – all I need is the raw formula as entered into the textarea of the field definition page.

If you have formulas that are not confidential and wish to help my efforts please follow the five step procedure listed on the following pages.

Thanks for your assistance.

Step 1

Temporarily turn off application tokens for your script:



Step 2

Press F12 to expose the Developer Tools and click on the console tab. The Developer Tools may appear at the bottom, side or in a seperate window depending on the state you left it in on after a prior usage.



Step 3

Paste the following code into the console, press enter and wait for an “all done” message to be displayed.

var csv = "";
var subdomain = document.location.host.split(".")[0];
$.ajaxSetup({async: false});
var dbids = Object.keys(gTableInfo);
dbids.forEach(function(dbid) {
  $.get(dbid, {
    act: "API_GetSchema"
  }).then(function(xml) {
    $("fields field[mode=virtual]", xml).each(function(index, field) {
      var fid = $(field).attr("id");
      $("formula", field).filter(":not(:empty)").each(function(index, field) {
        var formula = $(field).text();
        csv += `"Client","${subdomain}","${dbid}","${fid}","${formula.replace(/"/g, '""')}"\n`;
      });
    });
  });
});
$("<textarea>").css({width: "100%", height: "300"}).html(csv).prependTo("body");
console.log("all done");

Here is what should appear in the console after you paste your code:


Step 4

When the script completes it will splice a textarea at the top of the page with all formulas listed in a blob of csv data. After review, copy this CSV data into a file, save it and email it to me as a file attachment.



My sample data csv contains four simple numeric formulas although your formulas will probably be much longer and slightly more difficult to recognize as CSV data. 

"Client","haversineconsulting","bmqfp5qif","9","[a]
+ [b]"
"Client","haversineconsulting","bmqfp5qif","10","[a] + [b] + [c]"
"Client","haversineconsulting","bmqfp5qif","11","[a] + [b] * [c]"
"Client","haversineconsulting","bmqfp5qif","12","[a] * [a] * [a]"

The fields in this CSV format are:

  • Source
  • Subdomain
  • DBID
  • FID
  • Formula
Step 5

Turn application tokens back on!

Even if you choose to not share your formulas I think you will find this exercise useful to review your own formulas. You can import the CSV data you generate into your own table and then review your accumulated formulas. Normally formulas are worked on in isolation one at a time. When you view your formulas en masse you can recognize obvious patterns that will allow you to improve your formula's readability and identify reusable patterns. When you review thousands of formulas as I am doing you get enormous insights into the formula language and best practices as well as pave the way for the replacement supplementation of the formula language with JavaScript.

(Edited)