Using a record's ID

Record IDs in Ninox

  • Each new record in Ninox is assigned a unique ID (identifier).
  • The numbering starts at 1 for a new table and increments by 1 for 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".
  • Example:
Ninox
"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:
Ninox
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:

Ninox
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:

Ninox
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):

Ninox
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.0 is accepted: it equals 3;
  • 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 with number().

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.

Ninox
"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.

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