The Ninox script language (also "NX script language" or "NX script") is designed to automate simple, repetitive operations, as well as complex work sequences, to provide optimal support for your workflows.

We don't distinguish between functions and procedures in the following.

Ready? Let's go...

The input of functions or procedures is done via the formula editor in a formula field (always indicated by fx).

These fields are the "entrance" to the formula editor. We collected a few examples for you below.

These operators let you take 2 numeric values, perform a calculation, and return a numeric value. This is mainly about basic arithmetic operations.

Create a new variable with let and assign a value to the variable using :=.

Example

let myText := "Hello " + 'First Name' + "!";
myText

Result: Hello Sam! (if the name Sam is pulled from the First Name field)

Line numbers

Line numbers are displayed in the left margin of the formula editor window.

How to use

Line numbers are always visible by default.

Example

Indentation

Indents a code block automatically.

Code formatting

Takes your code and prettifies it to conform to a consistent style. Wraps code and adds or removes white space when necessary.

How to use

Access the formula editor to format existing code, click OK to leave the editor. The next time you open the formula editor again, this code appears newly formatted.

Example

Auto-completion

While you type, a pop-up appears next to the cursor displaying suggestions to complete your script.

How to use

To select the first option from the list, press the ENTER key.

To select another option, use the arrow keys, then confirm your selection with the ENTER key.

Example

Syntax highlighting

While you type, text is highlighted in different colors.

How to use

To trigger syntax highlighting, type an expression.

Example

Breadcrumbs

The current location of the written code is displayed above the formula editor's contents.

How to use

The location is displayed automatically.

Example

Error and warning marks

Displays errors and warnings while you script and suggest corrections.

How to use

While you type, gutter line numbers with erroneous code are highlighted and the code itself is underlined in red. To reveal the error message, hover above the line number.

Example

Search and replace

Search and replace text, variables, and expressions within a code block. Works for

• Match Whole Word,

• Match Case, and

• Use Regular Expression.

How to use

To search, enter text in the search bar or press CMD+F (macOS) or Ctrl+F (Windows).

Example

Brace matching and brace auto-completion

Highlights corresponding parentheses and automatically completes open parentheses.

How to use

To visually locate a parenthesis' match, use the arrow keys to select a parenthesis and highlight its counterpart.

To auto-complete parentheses, type an opening parenthesis.

Use if ... then ... else ... end to specify an if-then-else conditional statement that has Ninox check whether a sequence of statements should be executed (if... then...) or what should happen when the input condition is not met (... else...).

The if statement with comparison operators

The if statement must be an expression that is either true/yes or false/no.

The use of comparison operators, such as greater than (>=), less than (<=) or equal to (=) is also suitable for this purpose.

Depending on the result of the comparison, the further procedure is determined.

Example

Create a Total field (number field type), type the following script in a formula field, and vary the value to Total:

Result: If the entered value in the Total field is greater than or equal to €30, payment is made by Card, otherwise payment is made in Cash.

Result: paymentMethod is set to Cash by default. However, if the value in the Total number field is greater than €30, the value for paymentMethod is updated to Card.

Check data field or variable for content

Use null to check whether a data field is empty. So null does not stand for 0, but for empty. Combined with a branch, it allows you to execute a script even when a field is empty.

Example

Result: If the Total field is empty, a prompt ("Please enter an amount!") is displayed.

create

To create a record in a specific table by script, specify the corresponding table name in a button after create.

Example

create Customers

Result: A new, but empty record in the Customers table.

To fill this new record with data, first store the expression in a variable and then use the variable to access the desired fields.

1. Initialize the variable newCustomer with the expression create Customer to store the record in the variable.

2. Access the fields of the new record using the dot operator . and give the new record a unique Customer number consisting of the letter C and a UNIX timestamp.

Example

let newCustomer := (create Customers);
newCustomer.('Customer number' := "C" + number(now()))

Result: A new record in the Customers table with a unique customer number.

delete

Delete records automatically by specifying which records to delete after delete.

Insert the following script in a button to delete the current record.

Example

delete this

Result: The current record is deleted.

To delete several records, combine delete with select.

Example

delete select Customers where Status = 4

Result: All records in the Customers table with the status 4 are deleted.

To personalize texts, for example, send a letter that looks basically the same as an invoice to many recipients with the respective other data, this is easier to do with dynamic texts (see birthday greetings).

To make texts dynamic using a script, you have 2 options:

1. Connect simple strings and fields with the plus operator +.

2. Template strings with values inside braces {...}.

Simple strings

Concatenate simple strings with the + operator. Use + to include any data type, e.g., numbers.

Example

In a table, create a First Name field and paste the following content into a formula field.

"Hello " + 'First Name' + "!"

Result: Hello Sam! (if the First name field contains the value Sam)

Template strings

