Formulas

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

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

For example, if we have a row in a Datatable with Product, Price 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\

• _currentUrl: gets the current URL from the browser - only works in forms in "insert/capture" mode.

• _contextRow: if there is a context row it returns that row. This allows you to access that rows fields, or example _contextRow.someField in your formulas. A example where this could help - eg showing a product and then having a sub-form to submit an order of quantity, color etc for that product and you want the order to include the product it (eg _contextRow.productID)

• userID: the user ID of the the user who created the row or the creator

• _currentUserID: the user ID of the currently logged in user - eg used in a display formula to show something different for the logged in user

• 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

In addition to the current row's date you can also access some basic information about the currently logged in user:

• user.name: the name of the currently logged in user

• user.email: the email address of the currently logged in user

• user.highestRole: the highest role of the currently logged in user

Note, depending on the specific application you are building, typically, you will create a separate user datatable to capture user custom information.

Please ensure you adhere to both Buzzy's terms and conditions as well as other external restrictions and compliance requirements, like GDPR, HIPAA etc related to other privacy and compliance when exposing and using user related data.

---