Scripting in formulae (2.7 enhancements)

steve · October 30, 2011 11:22AM

Omniscope 2.6 introduced support for basic scripting in JavaScript within formulae using the SCRIPT function:


SCRIPT(`
  if (a < b) {
    "a is lower";
  } else if (b < a) {
    "b is lower";
  } else {
    "a equals b";
  }
`, "a", 5, "b", 7)

But this required you to pass any data-driven values to the script using a list of additional arguments to the SCRIPT function. This can be quite limiting.

New from 2.7 b100: data-driven scripting functions, as introduced for the new script pre-processing feature of the Content View, inside the SCRIPT formula function:


SCRIPT(`
  // Running total of Coupon field for all Categories up to the current Category:
  var array = subset_uniquesList("Category");
  var total = 0;
  var curval = refVal("Category");
  for (var i in array) {
    var val = array[i];
    total += subset_sum("Coupon", subset("Category", val));
    if (val==curval) break; // Stop here
  }
  // Result value:
  total;
`)

(Note also the use of back-quotes in formulae, making it easier to use regular quotes inside the script)

For a full list of supported functions, look for those with "In script" shown in the functions guide.

November 1, 2011 3:45PM

Very cool :) Just need to find a few spare days to play with all this new scripting (including the Content view updates)

schergr · December 13, 2011 4:58PM

How do you refer to the row before?

steve · December 14, 2011 6:36AM

Omniscope is a columnar database and formulas are designed primarily for use in a row-independent way - for example, field1 = [field2] + [field3].

Unlike Excel, references are simply the field alone, rather than column:row references.

You can identify subsets of the data using values or (in 2.7) using row index ranges. You can then extract metadata from those subsets. In this way it is possible, though cumbersome, to get values from an earlier record, for example:
SUBSET_SINGLETON([someOtherField], SUBSET([dateField], DATEADD([dateField], -1, "week"))
SUBSET_SINGLETON([someOtherField], ROWINDEXSUBSET(CURRENTROW()-1))

However you cannot have a formula field refer to the same formula field or other circular field references. So you cannot create a running total by using field1 = (previous record's field1 value) + 1 (pseudo-code). This wouldn't make sense, anyway, because unlike Excel, *every* cell in a column shares the same formula. Instead you would need to use the RUNNINGTOTAL function in a 2nd field: field1 = RUNNINGTOTAL([field2]).

Note: for the ROWINDEXSUBSET example, this requires 2.7, and needs an additional IF check for the case of the first row.

See the functions guide for more detail on these functions.

steve · July 16, 2012 12:17PM

Please note that scripting functionality is now documented here:
http://www.visokio.com/kb/scripting

Welcome!

Categories

Ideas Parade