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.

Syntax

age(date)

Return

number

Examples

 age('Date of Birth') 

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

age(date(1976, 4, 12)) 

Result: 45 (on May 27, 2021)

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.

Result: 1.8234765819369754

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.

Result: -0.25268025514207865

Result: 1.5707963267948966

Result: (invalid)

See also

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

See also

atan, which calculates the arctangent.

tan, which calculates the tangent of a number.

The function removes existing signs and returns the absolute, positive value of a number.

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

See also

, which returns the signum of a number.

This function will trigger a pop-up with an alert message and an OK button.

You can use this function to display information to the user, which they need to confirm if they want to continue.

If you need to provide options for the user to choose from, we recommend dialog().

If you call this function more than once within the same script, only the last function call will be executed, so the user has only one box to confirm.

Syntax

alert(any)

Return

void

Examples

Result: Add the code block to a button, and you'll get a popup with an OK button when clicking on it.

Result: This is the time at this very moment: 17:18. (will show the exact time when the popup is triggered.

See also

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.

Syntax

array([any], [any])

Return

[any]

Examples

array(myArray1, myArray2)

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

See also

This function converts the first character of each word in a text into a capital letter. This can be handy to correct entered data that should always begin with a capital letter, for example, the first and last name of a person.

In Trigger after update, you could add the function to automatically correct the entered data.

Syntax

capitalize(string)

Return

string

Examples

capitalize('Last name')

Result: Chamani (with the field Last name = chamani)

capitalize("Sam chamani")

Result: Sam Chamani

'Last name' := capitalize('Last name')

Result: Dacosta (with the field Last name = dacosta) for example added in the Trigger after update in the field settings of the text field Last name.

See also

lower, which returns a string in lower case.

upper, which returns a string in the upper case.

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

See also

atan2, which calculates the arctangent of the quotient.

tan, which calculates the tangent.

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.

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.

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.

Syntax

appointment(any, any)

Return

appointment

Examples

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

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)

See also

datetime, which converts to or returns a timestamp.

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

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.

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

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

Syntax

appendTempFile(link, string)

appendTempFile(string, string)

Return

void

Examples

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

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.

See also

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.

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:

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.

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.

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.

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

Result: No (false)

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

Result: Basketball,Dancing,Sailing,Soccer

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.

See also

This function closes the current fullscreen form view.

Syntax

closeFullscreen()

Return

void

Examples

Add this function in a formula field of a button.

Result: By clicking the button, the fullscreen mode will be closed.

See also

, which opens a given record in fullscreen mode.

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.

More about the languages supported by Ninox

Syntax

clientLang()

Return

string

Examples

clientLang()

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

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

closeAllRecords()

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

See also

closeRecord, which closes the top record form.

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

See also

floor, which rounds down a given number to the next lower integer.

round, which rounds a given number to the nearest integer.

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.

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.

See also

index, which returns the first position of a searched match in a string or an array.

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.

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

closeRecord()

Result: The current record form will be closed.

See also

openRecord, which jumps to the related table and opens a given record.

closeAllRecords, which closes all record forms.

This function returns values from an array, a table, or a multiple-choice field one after the other separated by a comma in a text field or a text variable. It’s used to reflect the selected options of a multiple-choice field as a running text, for example in forms or print layouts.

It’s often used for intermediate steps within a script.

Syntax

concat([any])

Returns

string

Examples

concat('Last name', 'First name') 

Result: Böhmer, Frank (with Last name = Böhmer and First name = Frank)

See also

join, which returns a string of all items of a given string array by a given separator.

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.

See also

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.

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'

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:

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:

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.

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:

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.

See also

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:

1. Create a Button field: When clicked, the button triggers a function to create an Excel file from the data provided in the formula editor.

2. Create an Image field: Serves a link, allowing you to download the file directly.

1. 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)

1. Apply style to a header cell:

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

1. Apply style to an entire column except the header:

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

1. 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"
					}
				}
			}
		]
	}
];

1. 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

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
}];

This function allows you to cache calculations of an elaborate script during the first execution. Instead of a new calculation, the saved value is then returned.

But if you want to trigger a recalculation of your script, you have the following options:

• Activate the edit mode

• Execute the invalidate() function in any table of your database

Learn more about Optimizing scripts

Syntax

cached(script)

Return

any

Examples

let cache := cached(
    let user := user();
    select Tasks where 'Due Date' > today() and 'Assigned user'.'Ninox user' = user
);
cache

Result: The records from the Tasks table that are still open and assigned to the current user are cached and returned.

See also

invalidate, which clears the cache of the database and the values stored by cached().

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.

Syntax

color(string)

color(number, number, number)

color(number, number, number, number)

color(choice)

Return

color

Examples

color("blue")

Result: Blue, but not the Ninox blue.

color("#4970FF")

Result: The Ninox blue

color(73, 112, 255)

Result: The Ninox blue in RGB code

color(73, 112, 255, 0.5)

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

color(Status)

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

See also

icon, which returns an icon.

