It happens rather rarely, but for certain calculations, it may be necessary to continue calculating with the positive value of a possibly negative result. In such cases the function saves querying the value and, if necessary, changing the sign by multiplication.

Syntax

abs(number)

Return

number

Examples

Result: 9.3

acos

To calculate the arccosine

The function returns the arccosine of a number.

Syntax

acos(number)

Return

number

Examples

acos(x) To calculate the arccosine of a given number x between -1 and 1.

acos(-0.25)

Result: 1.8234765819369754

age

To return the number of full years between now and a given date

Use this function to calculate the current number of full years that have passed since a given date.

The most common application is certainly the calculation of the age of a person based on the date of birth, but other uses are also conceivable:

depreciation periods
special termination rights that might be possible after a certain term (e.g. real estate loan)
determination of anniversaries, etc.

The current date is always used for this calculation.

Syntax

age(date)

Return

number

Examples

Result: Age in years on the current day, starting from the value of the Date of birth field.

Result: 45 (on May 27, 2021)

appendTempFile

To add content to a temporary file on the Ninox server

This function adds the given content to a temporary file, which will be accessed via a given link created by createTempFile().

The respective file must have been created already and still exist when executed.

Use this function in combination with createTempFile() to render large or long-running exports.

The function can be executed only on the client. Learn more about execution context

Syntax

appendTempFile(link, string)

appendTempFile(string, string)

Return

void

Examples

appendTempFile(myURL, myContent) To add content to a temporary file on the Ninox server.

let linebreak := "
";
let header := "CustomerID,Revenue" + linebreak;
URL := do as server
		createTempFile(header, "export.csv")
	end;
do as server
	for customer in select Customers do
		let line := customer.Id + "," + customer.Revenue + linebreak;
		appendTempFile(URL, line)
	end
end

Result: First, a temporary CSV file is created on the server and the link to the file is saved in the URL field. Then data from the Customers table will be added line by line to the file.

appointment

To convert given time-related values to an appointment

This function helps you to create a from-to-appointment based on 2 timestamps. Instead of the 2nd timestamp, you can also specify a duration and Ninox will return the end timestamp automatically.

If the Start and End timestamp are on the same day, the 2nd won’t be on display. Otherwise, both dates will be displayed, and you should give it enough space to show.

Syntax

appointment(any, any)

Return

appointment

Examples

Your result format may be different depending on your settings.

appointment(start, end) / appointment(start, duration) To convert 2 given time-related values to an appointment.

It doesn't matter if you add the end or the start first, Ninox will automatically select the earliest timestamp as a start.

appointment(datetime(2021, 3, 16, 19), datetime(2021, 3, 6, 17))

Result:

03/06/2021 17:00 – 03/16/2021 19:00 (US)
06/03/2021 17:00 – 16/03/2021 19:00 (UK)

appointment(datetime(2021, 5, 22, 10), time(1, 45))

Result:

05/22/2021 10:00 - 11:45 (US)
22/05/2021 10:00 - 11:45 (UK)

array

To create a new array by merging 2 arrays of a similar type

With this function, you can merge 2 arrays of the same data type and create a new array consisting of the values of both arrays.

It might be helpful to merge arrays for evaluation so that you only need to query only 1 array.

If you need to merge more than 2 arrays, just execute the function as often as needed.

Syntax

array([any], [any])

Return

[any]

Examples

array(myArray1, myArray2)

let myArray1 := ["1", "2", "3"];
let myArray2 := ["4", "5", "6"];
let myArray3 := array(myArray1, myArray2);
myArray3

Result: 1,2,3,4,5,6

asin

To calculate the arcsine

The function returns the arcsine of a number.

Syntax

asin(number)

Return

number

Examples

asin(x) To calculate the arcsine of a given number x between -1 and 1.

asin(-0.25)

Result: -0.25268025514207865

asin(1)

Result: 1.5707963267948966

asin(2)

Result: (invalid)

atan

To calculate the arctangent

The function returns the arctangent of a number.

Syntax

atan(number)

Return

number

Examples

atan(x) To calculate the arctangent of a given number x between -1 and 1.

atan(15)

Result: 1.5042281630190728

atan2

To return the arctangent of the quotient

The function returns the arctangent of the quotient of the given parameters.

Syntax

atan2(number, number)

Return

number

Examples

atan2(x,y) To calculate the arctangent of x divided by y.

atan2(7,4)

Result: 1.0516502125483738

avg

To return the average value of a number array

Calculate the mathematical average of numeric values from an array or a table with this function.

For example, you can calculate the average revenue per order or the average payment duration of invoices in days, etc.

You can use the function also on table views in columns with numeric values.

Syntax

avg([number])

Return

number

Examples

avg([1,2,3,4,5,6,7,8,9])

Result: 5

avg((select Invoices).Total)

Result: Average of all invoice totals in the Invoice table.

barcodeScan

To open the barcode scanner and return the scanned value as a string

With this function, you can scan a barcode or QR code via the camera on your mobile device.

To be used only on mobile devices.

Use this function in a button and save the returned value in a text field.

Syntax

barcodeScan()

Return

string

Examples

Add this script in a button.

'Item number' := barcodeScan()

Result: The camera scans a barcode and adds the value to the text field Item number.

ceil

To round up a given number to the nearest integer

With this function, you can round up a decimal number to the next higher integer. Existing decimal places are removed, and the remaining integer is increased by 1.

If an integer is already passed as a parameter, it remains unchanged.

Syntax

ceil(number)

Return

number

Examples

ceil(2.5)

Result: 3

ceil(12.00001)

Result: 13

ceil(27)

Result: 27

chosen

To evaluate multiple choice fields

The function determines all selected options of a multiple choice field and returns them in an array. You also can query if a specific value is included in the array.

For dynamic multiple choice fields (until now) only the query via the record IDs is possible.

Syntax

chosen(multi)

chosen(multi, string)

chosen(multi, number)

