Even thought tables in a data model are related in a way where one of them is the parent and the other the child, i.e., one-to-many relationship, it is possible to enforce that each record will only have a single related record; for instance, let’s say we have the table "Project" related to the table "Purchase order", and it is wished that the purchase order assigned to a project cannot be used for any other project other than that one, like:

Project Purchase order

Proj-A

PO-10001

Proj-B

PO-10002

If there were a Proj-C, the purchase orders PO-10001 and PO-10002 would not be available for it, because they are already taken, i.e., a unique purchase order for a unique project.

For that, create a field from the table "Project" (you may call it "PO"). As Type, choose Relation; as Parent table, choose Purchase Order. Then tick the Unique checkbox and save. Optionally, you may need to tick Mandatory as well, in case you need that no project is ever missing a purchase order. Then, for all the forms where you display a project with the "PO" Relation, apply the following Filter on the Relation field:

RecordName not in(PO from Project)

Make sure there are records available in the "Purchase order" table.

Alternatively, in case you don’t have purchase orders available, but instead you wish to automatically create and relate a purchase order once a project is created, go to Rules manager, and create the following rule:

Trigger and Target table: Project

Trigger condition: Record keyword CREATED

Actions: Run IQL

let @relatedPO = insert "Purchase order"
  values RecordGroup = STANDARD

set PO = @relatedPO

Note that "RecordGroup = STANDARD" is just an example, but there you can add other fields of "Purchase order", read more here.

Then, go to Data model editor, select the "Purchase order" table, and set Record name policy to Generated series. Otherwise, you must provide a custom name for the RecordName in the rule above. For this alternative, you won’t need the Unique and Mandatory properties for the Relation field, nor the Filter in the Relation field in forms explained at the beginning of this topic. However, we recommend to have the Relation field as read-only in the forms, so no one can unassign what it was made in an automatic way.