Template strings are indicated by a 3 minus signs --- at the beginning and end. Everything in between turns into text.

Put braces {...} around scripts, e.g., to write a serial letter that fetches the relevant data, such as first and last name, address, etc., from a table.

Example

Same as above, but with a template string.

---Hello { 'First Name' }!---

Result: Hello Sam! (if the First name field contains the value Sam)

Loops allow you to automatically execute a code block multiple numbers of times in succession. For example, apply the statements of a code block to each element of an array.

for ... in ... do ... end

One of the most important loops you need to know about—it'll be very helpful when writing your scripts. After for, select the corresponding variable name for the item in the list that you want to change.

The for loop is structured as follows:

Line 1: Not yet part of the loop but useful because it's easier to read: the records are stored in a variable.

Line 2: The loop starts with for [it follows a label for a single element from the list to be traversed] in [it follows the list] do.

Line 3: statement of what should be done.

Line 4: end terminates the loop (termination).

Example

You'd like to assign all customers who have status 2 a new one, status 1. First, select the customers with status 2 in the Customers (new) table. Then assign status 1 to each of these customers (customer).

let customers := select 'Customers (new)' where Status = 2;
for customer in customers do 
    customer.Status := 1;
end

Result: All customers who have status 2 are assigned status 1.

for ... from ... to ... do ... end

This loop is especially suitable when working with numeric values since instead of an array, a sequence of numbers will be iterated.

On each pass, 1 is added to the loop variable (increment = 1). It starts with the value after from (inclusive) and ends before the value after to (exclusive).

Example

let array := [10, 20, 30, 20, 10];
let result := 0;
for i from 0 to 3 do
	result := result + item(array, i)
end;
result

Result: 60

The values of the array with the indices 0, 1 and 2 are added. 10 + 20 + 30 = 60.

Change the height of the increment with step.

Example

let array := [10, 20, 30, 20, 10];
let result := 0;
for i from 0 to 5 step 2 do
	result := result + item(array, i)
end;
result

Result: 50

The values of the array with the indices 0, 2 and 4 are added. 10 + 30 + 10 = 50.

while ... do ... end

This loop is executed until the condition after while is no longer true.

Declare a counter variable, for example, which is incremented on each loop pass until the condition is no longer true. The condition is correct (true) as long as the variable is less than a specified value.

Example

let i := 0;
let result := "";
while i < 10 do 
    result := result + " " + i;
    i := i + 1
end;
result

Result: 0 1 2 3 4 5 6 7 8 9

Use order by to sort an array of records by a specific field. This is useful when sorting an array first before processing it further.

When you use order by in a View layout item, it is first sorted by a (selected) column header and second by the value specified after order by.

Example

(select Invoices) order by Total

Result: The entries of the Invoices table are sorted by the Total field (from small to large).

Example

Restrict the selection of records with where or brackets [...].

The value after order by does not necessarily have to be a field name, it can also be manipulated by functions, for example.

(select Invoices where Date = today()) order by number(substr('Invoice no.', 3))

Result: The entries with today's date of the Invoices table are sorted by the number in the Invoice no. text field.

3 refers to the position of the number within the invoice no., which in our case, for example, starts with the 4th position: NO-12574 (0=N, 1= O, 2=-, 3= 1 (the first number)).

Tip: Sorting strings

Be careful when sorting strings. Strings are not sorted alphabetically, but according to the index of the characters. So first all upper case letters are sorted alphabetically, then the lower case letters.

Explanation

ABcD is thus sorted to ABDc. To get the desired result, simply unify the values by upper() or lower(). Then all initial letters are first set to upper case or lower case respectively.

Result: ABcD

Alternative: Use the Ninox function sort() instead of order by.

Example

You have a table Example for order by with a First name field. It contains the following records: Aaron, Eddi, conrad, beate, Dahlia, and Fatima. Insert the following script into a formula field.

concat(((select 'Example for order by') order by 'First name').'First name')

Result: Aaron, Dahlia, Eddi, Fatima, beate, conrad

This is not the desired alphabetical sorting...

We add upper(), which sets all entries to upper case for sorting.

concat(((select 'Example for order by') order by upper('First name')).'First name')

If you only need the list of names and not the records themselves, we recommend using sort():

concat(sort((select 'Example for order by').'First name'))

Result: Aaron, beate, conrad, Dahlia, Eddi, Fatima

select lets you access any record of any table within a database using a script. Enter the name of the table from which you want to pull records after the select command.

Example

Insert the following script in the options of an embedded table view under Formula to display all the records of the Customers (New) table.

select 'Customers (New)';

Result: All records of the Customers (new) table are displayed in an embedded table.

Example

In the Customers table, a choice field Headquarter is available, in which the IDs of the choice values are assigned as

• 1 = Germany