chosen(dmulit, number)

chosen(multi, [number])

chosen(dmulti, [number])

Return

boolean

[string]

Examples

We have a multiple-choice Favorite sports field with the following choice values:

Favorite sport

Selection

Basketball, Dancing, Sailing, and Soccer are selected.

chosen(myMultiChoice, choiceIdList) To return Yes (true) the given numbers representing the choice value IDs are fully covered in the selection.

chosen('Favorite sports', [1, 3, 5])

Result: Yes (true) (Sailing is selected, too, but this is not relevant)

chosen(myMultiChoice, choiceId) To return Yes (true) if a given number equals the ID of one of the chosen values.

chosen('Favorite sports', 4)

Result: Yes (true) (Basketball, Dancing, and Soccer are selected, too, but this is not relevant)

chosen(myMultiChoice, searchString) To return Yes (true) if a given string equals at least one of the selected choice values.

chosen('Favorite Sports', "Dancing")

Result: Yes (true) (Dancing is the relevant selection, the others are not relevant)

chosen('Favorite Sports', "Climbing")

Result: No (false)

chosen(myMultiChoice) To get all chosen values from a multiple-choice field.

chosen('Favorite Sports')

Result: Basketball,Dancing,Sailing,Soccer

chosen('Favorite sports') = "Dancing"

Result: Yes (true) if only Dancing is selected and No (false) if Dancing is not selected, or also other sports are selected. So No (false) would be the answer in our example. This helps you to filter these records, where only the given value is selected.

clientLang

To return the currently used language of the browser or app in abbreviated form

This function will help you find out the language a user has set in the respective app. The language is displayed with the corresponding abbreviation, as de for German or en for English.

At Ninox you can set the language individually for each browser or app.

More about the

Syntax

clientLang()

Return

string

Examples

Result: es (if the client has Spanish set as the language)

closeAllRecords

To close all record forms

This function will close all currently open forms. You will return to the current view of your table.

Use this function for example in a button to speed up the process of closing several form layers stacked on top of each other.

If you'd like to close only the form on top, we recommend closeRecord().

Syntax

closeAllRecords()

Return

void

Examples

Result: All open forms are closed and you will return to the current view of your table.

closeRecord

To close the top record form

This function will close the current form. You will then see either the form below or the current view of your table if there was no other form view open.

The function can be executed only on the client. Learn more about

You could use this function to add an alternative way to close the form. If necessary, you can add further actions.

Syntax

closeRecord()

Return

void

Examples

Result: The current record form will be closed.

color

To return or convert to a color value

With this function, you can assign a color value to a color or formula field. You also can get the color value of a selected option in a choice field.

To return a specific color, you can either pass a string referencing the color or 3 (up to 4) numbers referring to the RGB/RGBA colors.

color() accepts any valid HTML/CSS color identifier.

Syntax

color(string)

color(number, number, number)

color(number, number, number, number)

color(choice)

Return

color

Examples

Result: Blue, but not the Ninox blue.

Result: The Ninox blue

Result: The Ninox blue in RGB code

Result: The Ninox blue in RGB code with the opacity set to 50%

Result: Returns the color of the selected option of the Status choice field.

contains

To check if a string or an array contains an exact match

This function helps you to check if a given string or array of any length contains the exact match you are looking for.

In case you are not only searching for a specific string or array but also need its position, you can use the index() function.

The result is true (Yes) for an exact match or false (No) for no exact match found.

Strings

This is case-sensitive, so you can use upper() or lower() to format your text first. contains() can be used to search for keywords in text fields or to determine if a specific option was selected in a multiple-choice field.

Arrays

An exact match means the searched value is equal to one of the array items.

Syntax

contains(string, string)

contains([any], any)

Return

boolean

Examples

contains(myText, match) to check if a given string contains an exact match.

Strings

contains("Hello world!", "Hello")

Result: Yes (true)

contains("Hello world!", "hello")

Result: No (false)

contains(lower("Hello world!"), "hello")

Result: Yes (true)

Arrays

contains([myArray], match) to check if a given array contains an exact match.

contains(["A", "B", "C"], "B")

Result: Yes (true)

contains([{ name: "Sam" }], { name: "Sam" })

Result: No (false)

JSON objects are not matched by their content but by their identity.

cos

To calculate the cosine

The function returns the cosine of an angle in radians. The result is a numeric value between -1 and 1.

Syntax

cos(number)

Return

number

Examples

cos(x) To calculate the cosine of a given number x between -1 and 1.

Result: 0.9689124217106447

count (aka cnt)

To return the number of concrete items in an array

With this function, you can count the number of entries in arrays or tables that are not null or empty strings ("").

If you'd like to count all items in your array, we recommend using length().

In combination with select ... where you can count records that meet a specific condition. For example, how many entries have the status open, or how many last names start with a B.

The function can also be very helpful to find out if there are any duplicates in your table.

Syntax

count([any])

cnt([any])

Return

number

Examples

Result: 2

Result: 98

(if the table Invoices contains 98 records, where the Total is greater than USD1,000)

Return: If all IDs are unique, the result is Yes (true). If there are duplicates, the result is No (false).

createCalendarEvent

To create an event in the Apple Calendar App

Available on our apps for Mac, iPad, and iPhone. Not on web browsers or Android apps.

With this function, you can create a direct entry in Apple's calendar app. If no specific calendar is specified, Ninox creates the entry in the default calendar.

Syntax

createCalendarEvent(string, string, timestamp, timestamp)

createCalendarEvent(string, appointment)

createCalendarEvent(string, timestamp, timestamp)

createCalendarEvent(string, string, appointment)

Return

void

Examples

createCalendarEvent(calendar, title, from, to) To create an event in a given Apple Calendar with a given title and a given start and end.

Result: Creates an entry named "Ninox-Webinar" with the start time from "Start” and the end time calculated from "Duration” in the Apple calendar "Private".

