Reference to a class or user

When using a special notation you can create a column of type Value from a referenced class. This column enables to link query data to a class record.

When we have e.g. class of projects referring in column primary_goal to the class goal and in column project_manager to an ObjectGears user, we can create a list of records, where the primary_goal will work as a link to the detail of the referred record and project_manager as a link to the page PersonQuickInfo with data of the given user.

References to a single record

Example: Class project referring to the class gol and to the internal class of ObjectGears users.

Entity relationship diagram:

SQL query:

SELECT pr.name, g.id AS Goal___goal, p.id AS Project_manager___person
FROM {{:class.project:}} pr
LEFT JOIN {{:class.goal:}} g ON pr.primary_goal = g.id
LEFT JOIN person p ON pr.project_manager = p.id

After the requested column code (and name at the same time) we will place three underscore signs and code of the class, into which the link shall point.

If the referred class 'goal' belongs to another model, it is necessary to state in the reference also model code and separate it from the model class by a colon.

SELECT pr.name, g.id AS [Goal___modelcode:goal], p.id AS Project_manager___person

Resulting record list:

There will be Short description of the referred record, resp. Full name of ObjectGears user displayed in the list instead of record id.

References to more records

In case columns in classes contain references to more records it is necessary to use slightly different syntax.

Example: Class Approval process refering to the internal class of ObjectGears users and to class Company. There is class Catalogue request referring to the class Approval process. All the mentioned relations are reference to more records.

Entity relationship diagram:

Data in the class Approval process look like this:

Columns in the class Approval contain multiple reference to users, multiple reference to another class and revers multiple reference from another class (another class references data from our class).

We will create this SQL query:

SELECT ap.id,
ap.name,
ap.id AS approvers___approval_process___approvers,
ap.id AS company___approval_process___applicable_for,
ap.id AS process___approval_process___cl_approval_process
FROM {{:class.approval_process:}} ap

All the referred records in columns referring to more records are not stored directly in the class ap (approval_process), but in associative table that we have described in the article Queries to columns with multiple references. We will identify the respective records in these associative tables by means of identifiers ap.id. The column of the associative table containing the respective references to the target classes is determined by the second and third element in the alias string (referring class and column of the referring class). The first element determines again the requested name and code of the query column that shall be created.

The resulting records overview in the query looks like this:

We can then set records sorting, way of displaying multiple records etc. in the query detail.