Records and tables

Learn how to sort, inspect, update, and reuse records and record lists in Ninox scripts.

Records are the core of every Ninox app. You use them to build reports, update related data, and drive workflows. This chapter focuses on functions you use with records and record lists after you already have a selection or record reference.

In this chapter, you will learn how to:

• Sort record lists and value lists for reports and views.

• Count selections, remove duplicates, and pick specific items from a list.

• Fetch records by ID and inspect table, field, database, and team IDs.

• Read and update fields by name, store simple key-value pairs, and duplicate records.

• Reuse cached results and refresh data when needed.

Function (A-Z)	Task
array()	Merge two arrays of the same type
count() cnt()	Count items in a list or selection
duplicate()	Create a copy of a record
fieldId()	Return the internal ID of a field
first()	Return the first item from a list or selection
get()	Read a field by name or a stored key
item()	Return one item at a specific position
last()	Return the last item from a list or selection
record()	Fetch a record by table and ID
removeItem()	Remove a key-value pair from a JSON object
rsort()	Sort values in descending order
set()	Update a field by name on a record
setItem()	Update or add a key-value pair in a JSON object
slice()	Return part of a list
sort()	Sort values in ascending order
tableId()	Return the internal ID of a table
unique()	Remove duplicate values from a list

Sort, count, and deduplicate record lists

Use these functions when you want to control order or summarize a list.

Sort value lists with sort() and rsort()

Use sort() and rsort() when you already have a list of values and want them in ascending or descending order. Use the select statement with order by when you need to query and sort records in ascending order.

Use them when you want to:

• Sort extracted field values before output.

• Prepare values for charts, summaries, or quick comparisons.

sort([any]) rsort([any])

• [any]: the list of values you want to sort

For sort() and rsort(), all passed values must use the same data type.

If you pass single values instead of an array, Ninox combines them into one array and sorts the result.

If you pass one or more arrays to sort() or rsort(), Ninox merges them into one sorted array.

Let’s take a look at some examples:

sort((select orders).date)

Returns the selected order dates in ascending order.

rsort((select orders).amount)

Returns the selected order amounts in descending order.

let statuses := ["Draft", "Closed", "Open"];
sort(statuses)

Sorts the list alphabetically.

sort("B", "E", "A", "E", "D", "C")

Combines the values into one array and sorts them in ascending order.

sort(["B", "E", "A"], ["E", "D", "C"])

Merges both arrays and sorts the combined result in ascending order.

rsort("B", "E", "A", "E", "D", "C")

Combines the values into one array and sorts them in descending order.

rsort(["B", "E", "A"], ["E", "D", "C"])

Merges both arrays and sorts the combined result in descending order.

Tips:

• order by does sort the result only in ascending order. For descending sorting use rsort().

Count items with count() and cnt()

Use count() to return how many concrete items are in a selection or list. cnt() does exactly the same like count().

Use it when you want to:

• Count records that match a filter.

• Check whether a selection is empty before you continue.

• Compare a list with unique() to detect duplicates.

count([any]) cnt([any])

• [any]: the list or selection you want to count

count() and cnt() count items that are not null and not empty strings ("").

Let’s take a look at some examples:

count(select orders)

Returns the number of records in the table "Orders".

count(select orders where status = "Open")

Returns the number of open orders.

let openOrders := select orders where status = "Open";
if cnt(openOrders) = 0 then "Nothing to process" else "Ready" end

Uses cnt() to check whether the filtered result is empty.

count(["A", "B", ""])

Returns 2 because the empty string is not counted.

let ids := (select contacts).customer_id;
cnt(ids) = cnt(unique(ids))

Returns true when all values are unique. Returns false when duplicates exist.

Tips:

• Use length() when you want to count all array items, including empty values.

Remove duplicates with unique()

Use unique() to remove repeated values from a list.

Use it when you want to:

• Derive one list of distinct values from many records.

unique([any])

• [any]: one or more lists from which you want to remove duplicates

If you pass more than one array, Ninox combines them and returns one array with unique values only.