createCalendarEvent(title, appointment) To create an event in the Apple Calendar App with a given title.

createCalendarEvent(title, from, to) To create an event in the Apple Calendar App with a given title and a given start and end.

createCalendarReminder

To create a reminder in the Apple Calendar App

Available on our apps for Mac, iPad, and iPhone. Not on web browsers or Android apps.

Create a reminder in the Apple Reminder App with this function.

Syntax

createCalendarReminder(string)

createCalendarReminder(string, timestamp)

createCalendarReminder(string, timestamp, timestamp)

createCalendarReminder(string, string, timestamp, timestamp)

Return

void

Examples

createCalendarReminder(title) To create a reminder in the Apple Reminder App with a given title.

createCalendarReminder(title, start) To create a reminder in the Apple Reminder App with a given title and given start.

createCalendarReminder(title, from, to) To create a reminder in the Apple Reminder App with a given title, a given start, and end.

createTempFile

To create a temporary file on the Ninox server

This function creates a temporary file on the Ninox server with the given content and file name and returns a link to that file.

Only members of the respective workspace (team) can access the file via the link.

Use this function in combination with appendTempFile() rendering large or long-running exports.

This function will only create a file if there is any content in it.

As this file is of temporary nature, it will be automatically deleted at some point. So don't take it for granted.

The function can be executed only on the client. Learn more about execution context.

Syntax

createTempFile(string, string)

Return

link

Examples

createTempFile(myContent, fileName) To create a temporary file on the Ninox server

