Relationships – how records of different entities belong together¶

A relationship connects the records of two entities: the customer of a ticket, the lines of an invoice, the tags of an article. This chapter describes the kinds of relationships, their settings, what they make possible and what happens when linked records are deleted.

It is intended for administrators who design the data model. Entities and fields are described in Entities and fields.

1. Relationships at a glance¶

A relationship always belongs to one entity, the parent, and points at another entity, the child. It answers the question "which records of the child entity belong to this record?".

Question	Setting	Choices
Which entity do the related records belong to?	"Child Entity"	Any entity, also the parent entity itself
How many related records can a record have?	"Cardinality"	ToOne – at most one, ToMany – any number
Where is the link stored?	"Foreign Key Location"	In the child, in the parent, in a join table, computed by an expression, or by field conditions

Relationships are managed in the Admin Studio under Entities: select the parent entity, then the group Relationships in the second column.

Summary¶

A relationship does not change the database. It uses fields that already exist – usually a Guid field that holds the record id of the other side.
Relationships are what forms, queries, expressions and actions use to reach related records. A Guid field alone only stores an id.
A relationship goes in one direction. For the way back, define a second relationship on the child entity.
Deleting a record does not delete or unlink its related records.

What relationships make possible¶

Where	What
Forms	Blocks that show the related records – as forms, a table, a list or a selector (see Forms)
Expressions	A field of the related record of a ToOne relationship; aggregates over a ToMany relationship – count, sum, average, minimum, maximum, exists (see Expressions)
Queries and filters	Conditions about related records, for example "has at least one open ticket" (see Queries)
Actions	"Create Related Record", "Update Related Record", "ForEach Related Record", "Link/Unlink Entity Records", "Delete Entity Record" on a related record (see Actions)

2. Creating a relationship¶

Open a sketch.
In the Admin Studio under Entities, select the parent entity – for example "Customer".
Click "+" next to the group Relationships. The management view of the relationships of this entity opens; choose "Add".
Enter a "Display Name", for example "Tickets".
Choose the "Child Entity" ("Ticket") and the "Cardinality" (ToMany). Only then is the "Foreign Key Location" offered.
Choose the "Foreign Key Location" and what it needs (section 4).
Save and publish the sketch.

The editor draws the relationship above the settings: the two entities, the fields that carry the key and the direction in which it is read. Missing settings are named there, for example "Choose the field that carries the foreign key."

3. Cardinality¶

Cardinality	Meaning	Diagram	Example
ToOne	A record has at most one related record	1 : 1	The customer of a ticket, the detail record of an order
ToMany	A record can have any number of related records	1 : n	The tickets of a customer, the lines of an invoice
None	Not set	–	Not a valid choice – the relationship returns nothing

"Many-to-many" – a ticket has several tags, a tag belongs to several tickets – is a ToMany relationship with a join table (section 4), defined on both sides.

4. Foreign key location¶

The "Foreign Key Location" decides where the link between two records is stored. A relationship whose location is not completely filled in has no related records – there is no error message.

Foreign key location	The link is …	Needs	Cardinality
"Foreign Key in Child"	a Guid field of the child that holds the record id of the parent	"Foreign Key Field" – a Guid field of the child entity	ToOne or ToMany
"Foreign Key in Parent"	a Guid field of the parent that holds the record id of the child	"Foreign Key Field" – a Guid field of the parent entity	ToOne only
"Use Join Table"	a row in a join table entity	"Join Table", optionally "Is Child (in Join Table)"	ToOne or ToMany
"Foreign Key Value Expression"	computed from the parent record	"Foreign Key Value" – an expression	ToOne or ToMany
"Custom Link"	defined by conditions on fields	"Custom Link Constraints"	ToOne or ToMany

Foreign Key in Child – the usual case¶

The child stores the record id of its parent. Use it for "one parent, many children".

Example: The entity "Ticket" has a Guid field "Customer RecId". The relationship "Tickets" of the customer: "Child Entity" Ticket, ToMany, "Foreign Key in Child", "Foreign Key Field" "Customer RecId".

Foreign Key in Parent – the lookup¶

The parent stores the record id of the one related record. Use it to point from a record to one record of another entity – a lookup.

Example: The relationship "Customer" of the ticket: "Child Entity" Customer, ToOne, "Foreign Key in Parent", "Foreign Key Field" "Customer RecId" of the ticket. Together with the relationship "Tickets" of the customer above, both directions use the same field.

Use Join Table – many-to-many¶

Each link is a record of a join table: an entity that stores which record is linked to which. One join table can serve many relationships, because every row also stores the two entities.

A join table is an ordinary entity with:

the entity flag SpecialPurpose.JoinTable,
four Guid fields with the flags SpecialField.ParentTypeId, SpecialField.ParentRecId, SpecialField.ChildTypeId and SpecialField.ChildRecId.

Only entities with the flag are offered under "Join Table". If the fields are missing, the diagram says so: "The chosen join table is missing the link fields (ParentRecId / ChildRecId)."

For the way back, define the reverse relationship on the child entity with the same join table and "Is Child (in Join Table)" switched on. It then reads the same rows from the other side.

Example: "Article" → "Tags": ToMany, "Use Join Table", join table "Links". On "Tag" → "Articles": ToMany, the same join table, "Is Child (in Join Table)" on.

