records_related()

Get the records in another table that are linked (via a lookup field) to a given record, in either direction.

Syntax

records_related(sourceTable, recordId, relatedTable, direction = "in")

Parameters

Name Type Required Description
sourceTable string or number yes The starting record's table: its name, its database table name, or its numeric table id.
recordId int yes The source record's primary key.
relatedTable string or number yes The related table to fetch from: its name, its database table name, or its numeric table id. Read ids from $table at run time rather than typing them in, because table ids change when a workspace is copied.
direction string no "in" (default) = records in relatedTable that link to the source record. "out" = records in relatedTable that the source record links to via its own lookup field. "auto" = resolve to whichever direction the relationship actually exists in (outgoing if only outgoing, otherwise incoming).

Direction

A lookup field connects two tables one way, but the relationship can be read from either end:

  • Incoming ("in") — the related table owns the lookup field pointing back at the source record's table. E.g. from a Customer, get the Orders that link to it.
  • Outgoing ("out") — the source record owns the lookup field. E.g. from an Order, get the Customer(s) it links to. Unlike the lookup token (which carries only id + title), this returns the linked records with all their fields.

Returns

A list (array) of records from relatedTable related to the source record in the requested direction.

Example

$orders = records_related("customers", $record["_meta"]["id"], "orders");
sys_log($record["name"] . " has " . count($orders) . " orders");
foreach ( $orders as $o ) {
    sys_log(" - " . $o["title"] . ": $" . $o["amount"]);
}

Example output

[
    ["title" => "Order #1001", "amount" => 250, "_meta" => ["id" => 501, "title" => "Order #1001"]],
    ["title" => "Order #1002", "amount" => 80,  "_meta" => ["id" => 502, "title" => "Order #1002"]]
]

Outgoing example

// From an order, fetch the full customer record(s) it links to.
$customers = records_related("orders", $record["_meta"]["id"], "customers", "out");
foreach ( $customers as $c ) {
    sys_log($c["name"] . " — " . $c["tier"]);
}

Notes

  • For "in", the related table must have a lookup field pointing to the source table; for "out", the source record's table must have a lookup field pointing to the related table.
  • Throws if the source record can't be found, or if the two tables aren't related in the requested direction.
  • Outgoing returns [] when the source record's lookup field is empty.

See also: records_query()