Link Search Menu Expand Document

Formula language

Formulas for calculated or computed fields, show/hide formulas and validation formulas all rely on JSONATA.

Each row in a microapp will be represented as a JSON object based on field label: field text value.

For example, if we have a row in a micro app with ProductPrice and Quantity, we have a JSON object like:

{
   Product:"Widget 1",
   Quantity: 3,
   Price: 5
}

You could then do things like:

  1. add a Total (formula field type) and set the formula to Quantity * Price
  2. add a validation formula on quantity: Quantity < 10
  3. add cross field validation on Price something like (Quantity * Price) < 100

Example use custom validation for a phone number regex:

Phone ~> /^[+]*[(]?[0-9]{1,4}[)]?[-\s\./0-9]*$/

Where field name is Phone

image

Example show to owners and authors only:

user.highestRole = "owner"  or user.highestRole = "author"

image

If the field is of a type that gives you an array… for example field type is Checkboxes, then you can do things like "A" in Checkboxes for the formula, if you wanted to check if “A” was selected and use that to show/hide a field.

The default meta data includes the following variables:

  • _id : the current rows unique ID\
  • userID: the user ID of the the user who created the row or the creator\
  • viewers: an array of additional user IDs who can view the row, set by a viewers field type\
  • isLocked: if the row has been locked\
  • submitted: Epoch date time value of the submitted date at the time the server created the record, which may be different to clientSubmitted (see below)\
  • deviceID: capture the device ID, to help debug if a specific device has issues\
  • appVersion: app version of when row was created, to help debug any issues.\
  • hasConflict: an array of conflicts\
  • clientCounter: useful for debugging what records have been sent to the server vs on the client.\
  • clientSubmitted: Epoch time client submitted the record. This may be different to submitted, which is the time the server received and created the record.

Displaying a button based on a valid email you can use a “show” based on regex expression, for example

(Email != null ) and $exists(Email) and  ($match($string(Email),/^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$/i))

Where “Email” is an email text field