Foreign Key Value Expression – computed¶

An expression evaluated on the parent record returns the record id of the child (ToOne) or a list of record ids as a JSON array (ToMany). An empty result means no related records.

Such a relationship is read-only: records cannot be linked or created through it. It cannot be used in queries – the editor says so with a notice.

Custom Link – by field conditions¶

A child record belongs to the parent when all "Custom Link Constraints" are met. Each constraint compares a field of the child with a field of the parent or with the result of an expression:

Setting	Meaning
" Field"	The field of the child on the left side of the condition
"Operator"	How the two sides are compared, for example Equals. Default: Equals.
"Use Value Expression"	Compare with an expression instead of a parent field
" Field" / " Value Expression"	The right side
"Populate ."	When a record is linked, the parent field is copied into the child field; when it is unlinked, the child field is cleared. Only with a parent field, not with an expression.

A parent record whose right-hand value is empty has no related records.

"Custom Link Population Rules" write further values when a record is linked and clear them when it is unlinked:

Setting	Meaning
"Field"	The field that is written – of the parent or of the child
"Use Value Expression"	Take the value from an expression instead of a field
"Value Field" / "Value Expression"	Where the value comes from

Example: Tickets belong to a contract when the ticket field "Contract number" equals the contract field "Number" (operator Equals) and the ticket field "Status" is not "Cancelled" (operator NotEquals, "Use Value Expression" with an expression that returns "Cancelled"). "Populate Ticket.Contract number" on the first constraint makes a ticket linked through the relationship carry the contract number.

5. Further settings¶

Setting	Meaning	Default
"Has Reverse Relationship"	The related records know the record they were loaded from. Expressions on a related record can then reach the parent without another query.	off
"Reverse Relationship"	The relationship of the child entity that points back to this entity. Only relationships whose child entity is this parent entity are offered.	empty
"Check Required Fields"	When the parent record is saved, the required-field rules of the related records that are saved with it are checked; a missing value blocks the save. Off, missing values in related records are not reported.	off
"Create Record Automatically"	As soon as the relationship is read and holds no record, a record of the child entity is created, linked and saved together with the parent – even when nothing was filled in. For the detail record every parent record should have.	off
"Don't Save Child With Parent"	Changes to the related records are not written when the parent is saved. Links that were added or removed are still written – the join table record, or the linked or unlinked child record.	off
"Load Immediately"	The related records are read as soon as a record of the parent entity is opened – for a new record after its default values were applied, for an existing record before the business rules that run on load. Rows of a grid skip it.	off

"Create Record Automatically" needs a link that can be written: foreign key in the child, in the parent (ToOne), a join table, or a custom link that populates a child field. A relationship with a "Foreign Key Value Expression" never creates a record.

6. Linking, saving and deleting¶

When links are written¶

Linking a record – in a form or with "Link/Unlink Entity Records" – sets the foreign key field, adds a join table record or fills the fields of a custom link. The change is written when the parent record is saved, together with the related records (unless "Don't Save Child With Parent" is on). Unlinking clears the foreign key field, removes the join table record or clears the populated fields.

The link itself is always written with the parent record, also with "Don't Save Child With Parent": the join table record, or – with a foreign key in the child or a custom link – the linked or unlinked child record. Until the save, the linked record already shows up in the relationship and an unlinked one no longer does. Discarding the changes of the parent record takes links and unlinks back.

If a record is unlinked before its link was saved, the link is simply dropped – the save writes nothing for it. A record that belonged to another record before keeps belonging to it.

Deleting records¶

Situation	Result
A parent record is deleted	Its related records stay. Their foreign key fields keep pointing to the deleted record; join table records stay.
A child record is deleted	The parent stays. With "Foreign Key in Parent", the parent's key field keeps the id of the deleted record.
Related records should go with the parent	Delete them with an action before deleting the parent – "ForEach Related Record" with "Delete Entity Record"

nexxcite does not check whether a record is still referenced before deleting it.

Deleting a relationship¶

Deleting a relationship only removes the definition. Fields and data are not touched. Forms, expressions, queries and actions that use the relationship stop showing related records and have to be adjusted.

7. Permissions¶

Permission	Allows
"Open Relationship Manager"	Seeing the relationships
"Add Relationship", "Edit Relationship", "Delete Relationship"	Creating, changing and deleting relationships

All four are part of "System Developer".

8. Limitations¶

Relationships with "Foreign Key Value Expression" are read-only and cannot be used in queries.
Grid columns show fields of the grid's own entity only, not fields of related records.
There is no cascading delete and no check for references (section 6).
A relationship that is not completely configured has no related records, without an error message. Check the diagram in the editor.

9. Frequently asked questions¶

Do I need a relationship if I already have a Guid field? Yes, as soon as you want to show the related record in a form, read its fields in an expression, query across it or count related records. The Guid field alone only stores the id.

Can I change the child entity or the foreign key location later? Yes, in the editor. Existing links refer to the old arrangement, though – records linked through a join table are not moved to a foreign key field, for example. Plan the relationship before records are linked through it. An AI assistant cannot make these changes.

Does a relationship create a column? No. Create the Guid field in the entity first, then the relationship that uses it.

Why are the related records empty? Check the diagram in the editor. Most often the "Foreign Key Field" or the "Join Table" is missing, or the join table lacks its flagged fields.