> For the complete documentation index, see [llms.txt](https://docs.ninox.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.ninox.com/ninox-scripting/automate-your-workflows/work-with-functions/records-and-tables.md). # Records and tables 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: ```ninox sort((select orders).date) ``` Returns the selected order dates in ascending order. ```ninox rsort((select orders).amount) ``` Returns the selected order amounts in descending order. ```ninox let statuses := ["Draft", "Closed", "Open"]; sort(statuses) ``` Sorts the list alphabetically. ```ninox sort("B", "E", "A", "E", "D", "C") ``` Combines the values into one array and sorts them in ascending order. ```ninox sort(["B", "E", "A"], ["E", "D", "C"]) ``` Merges both arrays and sorts the combined result in ascending order. ```ninox rsort("B", "E", "A", "E", "D", "C") ``` Combines the values into one array and sorts them in descending order. ```ninox 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: ```ninox count(select orders) ``` Returns the number of records in the table "Orders". ```ninox count(select orders where status = "Open") ``` Returns the number of open orders. ```ninox 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. ```ninox count(["A", "B", ""]) ``` Returns `2` because the empty string is not counted. ```ninox 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: ```ninox unique((select orders).customer) ``` Returns each customer only once. ```ninox unique(["D", "C", "A", "A", "D", "B"]) ``` Returns the distinct values from the array, `["D", "C", "A", "B"]`. ```ninox 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. {% hint style="info" %} * 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. {% endhint %} #### Let’s take a look at some examples: ```ninox 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"]`. ```ninox 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: ```ninox first(["B", "A", "C"]) ``` Returns `"B"`. ```ninox first(select orders where status = "Open" order by date) ``` Returns the first open order in date order. ```ninox last(select orders order by date) ``` Returns the last order in date order. ```ninox last(["B", "A", "C"]) ``` Returns `"C"`. ```ninox last(select customer) ``` Returns the last record from the "Customer" table. ```ninox 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`. ```ninox 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 nth 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: ```ninox item(["A", "B", "C"], 0) ``` Returns `"A"`. ```ninox item(select orders order by date, 2) ``` Returns the third order in the sorted selection. ```ninox item(["Apple", "Cucumber", "Orange"], 1) ``` Returns `"Cucumber"`. ```ninox item(split("John D Ventures, 2311 Great Ave., 10178 Central City", ","), 0) ``` Returns `John D Ventures`. ```ninox 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: ```ninox slice([1, 2, 3, 4], 1, 3) ``` Returns a sublist with the middle values, `[2, 3]`. ```ninox let amounts := rsort((select orders).amount); slice(amounts, 0, 5) ``` Returns the first five values from the sorted amount list. ```ninox slice(["Apple", "Bagel", "Cake", "Donut"], 1, 3) ``` Returns `["Bagel", "Cake"]`. ```ninox 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: ```ninox record(orders, 123) ``` Returns the record with ID `123` from the "Orders" table. ```ninox let orderId := 123; let orderRecord := record(orders, orderId); orderRecord."Status" ``` Loads the order, then reads its status. ```ninox record(customers, 540).date ``` Returns the `date` field from the `customers` record with ID `540`. ```ninox 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: ```ninox tableId(orders) ``` Returns the internal ID of the "Orders" table. ```ninox tableId(this) ``` Returns the internal ID of the current table. ```ninox fieldId(status) ``` Returns the internal ID of the `status` field. ```ninox 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: ```ninox get(this, "Status") ``` Reads the `Status` field from the current record. ```ninox set(this, "Status", "Closed") ``` Updates the `Status` field on the current record. ```ninox set(this, "Age", 42) ``` Updates the `Age` field on the current record. ```ninox set(this, "isActive", true) ``` Updates the `isActive` field on the current record. ```ninox get(this, "B") ``` Reads the value of the field with the internal ID `B`. ```ninox 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: ```ninox 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: ```ninox 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: ```ninox get("theme") ``` Reads the stored value for `"theme"`. ```ninox let data := { lastName: "Rogers", firstName: "Steve" }; setItem(data, "firstName", "Brian") ``` Updates the `firstName` key in the `data` object. ```ninox let data := { lastName: "Rogers", firstName: "Steve" }; setItem(data, "age", 112) ``` Adds the key-value pair `age: 112` to the `data` object. ```ninox 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: ```ninox duplicate(this) ``` Creates a copy of the current record. ```ninox let newCopy := duplicate(this); newCopy.status := "Draft" ``` Creates a copy, then resets the field "Status" to `Draft`. ```ninox 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: ```ninox 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 ```ninox count(select Orders where status = "Open") ``` Returns the number of open orders. ### Sort records for a report ```ninox 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 ```ninox 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. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.ninox.com/ninox-scripting/automate-your-workflows/work-with-functions/records-and-tables.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.