• 2 = Austria

• 3 = Switzerland

• 4 = France

• 5 = Spain

• 6 = Italy

and you'd like to display only the DACH region, i.e., Germany, Austria, and Switzerland.

select 'Customers (new)' where 'Headquarter' = 1 or 'Headquarter' = 2 or 'Headquarter' = 3;

Result: In an embedded table the records of the table Customers (new) are displayed, which have their company headquarters in 1 (Germany), 2 (Austria), or 3 (Switzerland).

Useful alternative to ... where

You may further filter already selected records, i.e., an array of type [nid], by specifying the condition to filter by in brackets [...] after the array.

let customers := select 'Customers (New)';
customers['Headquarter'=1]

In contrast to select ... where, all records are selected first and then filtered. With select ... where only the records that meet the conditions are selected.

Key features at a glance

• Conditional data management

  • Creatable if allows for record creation only under specific conditions, for more control over when data is generated.

  • Deletable if blocks records from being deleted by mistake or without authorization, which safeguards data integrity.

• Enhanced compliance

  • Admins can apply rules that restrict certain actions, such as deleting sent invoices, to ensure adherence to policies.

• Increased data efficiency

  • Admins can manage the life cycle of records, which avoids creating unnecessary or duplicate entries. This makes data handling more efficient.

• Greater control in user workflows

  • Users can only create or delete records under specific conditions or through designated actions, which provides tighter oversight of user activities.

• Easier workflow automation

  • Parts of the workflow, like creating or removing records automatically when certain criteria are met, are streamlined for efficiency.

Comparing create records/delete records and creatable if/deletable if

The features Creatable if and Deletable if offer a nuanced approach to managing data within the formula editor. This is in contrast with the pre-existing methods of create records and delete records, where you only select a user role from a dropdown menu.

While the latter work as a shortcut to manage creation and deletion based on user roles, Creatable if and Deletable if let you specify more precise conditions in the formula editor.

Access Creatable if and Deletable if

1. Start by creating a new database:

   • Go to your workspace and click the New database tile.

2. Select a database template:

   • Choose the Offers and invoices template from the list.

3. Access your database:

   • Once the database is created, open the Offers and invoices database.

4. Navigate to the Invoices table:

   • Within your database, find and click the Invoices table.

5. Edit fields:
6. Set permissions:

   • In the settings pop-up, click Creatable if or Deletable if (3) to define conditions.

7. Configure conditions:

   • Use the formula editor that opens to set up your conditions as per the examples provided.

Set up permissions for creating records

1. In the formula editor, type userHasRole("Supervisor"). This means only users with the Supervisor role can create records.

2. Click Save to close the formula editor.

3. Click Save in the table settings to apply changes.

4. To test, attempt to create a record by clicking the plus icon. If unauthorized, a message saying You are not authorized for this action will appear.

Set up permissions for deleting records

1. In the formula editor, type Status = 1. This means records with the status Open can be deleted.

2. Click Save to close the formula editor.

3. Click Save in the table settings to apply changes.

4. To test, attempt to delete a record not marked as Open by clicking the trash icon. Upon confirmation, if unauthorized, a message saying You are not authorized for this action will appear.

See also

Ninox executes scripts in a perpetual exchange between the browser or app and the server. Usually, this works fine because the exchange is fast enough to process a script quickly.

However, in some cases you may further optimize the performance of your scripts by placing instructions in special code blocks.

More about Tips and tricks for fast databases.

do as transaction ... end

To ensure scripts are executed within the same transaction, it's best to use do as transaction.

do as transaction was developed specifically for mobile apps (iPhone, iPad, Android) and desktop app (Mac) to process scripts locally. For example, when the internet connection is interrupted.

When use do as transaction in the web app, the script is always executed on the server.

The nesting of loop and select commands is usually very performance-heavy, so we recommend wrapping such scripts in a do as transaction block to speed up the process.

Example

Let's assign a Contact from the Contacts table a matching ID to the Contact field in each record of the Companies table. The entire process is executed within do as transaction.

do as transaction
	for company in select Companies do
		company.(Contacts := first(select Contacts where Id = company.Id))
	end
end

Result: All records in the Companies table are linked to an entry from the Contacts table within one transaction.

do as server ... end

Occasionally, it may be beneficial to fully execute a portion of your script on the server first, before sending it back to your computer or app.

do as server is most often used in conjunction with the http() function to execute API calls server-side first. This bypasses the browser's CORS (cross-origin resource sharing) policy, which would otherwise block the http call. Then you receive the requested data from the server.

More about API calls.

Example

Let's get some data from a table in a database. First, you send a GET request to the respective database. Using the dot operator . , access the values of the response.