styled, which converts a given string to a styled object.

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

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).

See also

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

See also

, which calculates the arccosine of a number.

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.

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

Syntax

createTempFile(string, string)

Return

link

Examples

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

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

See also

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.

See also

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

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.

See also

createTextFile, which creates a text file with a given name and content.

‌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.

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

‌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.

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

Syntax

databaseId()

Return

string

Examples

databaseId()

Result: mcxhedkkmvpp (for example)

See also

teamId, which returns the ID of the current workspace.

tableId, which returns the ID of a table.

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.

Syntax

duration(appointment)

Return

timeinterval

Examples

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

Result: 2 Days

See also

, which converts given time-related values to an appointment.

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.

Result: 180

See also

which converts an angle from degrees to radians.

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.

Syntax

email(any)

Return

email

Examples

email(myEmail)

Result: steve.rogers@theavengers.com

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

See also

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.

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.

See also

replacex, which replaces matches of a given regular expression.

substr, which returns a new string out of a given text.

testx, which returns true if a given text matches a given regular expression.

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.

See also

files, which returns all attachments of a record as an array.

importFile, which imports a file from a URL and saves it as an attachment of a record.

The function calculates the exponential function of a number based on Euler's number and is therefore called the natural exponential function.

Syntax

exp(number)

Return

number

Examples

exp(x) To calculate the power to the base e (e is the Euler's number) with a number x as an exponent.

exp(1) 

Result: 2.718281828459045 (the Euler’s number)

exp(0) 

Result: 1

exp(5 - 2)

Result: 20.085536923187668

See also

ln, which calculates the natural logarithm of a number.

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.

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.

See also

alert, which opens a pop-up message box.

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

1. 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.

1. 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.

1. 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.

See also

tableId which returns the ID of a table.

databaseId which returns the ID of a database.

teamId which returns the ID of the current workspace.

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)

See also

odd, which returns Yes (true) if the number is odd.

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

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

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.

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

See also

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.

Result:

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

Result:

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

with the file name pulled from the Invoice image field.

See also

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.

See also

Create and delete records

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 set 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.

Examples

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

get(this, "Name")

Return: Vania

get(this, "Yes/No")

Return: true

get(this, "B")

Return: 42

get(this, "")

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:

let me := this;
let myReport := Report;
let newSubRecord := (create Subtable);
newSubRecord.(
	Report := myReport;
	'Object name' := me.'Object name 1';
	Date := me.'Date 1';
	Value := me.'Value 1'
);
newSubRecord := (create Subtable);
newSubRecord.(
	Report := myReport;
	'Object name' := me.'Object name 2';
	Date := me.'Date 2';
	Value := me.'Value 2'
);
newSubRecord := (create Subtable);
newSubRecord.(
	Report := myReport;
	'Object name' := me.'Object name 3';
	Date := me.'Date 3';
	Value := me.'Value 3'
)
end

Dynamic data collection with get and set

The get and set functions simplify this process. get pulls the value from the main record by building the field name dynamically based on the current index. Then, set pulls that value into the matching field in the new subtable record. Here's how you can use it:

let me := this;
let nameSpace := ["Object name", "Date", "Value"];
let myReport := Report;
for i in range(3) do
	let newSubRecord := (create Subtable);
	newSubRecord.(Report := me.Report);
	for name in nameSpace do
		set(newSubRecord, name, get(me, name + " " + (i + 1)))
	end
end

See also

set which updates the value of a given field.

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.

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.

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.

See also

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 API 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.

formatXML({
    contact: {
        @type: "Person",
        firstName: {
            @: 'First Name'
        },
        lastName: {
            @: 'Last Name'
        },
        phone: [{
                @type: "Mobile",
                @: Mobile
            }, {
                @type: "Phone",
                @: Phone
            }]
    }
}, true)

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

<?xml version="1.0" encoding="utf-8"?>
<contact type="Person">
    <firstName>Steve</firstName>
    <lastName>Rogers</lastName>
    <phone type="Mobile">(+1) (202) 123 45 67</phone>
    <phone type="Phone">+1.151.11 ++66 14 66</phone>
</contact>

See also

parseXML, which converts an XML string to a JSON object.

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.

importFile(this, "https://ninox.com/example.jpg", "Image.jpg")

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

Invoice := importFile(this, printAndSaveRecord(this, "Invoice layout"))

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

See also

printAndSaveRecord, which saves a given record in a given layout as a PDF in the internal file system and returns a link to the file.

file, which returns a specific file based on the file name from a given record.

files, which returns all attachments of a record as an array.

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

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

1. First, specify whether a prefix should be inserted, e.g. a currency symbol.

2. Should a 1000 separator be inserted?

3. Specify whether or how many decimal places you want to display.

4. Specify a unit, e.g. a currency sign.

5. Type of decimal place separator, e.g. period (.), comma (,), single quote (') or double quotes (").

6. Type of 1000 separator, e.g. period (.), comma (,), single quotation mark ('), double quotation marks (") or a space ( ).

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

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.

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.

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).

See also