Using a record's ID
Record IDs in Ninox
- Each new record in Ninox is assigned a unique ID (identifier).
- The numbering starts at
1for a new table and increments by1for each new entry (2,3, etc.). - Deleted IDs are not reassigned. Therefore, the highest ID in the table reflects the total number of records created, including those that were deleted and are no longer visible.
- ID Representation:
- IDs can be represented as numbers or text.
- When represented as text, the ID combines the table's ID (one or more uppercase letters) with the record number.
Example:"A1"
Key notes on IDs
- Converting an ID to Text:
- Use the
string()function to get a textual representation of an ID. - The
text()function only returns the numeric part of the ID as text. Example:"1".
- Use the
- Example:
"Correct textual representation, e.g. ""A1""";
string(maTable.id);
"Only the numeric part as text, e.g. ""1""";
text(maTable.id);- Using IDs in Expressions:
- When performing arithmetic or concatenation, the ID adopts the type of the other data.
- Example:
var t := "Num ID: " + maTable.id;Output : Num ID: 1
Searching for Records by ID
To retrieve a record by ID in a where clause, you can use either a number or a text representation.
Using a Number:
select Customer where id = 1;Using Text:
When using text, ensure that the ID includes both the table's ID and the record number.
Example:
select Customer where id = "B1";Extract the record number from an ID
This function lets you pull out the record number from a text‑based ID (e.g., A1):
function recordNumber(id : text) do
"Capture the digit group.";
number(extractx(id, "^[A-Z]+([0-9]+)$", "", "$1"))
end;
"Example: apply to the current record’s ID";
var numId := recordNumber(string(myId));string(myId) converts the ID to text.
RegExp ^[A-Z]+([0-9]+)$ means
^[A-Z]+→ one or more letters at the start,([0-9]+)→ the digits we want (capture group 1),$→ end of the string.
extractx(..., "", "$1") returns only the captured digits.
number(...) converts that digit string to a number.
Finding a record with record()
record(Table, n) returns record number n directly, without going through the table. The number must be a whole number greater than zero:
- a decimal number (
3.5), zero or a negative number returns nothing, without any message: Ninox does not round the number; 3.0is accepted: it equals3;- a text (
"3") or a list ([3, 4]) is rejected as soon as the formula is written ("Expression does not return a number", "Expression returns multiple values"): a number received as text must first be converted withnumber().
The trap shows up with a calculated number (record(Customer, total / 2) when total is odd) or a number received from outside, through the API for example.
"Found: record number 3";
record(Customer, 3);
"Nothing, and no error: Ninox does not round";
record(Customer, 3.5);
"A number received as text must be converted first";
record(Customer, number("3"));Warning
A whole number that matches no record (deleted, or not yet created) does not return "nothing": record() returns a reference to a missing record. if record(Customer, 999999) then is therefore true, whereas if record(Customer, 3.5) then is false.
To find out whether the record exists, test its ID: it is empty in both cases.
var n := 3.5;
if record(Customer, n).id then
"The record exists"
else
"No record: n is not a whole number greater than zero, or no record has this number"
end(Measured through the API, on a table of 8 records.)