let response := do as server
				http("GET", "https://api.ninoxdb.de/v1/teams/" + teamId() + "/databases/" + databaseId() + "/tables/" + tableId(this) + "/records", {
				Authorization: "Bearer 0xxx0000-000x-00xx-x0x0-0x0x0000000x"
		}, null)
	end;
response.result

Result: The result value of the response is returned, which in this case consists of only one record and the information contained in the individual fields.

[{
    "id":1,
    "sequence":129,
    "createdAt":"2021-03-11T08:43:53",
    "createdBy":"xx0xxXX0XxxxXXxXx",
    "modifiedAt":"2022-01-21T14:20:36",
    "modifiedBy":"xx0xxXX0XxxxXXxXx",
    "fields": {
                "Product name":"Ninox Cola",
                "Product ID":"PID-123456789",
                "Price":0.99
              }
}]

do as deferred ... end

Normally Ninox executes write transactions in sequence.

Some of these write transactions may take more time, causing subsequent transactions to wait a bit. This may cause Ninox to slow down unintentionally.

Use the do as deferred statement to ensure read transactions within a write transaction are split off into a separate (read) transaction—all of which can be executed in the background.

A real-life use case

When you modify data in a large table, this may take several seconds instead of a few milliseconds. Downstream statements then have to wait until all data is completely modified.

do as deferred speeds up the processing of statements because statements that occur between do as deferred and end are processed separately and thus do not interfere with other processes.

Example

Optimize a status change with a resulting triggered email as follows.

Set a field Status = 3. This causes the trigger stored in the Status field to trigger after change the following instructions (see code block):

1. The field sent on is set to today's date.

2. An email with a payment request is sent. This is executed separately.

if Status = 3 then
	'Sent On' := today();
	do as deferred
		sendEmail({
			from: "sender@domain.com",
			to: "recipient@domain.com",
			cc: "recipient2@domain.com",
			bcc: "recipient3@domain.com",
			subject: "Your invoice",
			text: "Pay €100."
		})
	end
end

Result: All records whose status is set to 3 have today's date stored in the Sent On field. In a detached transaction, an email requesting payment is also sent to all records with the status 3.

This allows you to compare 2 numeric values. The operators return a result that is either true or false. The output in the Ninox field is Yes (true) or No (false).

Create your own functions with Ninox to automate workflows according to your needs.

Advantages

• write a script once and use it throughout the database

• concise scripts because you only need your own function and don't have to insert the entire script once or even multiple times

To define a function, use function followed by whatever name you choose. Then, in parentheses, specify which parameters are to be used. These are separated by commas.

Define parameters respectively as follows

parameterName : parameterType

Define functions globally

To use your functions across the entire database, enter the function in the tab bar of the database under Options in Global functions.

Options are displayed when you're in edit mode .

How it works

1. Enable edit mode

2. Click the Options tab

3. Enter your function(s) under Global Functions.

Insert the function in a formula field of a table and that function is only available in that formula field. This is useful when keeping repetitive sections short, or when your function refers to the current table, respectively.

Hello Mom. You are 100 years old!

Let's create a function hello to greet a person and tell them their age. First, pass the function a string for the name and a number for the age as parameters. These parameters are inserted into a given sentence at the appropriate positions.

Example

function hello(name : text,age : number) do
	"Hello " + name + ". You are " + age + " years old!"
end

Result: Hello Mom. You are 100 years old!

If you called the function like this: hello("Mom", 100)

Data types for creating user-defined functions

These following data types are already available for creating user-defined functions.

Create multiple conditional statements by concatenating if-then-else blocks. Write another if after else for another condition and so on—continue for as long as needed.

Example

As in the previous example, use the Total number field again and insert the following script into a Formula field:

if Total = null then
	"Please enter an amount!"
else if Total >= 30 then 
	"Card" else "Cash" end
end

Result: In the function field you will receive the response "Cash", "Card" , or "Please enter an amount!" according to the input.

Replace multiple nested conditional statements with switch ... case. Use this statement to query an expression (switch ...) for possible results (case ...:) and set a default behavior (default:) if the expression does not correspond to any of the results.

Depending on the result, assign the next step accordingly. This helps avoid deeply nested if statements.

Example

There's a choice field Payment method with the following options:

1. Cash

2. Bank transfer

3. Direct debit

Insert the following script into a formula field to display info about the selected payment type:

switch 'Payment method' do
case 1:
	"Payment method: " + text('Payment method') + "."
case 2:
	"Payment method: " + text('Payment method') + ". Only from €30."
case 3:
	"Payment method: " + text('Payment method') + ". Do not forget your signature."
default:
	"Please select a payment method."
end

Result: Based on your entry in the Payment method choice field, exactly one of the following info is visible in your formula field:

• Payment method: Cash

• Payment method: Bank transfer. Only from €30.

• Payment method: Direct debit. Do not forget your signature.

• Please select a payment method.