URL := do as server createTempFile("CustomerID,Revenue
", "export.csv")
end

Result: The URL field contains a link to the export.csv file.

createTextFile

To create a text file with specified name, content, and encoding options

The createTextFile function allows you to create and attach text files directly to records within Ninox. This function supports the creation of various file types, including .txt, .html, and .csv, which can then be downloaded or used outside of Ninox.

You can specify the file's name, extension, and content, and optionally define an encoding format (using the server-side variant). If no specific location is designated, the file will attach directly to the record, accessible via the paperclip icon (📎) in the Attachments tab.

Caution: The encoding option can only be used within a do as server code block.

Syntax

createTextFile(nid, string, string)

createTextFile(nid, string, string, JSON)

Parameters

record: The record to which the text file will be attached.
content: The text content to be written to the file. This can be:
- Text directly entered within quotation marks. For example, "Hello, world!".
- Text pulled from a text field, multiline text field, or rich text field.
filename: The intended file name, including its extension. For example, file.txt or document.html.
options (optional, server-side only): A JSON object specifying encoding options.
- encoding: Defines the file's encoding format. Available encoding values:
  - 'utf8', 'utf-8'
  - 'utf16le', 'utf-16le' (aliases: 'usc2', 'usc-2')
  - 'latin1' (alias: 'binary')
  - 'base64'
  - 'base64url'
  - 'hex'
  - 'ascii'

Note: Encoding strings are not case-sensitive. For example, UTF-8 can be specified as utf8, UTF8, or uTf8.

Return

file: A generated file attached to the specified record.

Examples

In the following examples, your database contains these fields:

A rich text field named My text containing content to be saved.
An image field named File.
A button named Create document.

To follow these examples, open the settings of the Create document button, then add the corresponding script to the On click formula editor.

1. Basic text file attachment with plain text conversion

To create a text file named MyTextFileExample.txt using the plain text in My text, use:

createTextFile(this, text('My text'), "MyTextFileExample.txt")

Explanation: The text('My text') function removes any formatting and returns the plain text from My text.

Result: Upon clicking Create document, a .txt file named MyTextFileExample.txt is created and attached to the record. You can access the file in the Attachments tab (📎); clicking the image field will automatically download the file.

2. HTML file attachment with raw text (including HTML tags)

To create an HTML file (with HTML tags) named MyTextFileExample.html using the text from My text and attach it to the File image field, use:

File := createTextFile(this, raw('My text'), "MyTextFileExample.html")

Explanation: The raw('My text') function keeps any HTML tags within My text, allowing the output file to display as a formatted HTML document in a browser.

Result: Upon clicking Create document, an .html file named MyTextFileExample.html is created and attached to the File image field. Clicking the image field will automatically download the file.

Note: Using raw('My text') with an .html extension ensures that any HTML tags within My text (such as <h1>, <p>, etc.) are retained. This allows the file to open in a web browser with structured formatting based on these tags, which is suitable for content you wish to display as a styled web page.

3. HTML file with UTF-8 encoding

To apply UTF-8 encoding to an HTML file created from My text, use the server-side variant of the function within a do as server block:

do as server
  File := createTextFile(this, raw('My text'), "MyTextFileExample.html", {encoding: "utf8"})
end

Explanation: The server-side block (do as server ... end) is necessary to specify encoding.

Result: Upon clicking Create document, an .html file (encoded in UTF-8) named MyTextFileExample.html is created and attached to the File image field. Clicking the image field will automatically download the file.

Note: The encoding option is suitable for preserving international characters, special symbols, or multilingual content in the file.

createXLSX

To dynamically create customizable, styled, multi-sheet Excel files directly from databases

With this function, you can create an XLSX file with customizable content and multiple sheets, reflecting the data and style defined in the input parameters.

The resulting file is saved directly in Ninox, offering dynamic data management and formatting options.

Syntax

createXLSX(nid, any, string)

Return

file

Examples

Using `createXLSX`

To create an Excel file with the createXLSX function:

Create a Button field: When clicked, the button triggers a function to create an Excel file from the data provided in the formula editor.
Create an Image field: Serves a link, allowing you to download the file directly.

Enable edit mode and click the On click field. In the formula editor:
1. Define the columns and rows.
2. Define the worksheet structure.
3. Use the createXLSX function.
4. Define styles and formatting (optional).
5. Save the script. Create the Excel file by clicking the button.

Define columns and rows

First, create an object to define the columns:

let columns = [
	{
		header: "Name",
		key: "name",
		width: 10,
	}, 
	{
		header: "Age",
		key: "age",
		width: 10
	}, 
	{
		header: "URL",
		key: "url",
		width: 30
	}, 
	{
		header: "Description",
		key: "description",
		width: 20
	}
];

Next, define the rows. You can use #special-supported-fields, if you need to:

let rows = [
	{
		name: "Luis Gómez",
		age: 30,
		url: {
			text: "www.google.com",
			hyperlink: "http://www.google.com",
			tooltip: "www.google.com"
		}
	}, 
	{
		name: "Maria Silva",
		age: 25,
		url: {
			text: "www.google.com",
			hyperlink: "http://www.google.com",
			tooltip: "www.google.com"
		}
	}, 
	{
		name: "Ayesha Khan",
		age: 35,
		url: {
			text: "www.google.com",
			hyperlink: "http://www.google.com",
			tooltip: "www.google.com"
		}
	}, 
	{
		name: "Li Wei",
		age: 40,
		url: {
			text: "www.google.com",
			hyperlink: "http://www.google.com",
			tooltip: "www.google.com"
		}
	}, 
	{
		name: "Rajesh Kumar",
		age: 21,
		url: {
			text: "www.google.com",
			hyperlink: "http://www.google.com",
			tooltip: "www.google.com"
		}
	}, 
	{
		name: "Sofia Müller",
		age: 24,
		url: {
			text: "www.google.com",
			hyperlink: "http://www.google.com",
			tooltip: "www.google.com"
		}
	}
];

Define worksheet structure

Define a worksheet with columns and rows:

let worksheets = {
	Sheet1: {
		columns: columns,
		rows: rows
	}
};

Use `createXLSX`

Call the createXLSX function with the defined worksheets:

Image := createXLSX(this, worksheets, "example.xlsx")

Define styles and formatting (optional)

Apply style to a header cell:

let columns = [
	{
		header: "Name",
		key: "name",
		width: 10,
		headerStyle: {
			font: {
				bold: true
			}
		}
	}
];

Apply style to an entire column except the header:

let columns = [
	{
		header: "Name",
		key: "name",
		width: 10,
		style: {
			font: {
				name: "Comic Sans MS"
			}
		}
	}
];

Apply style to an entire row:

let rows = [
	{
		name: "Luis Gómez",
		age: 30,
		// hyperlink field
		url: {
			text: "www.google.com",
			hyperlink: "http://www.google.com",
			tooltip: "www.google.com"
		},
		styles: [
			{
				fill: {
					type: "pattern",
					pattern: "solid",
					fgColor: {
						argb: "F08080"
					}
				}
			}
		]
	}
];

Apply style to specific cells in a row:

let rows = [
	{
		name: "Luis Gómez",
		age: 30,
		// hyperlink field
		url: {
			text: "www.google.com",
			hyperlink: "http://www.google.com",
			tooltip: "www.google.com"
		},
		styles: [
			{
				targets: ["name", "age"],
				fill: {
					type: "pattern",
					pattern: "solid",
					fgColor: {
						argb: "F08080"
					}
				}
			}
		]
	}
];

Create Excel file

Finally, when you've saved your script, click the button to create an Excel file.

Supported styles and formatting options

Font

{
  font: {
    name: "Arial Black",
    color: { argb: "FF00FF00" },
    family: 2,
    size: 14,
    italic: true,
    underline: true,
    bold: true
  }
}

Font formatting options

Alignment

{ alignment: { vertical: "top", horizontal: "left" }

Alignment formatting options

Border

// set single thin border
{
  border: {
    top: { style: "thin" },
    left: { style: "thin" },
    bottom: { style: "thin" },
    right: { style: "thin" }
  }
}

// set double thin green border
{
  border: {
    top: { style: "double", color: { argb: "FF00FF00" } },
    left: { style: "double", color: { argb: "FF00FF00" } },
    bottom: { style: "double", color: { argb: "FF00FF00" } },
    right: { style: "double", color: { argb: "FF00FF00" } }
  }
}

// set thick red cross
{
  border: {
    diagonal: { up: true, down: true, style: "thick", color: { argb: "FFFF0000" } }
  }
}

Valid border styles

thin
dotted
dashDot
hair
dashDotDot
slantDashDot
mediumDashed
mediumDashDotDot
mediumDashDot
medium
double
thick

Patterned fill

// fill with red dark vertical stripes
{
  fill: {
    type: "pattern",
    pattern: "darkVertical",
    fgColor: { argb: "FFFF0000" }
  }
}

// fill with yellow dark trellis and blue behind
{
  fill: {
    type: "pattern",
    pattern: "darkTrellis",
    fgColor: { argb: "FFFFFF00" },
    bgColor: { argb: "FF0000FF" }
  }
}

// fill with solid coral
{
  fill: {
    type: "pattern",
    pattern: "solid",
    fgColor: { argb: "F08080" }
  }
}

// fill with blue-white-blue gradient from left to right
{
  fill: {
    type: "gradient",
    gradient: "angle",
    degree: 0,
    stops: [
      { position: 0, color: { argb: "FF0000FF" } },
      { position: 0.5, color: { argb: "FFFFFFFF" } },
      { position: 1, color: { argb: "FF0000FF" } }
    ]
  }
}

// fill with red-green gradient from center
{
  fill: {
    type: "gradient",
    gradient: "path",
    center: { left: 0.5, top: 0.5 },
    stops: [
      { position: 0, color: { argb: "FFFF0000" } },
      { position: 1, color: { argb: "FF00FF00" } }
    ]
  }
}

Pattern fill options

To fill a cell using the solid pattern, you don't need to specify bgColor.

Valid pattern types

none
solid
darkGray
mediumGray
lightGray
gray125
gray0625
darkHorizontal
darkVertical
darkDown
darkUp
darkGrid
darkTrellis
lightHorizontal
lightVertical
lightDown
lightUp
lightGrid
lightTrellis

Gradient fill

Supported special fields

The function supports special fields like hyperlinks, rich text, and formulas:

Hyperlinks provide links to web content or internal references.
Rich text allows for mixed-format text, including bold, italic, and other font styles.
Formulas enable cells to compute values dynamically.
Dates can be used directly from Ninox.

Hyperlink

// link to web
value = {
  text: "www.mylink.com",
  hyperlink: "http://www.mylink.com",
  tooltip: "www.mylink.com"
};
// internal link
value = {
  text: "Sheet2",
  hyperlink: "#'Sheet2'!A1"
};

Rich text

value = {
  richText: [
    { text: "This is" },
    { font: {italic: true}, text: "italic" },
  ]
};

Formula

value = { formula: "A1+A2" };
value = { formula: "SUM(A1,A2)" };

Date

let columns = [{
    header: "Birthdate",
    key: "birthdate",
    width: 10,
    date: true
}];

createZipFile

To create a zip archive including all files of a given file array

With this function, you create a zip archive containing all files passed in an array.

At the moment the function works only if it is executed server-side. More about execution context.

Syntax

createZipFile(nid, [file], string)

Return

file

Example

createZipFile(record, files, filename) creates a zip archive with all files of the array files and a given name.

do as server
let products := (select Products).photo
File := createZipFile(this, products, "Products.zip")
end

Result: The Products.zip zip archive containing all product photos from the Products table is saved in the File image field.

databaseId

To return the ID of the current database

This function tells you the ID of the current database you are in.

Syntax

databaseId()

Return

string

Examples

databaseId()

Result: mcxhedkkmvpp (for example)

date

To convert to or return a date value

This function extracts the date from a timestamp. It also returns a date based on the numeric values for year, month, and day.

‌If you specify "0" as the value for the day, the last day of the previous month will return. This is a very handy way, to determine the last day of a month.

‌Furthermore, a Unix time specification in milliseconds can be converted into a date.

Syntax

date(any)

date(number, number, number)

Return

‌date

‌Examples

Your result format may be different depending on your settings.

‌date(year, month, day) To return a date value.

date(2021, 8, 0)

‌Result: 07/31/2021 (US) (last day of the previous month)

‌date(myTimeValue) To convert a given time-related value to a date value. If the value is a number, it represents the Unix time in milliseconds.

date('Date + Time')

‌Result: Date from the field Date + Time

date(start(Appointment))

‌Result: Date of the start of the Appointment field

date(1617873118285)

‌Result: 04/08/2021 (US)

‌See also

‌time, which returns the current time.

datetime

To convert to or return a local timestamp

You can use this function to create a timestamp from a date and time or numeric values. If the parameter for the time is missing, it will automatically use 00:00.

Syntax

datetime(number)

datetime(number, number, number)

datetime(number, number, number, number)

datetime(number, number, number, number, number)

datetime(number, number, number, number, number, number)

Return

timestamp

Examples

Your result format may be different depending on your settings.

‌datetime(year, month, day) To return a timestamp out of the given year, month, and day numbers.

datetime(2021, 5, 27)

‌Result: 05/27/2021 00:00 (the time is set automatically) (US)

‌datetime(year, month, day, hour) To return a timestamp out of the given year, month, day, and hour numbers.

‌datetime(year, month, day, hour, minute) To return a timestamp out of the given year, month, day, hour, and minute numbers.

datetime(2021, 05, 27, 18, 5)

‌Result: 05/27/2021 18:05 (US) 27.05.2021 18:05

‌datetime(year, month, day, hour, minute, second) To return a timestamp out of the given year, month, day, hour, minute, and second numbers.

‌datetime(year, month, day, hour, minute, second, millis) To return a timestamp out of the given year, month, day, hour, minute, second, and millisecond numbers.

datetime(myDate, myTime) To convert a given date and a time to a timestamp.

datetime(Date, Time)

‌Result: The date of the field Date and the time of the field Time, for example, 05/27/2021 18:05.

‌datetime(millis) To convert a given number to a timestamp where the number represents the Unix time in milliseconds.

day

To return the day of the month from a date value as a number

‌This function extracts the day of a month from a date, appointment, or timestamp; therefore, the result will be a numeric value between 1 and 31.

‌You can use all data types containing a date for the calculation.

Syntax

day(appointment)

day(date)

day(timestamp)

‌Return

‌number

Examples

‌day(myAppointment) To return the day of a given start of an appointment.

‌day(myDate) To return the day of the month of a given date.

day('Date')

‌Result: 1

(with

Date = 07/01/2021) (US)
Date = 01/07/2021 (UK))

‌day(myTimestamp) To return the day of the month of a given timestamp.

day(1624226400000)

‌Result: 20 (of June 20, 2021)

‌See also

‌month, which returns the month from a given date value as a number.

quarter, which returns the quarter of a given date value as a number.

week, which returns the calendar week of a given date value.

year, which returns the year of a given date value.

days

To return the number of days between 2 dates

With this function, you calculate the number of days between 2 date values. All days of the week count, i.e. Monday until Sunday. All data types that contain a date can be used.

‌The function is handy to determine and check deadlines, periods, and due dates.

Syntax

days(date, date)

‌Return

‌number

Examples

‌days(start, end) To return the number of days between 2 given dates.

days(date1, date2)

‌date1 = 07/02/2021 and date2 = 12/31/2021 (US)

‌Result: 183 (days until New Year’s Eve)

‌See also

‌workdays, which returns the number of workdays (Monday – Friday) between 2 dates.

degrees

To convert an angle from radians to degrees

The function returns the corresponding degree value of a radian. It is mostly used for trigonometric calculations.

Syntax

degrees(number)

Return

number

Examples

degrees(x) To convert angle x from radians to degrees.

degrees(3.141592653589793)

Result: 180

dialog

To pop up a dialog with answer options

This function will trigger a pop-up with notes created by you. You can add a selection of options with answers to choose from, for example, Yes or No.

The running script is put on hold until the user picked an answer.

The result can be stored in a field or variable and therefore be processed in the further execution of the script.

The function can be executed only on the client. Learn more about execution context

Syntax

dialog(string, string, [string])

Return

string

Examples

dialog(title, message, answerOptions) To pop up a dialog with answer options.

Add this script to a button.

let title := "Delete record";
let message := "Should this record be deleted? Are you 100% sure? 
It will be gone forever. You cannot get it back. It will be gone and away."
let answerOptions := ["Yes, get rid of it.", "No! I still  need it!"]
if dialog(title, message, answerOptions) = "Yes, get rid of it." then
    delete this
end

Result: You should get this popup. By clicking Yes this record will be deleted.

duplicate

To create a duplicate of a given record

With this function, you create an exact duplicate of the record to which you apply this function. Also, linked data from sub-tables will be transferred.

Syntax

duplicate(nid)

Return

nid

Examples

duplicate(this)

Result: The current record—with all entries—is duplicated.

let newRecord := duplicate(this);
openRecord(newRecord)

Result: Creates a duplicate of the current record and opens it.

duration

To return the duration of an appointment

This function calculates the duration of an appointment for you, i.e. the time between the start and the end. The result is shown in hours and minutes.

If the duration is longer than 24 h, the days are shown additionally. There is no conversion from days to weeks, months, or years.

Summer and winter time are considered.

Syntax

duration(appointment)

Return

timeinterval

Examples

Your result format may be different depending on your settings.

duration(holiday1)

Result: 4 Days 9:00 (with holiday1: 05/24/2021 09:00 - 05/28/2021 18:00 (US))

duration(appointment(date(2021, 3, 27), date(2021, 3, 29)))

Result: 2 Days

email

To convert a given value to an email value

This function converts a regular formula field to an email field with a mailto button.

Click the button to open your local email clients and send an email.

There is no sanity check of the transferred content. Every given string will be displayed as an email field.

Syntax

email(any)

Return

Examples

email(myEmail)

email("steve.rogers@theavengers.com")

Result: steve.rogers@theavengers.com

(as an email field with a little envelope you can click that directly links to your default email client)

endof

To return the end timestamp of a given appointment

The function returns the end timestamp of a given appointment. The result is a value with date and time, which can be further processed or formatted.

Syntax

endof(appointement)

Return

timestamp

Examples

Your result format may be different depending on your settings.

endof('Appointment')

Result:

07/03/2021 06:00 PM (US)
03/06/2021 18:00 (UK)

(with Appointment =

06/02/2021 09:00 AM - 07/03/2021 06:00 PM (US)
02/06/2021 09:00 - 03/07/2021 18:00 (UK))

format(endof('holiday1'), "dddd, HH:mm") + " h"

Result: Friday, 18:00 h

(with holiday1 =

05/24/2021 09:00 AM - 05/28/2021 06:00 PM (US)
24/05/2021 09:00 - 28/05/2021 18:00 (UK))

even

To return "true" if the number is even

With this function, you can check whether a number is even, i.e. divisible by 2 without remainder. The return value is either Yes (true) or No (false). The 0 is also evaluated as even.

Syntax

even(number)

Return

boolean

Examples

even(10 + 12)

Result: Yes (true)

even(10 + 13)

Result: No (false)

even(0)

Result: Yes (true)

even(10.2)

Result: No (false)

extractx

To extract a string from a given text using a regular expression

With this function, you can extract the first match of a search string in a text.

Instead of simple strings as a search pattern, you can use regular expressions (regex). That means you have much more possibilities for text analysis.

You could use this function for example to extract relevant information from imported data.

Using regular expressions might need some extra research. We recommend checking regular expressions on https://regex101.com/ (external link).

Syntax

extractx(string, string)

extractx(string, string, string)

extractx(string, string, string, string)

Returns

string

Examples

extractx(myText, regex, flags, extract) To extract a string from a given text using a regular expression. Flags can be added to the regular expression. The string can be further processed with a given extract.

We have a database with some records in it. We also have …

an image field My attachment with a file ImportantDocument.pdf
a formula field File name

Open the field settings of your formula field File name and add the following script to the formula field Formula:

extractx(text('My attachment'), "(.+)\/(.+)", "i", "$2")

Result: In the formula field you will see the name of the attachment: ImportantDocuement.pdf

extractx(myText, regex, extract) To extract a string from a given text using a regular expression. The string can be further processed with the given extract.

extractx(myText,regex) To extract a string from a given text using a regular expression.

fieldId

To return the ID of a field

This function retrieves the internal identifier of a specified field. A field represents a location stored within a row of a table in a database. Each field is defined by an ID, a name, and a field type. Field IDs are capital letters starting with "A," "B," ..., "AA," "AB," etc.

Syntax

fieldId(nid, string)

fieldId(string, string)

Return

string

Benefit of `fieldId`

No direct UI references in scripts: This function allows you to work with field IDs rather than field names, so your scripts aren't tied to specific UI elements. This is helpful if field names change over time.

Use cases for `fieldId`

Calculations without the field name: You can use fieldId to do calculations or work with data without needing to know the exact field name, making your scripts more flexible.
Populate a row with JSON objects: You can use fieldId to easily fill a row with data from JSON objects, making data entry and management simpler.

Examples

Use fieldId(nid, fieldName) to retrieve the field ID using the current table's record ID (nid) and the field name:

fieldId(this, "Name")

Result: D

This is the field ID for the field named "Name" in the current table.

Use fieldId(tableName, fieldName) to retrieve the field ID using the table name and field name:

fieldId("Customer", "Name")

Result: A

This is the field ID for the field named "Name" in the "Customer" table.

Use fieldId(fieldName) to retrieve the field ID using the field name:

fieldId(Number)

Return: D

The field ID for the field named "Number" in the current table.

file

To return a specific file based on the file name from a given record

With this function, you can address a specific file attachment of a record, e.g. to display it in an image field or to send it as an attachment of an automatically generated e-mail.

Syntax

file(nid, string)

Return

file

Examples

file(record, fileName) To return a specific file based on the file name from a given record.

file(this,"INV-2021_13874.pdf")

Result: Returns the file “RE-2021_13874.pdf” from the current record.

fileMetadata

To return the metadata, like file name, size and modification date of a file based on a given record and file name

With this function, you can find out the metadata of a file. You will get back:

file name
file size
modification date

Syntax

fileMetadata(nid, string)

Return

JSON

Examples

fileMetadata(record, fileName) To return the metadata, like file name, size and modification date of a file based on a given record and file name.

filedata(this, "Invoice_001.pdf")

Result:

{"name":"Invoice_001.pdf","size":95935,"modifiedDate":1661385600000}

fileMetadata(this, last(split(text(Invoice), "/")))

Result:

{"name":"Invoice_001.pdf","size":95935,"modifiedDate":1661385600000}

with the file name pulled from the Invoice image field.

files

To return all attachments of a record as an array

In contrast to file() with this function, you can address all file attachments of a record, e.g. to send them as an attachment of an automatically generated e-mail or search for specific files in a table.

Syntax

files(nid)

Return

[file]

Examples

files(this)

Return: An array with all attachments of the current record.

count(files(this))

Return: 10 (If the current record has 10 attachments).

You (Steve Rogers) would like to send an email with the attachments of the current record to Hulk. This would be the script in the button within the record, that Steve would click to trigger the action.

sendEmail({
	from: "steve@avengers.com",
	to: "hulk@avengers.com",
	subject: "Importent documents",
	text: "Here come the important documents!",
	attachments: files(this)
})

Result: Hulk will receive an email with all the attachments of the current record.

first

To return the first item of an array

You can access the first entry of an array with this function. This function comes in handy if you try to access a record in an array of records.

The record IDs of the tables are arranged differently in the multiple platforms. Therefore, different results are displayed according to each platform. This applies if the array consists of records.

If you combine the function with select and the respective array has more than 1 element, we recommend sorting the array first with order by .

Syntax

first([any])

Return

any

Examples

Result: B

Result: Returns the first record of the Customer table.

Result: Returns the first record of the table Customer where the last name starts with an A.

floor

To round down a given number to the nearest integer

With this function, you can round down a decimal number to the next lower integer. Existing decimal places are removed.

If an integer is already passed as a parameter, it remains unchanged.

Syntax

floor(number)

Return

number

Examples

Result: 2

Result: 12

Result: 27

format

To format a given value

With the function, you can format numbers, date, and time values as you need them, to use them in text or formula fields.

Note: Format numbers

All entries are optional. If you do not specify anything, nothing will be formatted.

If you want to format numbers with this function, you must specify the defaults for formatting in a certain order:

First, specify whether a prefix should be inserted, e.g. a currency symbol.
Should a 1000 separator be inserted?
Specify whether or how many decimal places you want to display.
Specify a unit, e.g. a currency sign.
Type of decimal place separator, e.g. period (.), comma (,), single quote (') or double quotes (").
Type of 1000 separator, e.g. period (.), comma (,), single quotation mark ('), double quotation marks (") or a space ( ).

Use double quotes ("") for quotes within a string.

Syntax

format(number, string)

format(date, string)

format(date, string, string)

format(appointment, string)

format(appointment, string, string)

format(timestamp, string)

format(timestamp, string, string)

format(time, string)

Return

string

Examples

Your result format may be different depending on your settings.

format(myNumber, formatExpression) To format a given number as a string.

Result: 2.385,97 €

Ergebnis: $2,385.97US

Result: 0000012345

format(myDate, formatExpression) To format a given date as a string.

Result:

Mon, May 31, 2021 (with “Date” = 05/31/2021) ((US)
Mon, 31st May 2021 (with “Date” = 31/05/2021) (UK)

format(myDate, formatExpression, language) To format a given date as a string, where the result shows in a specific language supported by Ninox.

format(myDatetime, formatExpression) To format a given timestamp as a string.

Result: 1622466387 (on May 31, 2021 15:06)

"X" shows the result without Milliseconds! "x" shows the result with Milliseconds.

format(myAppointment, formatExpression) To format a given start of an appointment as a string.

format(myAppointment, formatExpression, language) To format a given start of an appointment as a string, where the result shows in a specific language supported by Ninox.

format(myTime, formatExpression) To format a given time as a string.

format(myDatetime, formatExpression, language) To format a given timestamp as a string, where the result shows in a specific language supported by Ninox.

formatJSON

To create a JSON string of a valid JSON object

This function converts a JSON object to a string. This can be useful if you want to check the content of an incoming JSON object or if you want to modify it with other text functions.

Syntax

formatJSON(JSON)

Return

string

Examples

With this script inserted in a button, you can store the response of an API GET request in the field Text to check the response object. Without formatJSON() the result would not be very helpful.

More about at Ninox.

Please remember to exchange the following API key with your personal API key.

Result: You'll get a JSON string with data of your current table:

{"result":{"id":"I","name":"Table1","fields":[{"id":"A","name":"Text","type":"string"},{"id":"B","name":"Number","type":"number"},{"id":"D","name":"Text 2","type":"string"}]}}

formatXML

To convert a given JSON object into XML text, which might be optically structured

Use this function to convert data into XML format. XML is beside JSON one of the most popular formats for exchanging data. That's why this function is of particular interest when connecting to external servers via API.

More about at Ninox.

Syntax

formatXML(JSON)

formatXML(JSON, boolean)

Return

string

Examples

formatXML(JSON) To convert a given JSON object into XML text.

formatXML(json,pretty) To convert a given JSON object into XML text. If pretty is true the text is optically structured.

Result: You'll get an XML string with the data from the JSON object.

get

To return a value of a given field

This function retrieves the value of a field. You can use get to access fields by their name or ID, making it easy to extract data for use in scripts and calculations.

Syntax

get(nid, string)

Return

text

number

boolean

void

Benefits of `get`

Easy to use: It makes it easy to get data from fields in a consistent way, no matter what type of data it is.
Integration: You can easily use get with other functions like to update and manage data dynamically in your scripts.
More flexibility: The get function can fetch data using either the field's name or ID, making it easy to work with different field naming styles and database setups.

Using get can make it easier to access and work with data in Ninox, allowing for efficient data management and easy integration with other functions.

Examples

Here are some simple examples to show how the function works with different inputs.

Return: Vania

Return: true

Return: 42

Return: void

Traditional method

In a typical scenario, in checklist forms field names are often numbered. For example:

Object name 1, Value 1, Date 1
Object name 2, Value 2, Date 2
Object name 3, Value 3, Date 3

To collect all this data and place it into a subtable, you might traditionally use:

Dynamic data collection with `get` and `set`

html

To return a rich text representation of any value

With this function, you can display text that contains HTML tags. You can use all basic HTML tags for display, but also tables, lists, and inline styles.

The result can be displayed in a formula field, for example, or be used together with sendEmail().

In a normal text field, any HTML tags will not be interpreted but simply displayed as text.

In a Rich text field, the text will be formatted according to the HTML tags.

Syntax

html(any)

Return

html

Examples

Result: Ninox is great!

Result: Ninox is great! in a red box with the size (100px x 100px) in a Rich text field.

http

To send an HTTP request

With this function, you can make an API call to access data outside your current database. You can integrate some external services or exchange data between 2 databases.

This function can take up to 4 parameters:

Method A string defining the HTTP method for the API call. This is usually "GET", "POST", "PUT", or"DELETE"
URL A string holding the URL to the external service
Header An optional JSON object containing meta information of the API call like an API key or the content type.
Body An optional JSON object usually containing the data that will be shared with other external services.

Using API might need some extra knowledge.

To access a Ninox database you need an API key.

We recommend executing this function together with do as server. More about

Syntax

http(string, string)

http(string, string, JSON)

http(string, string, JSON, JSON)

With files

http(string, string, JSON, [file])

http(string, string, JSON, JSON, [file])

Return

JSON

Examples

http(method, url) To call REST services without a header or body.

http(method, url, header) To call REST services—the method and URL are strings; the (optional) header is a JSON object.

http(method, url, header, body) To call REST services—the method and URL are strings (header and body are optional objects).

Result: You will receive a JSON object either containing all tables of the database if the API call is successful, or a JSON object containing an error message if not.

Result: You will receive a JSON object either containing all records of the Customers table, where the Status = 4, if the API call is successful or a JSON object containing an error message if not.

With files

http(method, url, header, files) To call REST services and include a list of files - the method and URL are strings; the (optional) header is a JSON object.

http(method, url, header, body files) To call REST services and include a list of files - the method and URL are strings (header and body are optional objects).

icon

To return an icon

With this function, you can assign an icon value to an icon or formula field. You also can get the icon value of a selected option in a choice field.

Currently, there are 254 icons available at Ninox. Each icon has an internal name to refer to.

Syntax

icon(choice)

icon(string)

Return

icon

Examples

Result: The icon of the selection of the choice field.

Result:

importFile

To import a file from a URL and save it as an attachment of a record

With this function, you can import a file and attach it to a record or insert it in an image field.

The file can be pulled/retrieved from an external URL. But with printAndSaveRecord() you can also attach an extra created file.

Syntax

importFile(nid, string)

importFile(nid, link)

importFile(nid, string, string)

importFile(nid, link, string)

Return

file

Examples

importFile(record, link, fileName) To import a file from a given URL string and save it as an attachment of a record.

Result: The file provided via the given link is attached to the current record with the name Image.jpg.

Result: Prints the current record in the Invoice layout to a PDF and saves the file in the Invoice image field.

index

To return the start position of the first match in a string or an array

With this function, you can determine the position of a searched value within a given string or an array.

Unlike contains(), this function not only checks whether the searched value exists but also determines the numeric value of its start position within a string or an array. Counting starts at 0.

If there is no match for the searched value, the result is -1.

Strings

If the searched value is part of a string, the position of the match's first characters is returned. The result is then 0.

If you don't want to distinguish between upper and lower case letters, we recommend that you unify either the specified text or the searched string with the functions or .

Arrays

If there is no array item that is identical to the searched value, the result is -1.

Syntax

index(string, string)

index([any], any)

Return

number

Examples

Strings

index(string, match) determines the first position of a given match in a string, starting with 0.

Result: 3

Result: 0

Result: -1 (no exact match found)

Arrays

index([myArray], match) determines the first position of a given match in an array, starting with 0.

Result: 2

Result: -1 (no exact match found)

isAdminMode

To return Yes (true) if the edit mode is active

With this function, you'll find out if the current user is in edit mode, i.e. the wrench icon is activated (red).

Syntax

isAdminMode()

Return

boolean

Examples

Result: Yes (true) if you activated the wrench icon to edit your database

join

To return a string consisting of all items of the given string array separated by a given separator.

Merge multiple elements from an array or table into one string.

Unlike the function concat(), however, you can specify here which character should separate the elements. This can be a comma, for example, but also a line break. If you select a line break, you can output the entries in a multiline text field.

The function has nothing in common with the SQL statement of the same name.

Syntax

join([string], string)

Return

string

Examples

join(stringArray, separator) To return a string consisting of all items of the given string array separated by a given separator. The separator can be a line break.

Result: A B C

Result: The names of all customers whose sales are higher than €10,000, one after the other, separated by commas.

latitude

To return the latitude value of a given location value

This function will extract the latitude of an address in a location field as a decimal number.

Syntax

latitude(location)

Return

number

Examples

Result: 52,5235175 (for Monbijouplatz 5, 10178 Berlin—Ninox office address)