Let’s take a look at some examples:

unique((select orders).customer)

Returns each customer only once.

unique(["D", "C", "A", "A", "D", "B"])

Returns the distinct values from the array, ["D", "C", "A", "B"].

sort(unique(["A", "D", "A", "B"], ["B", "C", "E", "D"], ["F", "E"]))

Combines the arrays, removes duplicates, and sorts the result, ["A", "B", "C", "D", "E", "F"].

Tip:

• Compare count(list) with count(unique(list)) to check whether a list already has unique values.

Merge two arrays with array()

Use array() to merge two arrays of the same data type into one new array.

Use it when you want to:

• Merge two result arrays before one shared check.

array([any], [any])

• first [any]: the first array

• second [any]: the second array

array() returns one array.

• Use array() when you need to merge exactly two arrays. To merge more arrays, call array() again with the result and the next array.

• Both input arrays must contain values of the same data type.

Let’s take a look at some examples:

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

Returns one merged array with the values from both arrays, ["1", "2", "3", "4", "5", "6"].

let myArray1 := ["A", "B", "C"];
let myArray2 := ["D", "E", "F"];
let myArray3 := ["G", "H", "J"];
let myArray4 := ["K", "M", "W"];
let myArray5 := array(myArray1, myArray2);
let myArray6 := array(myArray3, myArray4);
let myArrayTotal:= array(myArray5, myArray6);
myArrayTotal

