Logmedo.com - Do It. Log It.

Using JavaScript in Formula Attributes

"Formula" attribute is a special type that can evaluate a combination of values of other attributes, and can contain mathematical operators and functions. Formula attribute can be as simple as "Total Distance/Fuel" to compute miles per gallon (mpg), as shown above in the "Fuel Records" section of the "Vehicle Logbook", or can be a complex JavaScript code.

NOTE: The JavaScript is evaluated on the server,
and does not have access to the HTML DOM (Document Object Model).

For example, in the above "Vehicle Logbook", the value of the field "mpg" is calculated with the following JavaScript:

// get the value of the "Total Distance" attribute for the current row
var totalDistance = ROW_ATTR('totalDistance');

// get the value of the "Fuel" attribute for the current row
var fuelUsed = ROW_ATTR('fuel');

var currentMpg = (totalDistance/fuelUsed).toFixed(1);
// output the value
currentMpg;

For the first row, the above expression would expand to:

var totalDistance = 175;
var fuelUsed = 8.8;
=> currentMpg = 19.9

NOTE - each user-defined attribute needs to have a unique identifier that is used in formula expression. Normally, this will be automatically computed from name of the attribute, e.g., the name Total Distance will result in an identifier called totalDistance. However, you may choose to use a different identifier. The identifier needs to be unique within its table.

For example, in the Vehicle Logbook example, the identifier for the attribute Fuel, fuel, is unique to the table Fuel Records. The valid characters for an identifier are the letters a through z, the numbers 0 through 9, the $ (dollar) symbol, and _ (underscore).

The value of the last entry in the formula expression is displayed in the cell. If you want to format the value (e.g., show it in a different color), use the function WRITE().

In addition to referring to simple attributes defined in the same table, formula expression can also refer to attributes that are defined in other tables belonging to the same app. In the Vehicle Logbook example, in the table Fuel Records, the column "My Car" refers to the table "My Car". For a given row, the entry linked to the other table can be gotten by referring to it as:

// get the JSON representation of the car at the current row
var vehicleJSON = ROW_LINKED('myCar');

List of available functions

The following JavaScript functions are available for loading and manipulating entries (The sample Vehicle Logbook is used in the examples):

ROW_ATTR(identifier) - Returns the value of the attribute with the given identifier for the current row.
COL_SUM(identifier) - For attributes with numerical values, returns the sum total of the column.
COL_AVG(identifier) - For attributes with numerical values, returns the average value of the column.
ROW() - Returns the current row representation as JSON object. Note that this returns a JSON object and not a JSON string. For example:

{
  "id":"080c8376-8d49-4dd5-a0b6-60b8b75b93a1",
  "dateCreated":"2013-05-13T01:57:30Z",
  "lastUpdated":"2013-05-13T01:57:30Z",
  "date":"2013-02-06",
  "starred":false,
  "notes":"",
  "attr":[
 {
    "identifier":"fuel",
    "id":"daa7c054-f6ac-45f0-894e-2bbe5c72d62e",
    "columnType":"DOUBLE",
    "value":16.5,
    "name":"Fuel"
 },
 ...
 ...
  ],
  "association":{
    "resource":[
      {
        "identifier":"car",
        "schemaId":"e03f42e1-23ce-102e-9630-327cd4b40910",
        "id":"e03f42e1-23ce-102e-9630-327cd4b40910",
        "name":"Honda Pilot"
      },
      ...
    ]
  }
}

ROW_NAME() - Returns the name of the entry at the current row for table of type Resource.
ROW_DATE() - Returns the date of the entry at the current row for table of type Activity.
ROW_CATEGORY() - Returns the category, if defined, of the entry at the current row.
ROW_NOTES() - Returns the notes, if any, of the entry at the current row.
ROW_LINKED(identifier) - Returns the given linked table, if any, for the current row as a JSON object. For example, ROW_LINKED('car') for the first row of the able "Fuel Records" returns:

{
   "identifier":"car",
   "schemaId":"e03f42e1-23ce-102e-9630-327cd4b40910",
   "id":"e03f42e1-23ce-102e-9630-327cd4b40910",
   "name":"Honda Pilot"
}

TABLE(identifier) - Returns the rows of the given table as a JSON object, up to a max of 500 entries. For example, TABLE("fuelRecords") returns up to 500 entries. You can also specify the number of entries to return. ROW({"id":"fuelRecords", max:2}) returns:

{
   "total":120,
   "max":2,
   "offset":0,
   "entries":[
      {
         "id":"1b38d2c5-856b-4a03-8bbf-766e8c2f5f20",
         "dateCreated":"2013-11-14T01:16:48Z",
         "lastUpdated":"2013-11-14T01:16:48Z",
         "date":"2013-11-12",
         "starred":false,
         "notes":"Stop & Shop 10c/gal discount",
         "attr":[
            {
               "identifier":"fuel",
               "id":"a8989c63-1e4d-4138-8715-177f25a6fc5d",
               "columnType":"DOUBLE",
               "value":15.2,
               "name":"Fuel"
            },
            ...
            ...
            {
               "identifier":"costMiles",
               "id":null,
               "columnType":"FORMULA",
               "returnType":"CURRENCY",
               "value":"",
               "name":"Cost/miles"
            }
         ],
         "association":{
            "resource":[
               {
                  "identifier":"car",
                  "schemaId":"e03f42e1-23ce-102e-9630-327cd4b40910",
                  "id":"e03f42e1-23ce-102e-9630-327cd4b40910",
                  "name":"Honda Pilot"
               }
            ]
         }
      },
      {
         "id":"819038be-40fb-4b20-a131-f42b507bf9b8",
         ...
         ...
      }
   ],
   "columnTotals":{
      "fuel":1887.3,
      "cost":6179.17,
      "totalDistance":34289
   }
}

WRITE(value) - Normally, the value of the last expression is displayed to the user. If you want to format the output using CSS, use this function. Note that WRITE needs to be the last statement. If there are multiple WRITE, only the last one has any effect. Ensure that the value being written is of the correct type. For example, if the formula attribute "return type" is defined as "decimal" and you write a text, the value will not be displayed on the page. The function WRITE takes in an optional second argument that you can use to specify CSS attributes to format how the output appears on the page. Example:

WRITE(currentMpg, {'color':'red', 'font-weight':'bold'});

MARKUP(text) - If the output text contains HTML tags, they will be escaped and you will see the raw HTML markup. To display proper formatted text, use MARKUP.

var hello = MARKUP("<b>Hello</b>");
WRITE(hello);

APP_NAME() - the name of the app.
USERNAME() - the username of the current user.
USER_EMAIL() - the email of the current user.

In addition to the above functions, you can also use the popular underscore.js library. You can also use moment.js for working with dates and time.

You can use standard JavaScript objects and functions. JavaScript HTML DOM functions and functions that reference browser objects are not available.

Tip: To see the output of any of the above functions, create a dummy formula attribute and use the standard JavaScript JSON.stringify() method to see the output. For example:

var car = ROW_LINKED('car');
// This will display the representation of car in JSON format.
JSON.stringify(car);