Formula Parser In JavaScript

Question

THERE IS AN ASK OF YOU AT THE BOTTOM OF THIS POST - SKIP THE DETAILS IF YOU DON'T UNDERSTAND MY TECHNICAL RATIONALEAs some of you may be aware I am deep into creating a QuickBase&nbsp;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]) = "",&nbsp; Today(),&nbsp; [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.[&nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; "type": "Call",&nbsp; &nbsp; &nbsp; "name": "If",&nbsp; &nbsp; &nbsp; "arguments": [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "type": "binop",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "operator": "and",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "args": [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "type": "Field",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "name": "TASK COMPLETE"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;},&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "type": "binop",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "operator": "=",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "args": [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "type": "Call",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "name": "IsNull",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "arguments": [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "type": "Field",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "name": "COMPLETE DATE"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;},&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "type": "binop",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "operator": ",",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "args": [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "type": "Literal",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "value": "\"\""&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;},&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "type": "binop",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "operator": ",",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "args": [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "type": "Call",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "name": "Today",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "arguments": null&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;},&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "type": "Field",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "name": "COMPLETE DATE"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; &nbsp; ]&nbsp; &nbsp;}]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&nbsp;QuickBase Support Center&nbsp;application:var names = "";_.each(gFI, function(table, index) {&nbsp; _.each(table, function(field, index) {&nbsp; &nbsp; names += field.name;&nbsp; });});var counts = _.countBy(names, function(letter) {&nbsp; return letter;});console.log(JSON.stringify(counts, null, " &nbsp;"));

igligl · Answer

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

igligl · Answer

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

_anomdiebolt_ · Answer

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.

_anomdiebolt_ · Answer

Thanks to all how sent me a reply. I have the issue resolved now for all practical purposes.

_anomdiebolt_ · Answer

NEW ASKI 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&nbsp;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=nwrPlease 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.&nbsp;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.

Forum Discussion

Formula Parser In JavaScript

Related Content

Delete Formula Button Error

Formula Assistance

Formula Url Buttons

Formula Query with custom key

URL formula -API_RunImport