Returns one merged array with the values from 4 arrays: ["A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "M", "W"].

Access items in record lists

Use these functions when you want one record, the list edges, or only part of a larger result.

Get the first or last item with first() and last()

Use first() and last() to return one item from the start or end of a list or selection.

Use them when you want to:

• Pick the earliest or latest record from a sorted selection.

• Read only one result from a larger list.

first([any]) last([any])

• [any]: the list or selection from which you want one edge item

first() returns the first item from an array or selection. last() returns the last item from an array or selection.

Let’s take a look at some examples:

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

Returns "B".

first(select orders where status = "Open" order by date)

Returns the first open order in date order.

last(select orders order by date)

Returns the last order in date order.

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

Returns "C".

last(select customer)

Returns the last record from the "Customer" table.

first((select customer where substr(last_name, 0, 1) = "A") order by last_name)

Returns the first customer record where the last name starts with A.

last((select customer where substr(last_name, 0, 1) = "A") order by last_name)

Returns the last customer record where the last name starts with A.

Tips:

• Check whether the selection is empty before you depend on the result.

• If the array contains record IDs, the result can differ across platforms because record IDs are ordered differently.

Get one item by position with item()

Use item() to return one value or record from a specific position in a list, or to read a value from a JSON object by key.

Use it when you want to:

• Read the n^th value from an array.

Positions are zero-based. Position 0 is the first item.

item([any], number) item(JSON, number) item(JSON, string)

• [any]: the list or selection you want to read from

• number: the zero-based position or a numeric JSON key

• string: the JSON key

Let’s take a look at some examples:

item(["A", "B", "C"], 0)

Returns "A".

item(select orders order by date, 2)

Returns the third order in the sorted selection.

item(["Apple", "Cucumber", "Orange"], 1)

Returns "Cucumber".

item(split("John D Ventures, 2311 Great Ave., 10178 Central City", ","), 0)

Returns John D Ventures.

let data := {
    firstName: "Jane",
    lastName: "Doe"
};
item(data, "firstName")

Returns "Jane".

Tips:

• Use first() or last() when you only need the edges of a list.

• Use a string key or numeric key when you read from JSON.

Return part of a list with slice()

Use slice() to return a sublist from a larger array or selection, or to extract part of a text.

Use it when you want to:

• Split a list into smaller chunks.

slice([any], from, to) slice(string, from, to)

• [any]: the list or selection you want to slice

• string: the text you want to slice

• from: the start position, inclusive

• to: the end position, exclusive

Positions are zero-based.

If to is larger than the available number of items or characters, Ninox returns everything from from to the end.

Let’s take a look at some examples:

slice([1, 2, 3, 4], 1, 3)

Returns a sublist with the middle values, [2, 3].

let amounts := rsort((select orders).amount);
slice(amounts, 0, 5)

Returns the first five values from the sorted amount list.

slice(["Apple", "Bagel", "Cake", "Donut"], 1, 3)

Returns ["Bagel", "Cake"].

slice("Mermaid", 3, 7)

Returns "maid".

Tips:

• from is inclusive and to is exclusive.

• With text, slice() works like substring().

Work with records, tables, and IDs

Use these functions when you need one specific record or metadata about your current structure.

Fetch a record by ID with record()

Use record() to load one record from a table when you already know its numeric ID.

Use it when you want to:

• Reopen a referenced record by ID.

• Reconstruct a record reference from stored metadata.

record(table, id)

• table: the table that contains the record

• id: the numeric record ID

record() returns a record reference.

The returned value is referential, not the full record itself.

record() always returns a record-reference type, even if the numeric ID does not exist.

If you need to check whether the record exists, use record(table, id)._id. It returns the record ID only for existing records.

Let’s take a look at some examples:

record(orders, 123)

Returns the record with ID 123 from the "Orders" table.

let orderId := 123;
let orderRecord := record(orders, orderId);
orderRecord."Status"

Loads the order, then reads its status.

record(customers, 540).date

Returns the date field from the customers record with ID 540.

if record(customers, 540)._id then
    "Exists"
else
    "Not found"
end

Checks whether the record with ID 540 exists.

Get internal IDs with tableId() and fieldId()

Use these functions when you need metadata for logging, API calls, diagnostics, or admin scripts.

Use them when you want to:

• Build API URLs dynamically.

• Inspect internal object references.

tableId(table) tableId(record) tableId(string) fieldId(field) fieldId(record, string) fieldId(string, string)

• table: the table for which you want the internal ID with tableId(table)

• record: a record from the table for which you want the internal ID with tableId(record)

• string: the table name for tableId(string), or the field name or table-and-field names for fieldId()

• field: the field for which you want the internal ID with fieldId(field)

tableId() returns the internal identifier of a table as a string.

Table IDs use capital letters and start with A.

fieldId() returns the internal identifier of a field as a string. Field IDs are letter-based values such as A, B, AA, or AB.

Let’s take a look at some examples:

tableId(orders)

Returns the internal ID of the "Orders" table.

tableId(this)

Returns the internal ID of the current table.

fieldId(status)

Returns the internal ID of the status field.

fieldId(this, "Name")

Returns the field ID for Name in the current table.

Tips:

• Use tableId() as a stable identifier in API calls.

• Keep field IDs for technical workflows, not user-facing labels.

Read, update, copy, and store values

Use these functions when you want to change a record by field name or keep a small stored value for later reuse.

Read and update fields with get() and set()

Use get() to read a field from a record. Use set() to update a field on a record.

Use them when you want to:

• Read a field dynamically.

• Update another record inside a script.

• Write generic helper logic that works across fields.

get(record, string) set(record, string, any)

• record: the record you want to read or update

• string: the field name or field ID

• any: the value to write with set

get() can return different value types, such as text, numbers, booleans, or an empty value.

set() updates a field by its name or by its field ID as text, for example "Name" or "A".

set() does not return a value.

Let’s take a look at some examples:

get(this, "Status")

Reads the Status field from the current record.

set(this, "Status", "Closed")

Updates the Status field on the current record.

set(this, "Age", 42)

Updates the Age field on the current record.

set(this, "isActive", true)

Updates the isActive field on the current record.

get(this, "B")

Reads the value of the field with the internal ID B.

let nextOrder := first(select orders where status = "Open" order by date);
set(nextOrder, "Status", "In progress")

Updates the first open order in date order.

Tips:

• Use normal field access when the field name is fixed.

• Use a field ID when you want the script to stay stable even if the field name changes.

• get(this, "") returns an empty value.

• Use set() when you want to update many fields without repeating one assignment per field.

Example for dynamic field mapping:

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

This reads numbered fields from the current record and writes them into new Subtable records.

Example for dynamic updates from API-style JSON data:

for i in response do
    let newContact := create contacts;
    for key, value in i.fields do
        set(newContact, key, value)
    end
end

This loops through the fields in each response item and updates the matching Ninox fields dynamically.

Read stored values with get(), and update JSON objects with setItem() and removeItem()

Use get() when you want to read a stored key-value entry. Use setItem() to update or add a key-value pair in an existing JSON object. Use removeItem() to remove a key-value pair from a JSON object.

Use them when you want to:

• Update one value inside a JSON object.

• Add a new key to a JSON object.

• Remove one key from a JSON object before you reuse or send it.

get(string) setItem(JSON, string, any) removeItem(JSON, string)

• string: the storage key for get

• JSON: the JSON object you want to change

• string: the key you want to update, add, or remove

• any: the value to write with setItem

Let’s take a look at some examples:

get("theme")

Reads the stored value for "theme".

let data := {
    lastName: "Rogers",
    firstName: "Steve"
};
setItem(data, "firstName", "Brian")

Updates the firstName key in the data object.

let data := {
    lastName: "Rogers",
    firstName: "Steve"
};
setItem(data, "age", 112)

Adds the key-value pair age: 112 to the data object.

let data := {
    lastName: "Rogers",
    firstName: "Brian"
};
removeItem(data, "firstName")

Removes the firstName key-value pair from the data object.

data is changed directly.

Tips:

• get(this, "Field") reads a record field, but get("key") reads a stored key-value entry. Keep the two get() forms separate in your scripts.

• If the JSON object is stored in a variable, removeItem() changes that object even if you do not assign the result again.

• If the JSON object is stored in a variable, setItem() also changes that object even if you do not assign the result again.

Copy a record with duplicate()

Use duplicate() to create a copy of an existing record.

Use it when you want to:

• Reuse a similar record as a template.

duplicate(record)

• record: the record you want to copy

duplicate() returns the duplicated record with a different record ID.

When you duplicate a record, Ninox copies all field values, but no file attachments. Sub-tables (composition) records are also duplicated.

Let’s take a look at some examples:

duplicate(this)

Creates a copy of the current record.

let newCopy := duplicate(this);
newCopy.status := "Draft"

Creates a copy, then resets the field "Status" to Draft.

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

Creates a duplicate of the current record and opens it.

Tips:

• Reset unique values after duplication when needed.

• Review copied status fields, numbers, or references before you continue.

Refresh cached results and query SQL connections

Use these functions when your script depends on external data, repeated calculations, or a connected SQL database.

Reuse expensive results with cached()

Use cached() to run a script once, store its result, and return the saved value on later calls.

Use it when you want to:

• Reuse the result of an elaborate script.

• Cache a selection that is expensive to build.

• Improve performance in scripts that repeat the same calculation.

cached(script)

• script: the script or expression to run once and cache

Let’s take a look at some examples:

let cache := cached(
    let currentUser := user();
    select Tasks where due_date > today() and assigned_user.ninox_user = currentUser
);
cache

Returns the open Tasks records assigned to the current user. The result is cached and reused on later calls.

Tips:

• cached() returns whatever the inner script returns.

• Recalculate the cached result by switching to edit mode or running invalidate().

• Use caching for elaborate scripts that would otherwise slow down the app.

Common record and table recipes

These short patterns cover common record queries and updates in Ninox.

Count open orders

count(select Orders where status = "Open")

Returns the number of open orders.

Sort records for a report

select orders where status = "Open" order by customer + amount

Returns open orders in a predictable report order.

Duplicate the current record and reset its status

let copy := duplicate(this);
copy.status := "Draft"

Creates a new draft from the current record.

Use this chapter together with filtering, formatting, and reporting logic:

• Use date functions inside where and order by clauses.

• Use text functions to format selected values for output.

• Use numeric functions to summarize selected amounts.