In Ultorg perspectives, a join against another table's single-column primary key can be displayed in a simplified manner. This display option, called Compact Join, allows fields from a referenced table to be displayed as part of the referencing field's cell formatting, without complicating the output with an extra level of column headers.

In actions relating to query building, formatting, and data editing, a compact join will behave more like a primitive field than a subquery. The actual query result remains unaffected.

Enabling and Disabling

The compact join option can be toggled from the context menu, as shown above.

You can also toggle compact join state from the field selector, using the checkbox next to the referenced table's icon ( / “Show Explicit Join” when unchecked):

In the field selector, compact joins are indicated with a referencing column icon (). Primary key fields are indicated with a key icon (). For compact joins, you can pick any combination of primitive fields to show from the referenced table:

The original referencing value can be either shown or hidden. In either case, it will be displayed in the formula bar whenever the cursor is on a compact join cell, as shown above.

In a relational database, a referencing field will often have the word “Key” or “ID” at the end of its name. If a compact join is showing fields other than the ID, Ultorg will hide this part of the name in the table header, e.g. to show “User” instead of “User ID” as in the example above.

Foreign Keys or Custom Joins

The Compact Join feature is applicable for joins against single-column primary keys only. The referencing field is often declared as a foreign key at the data source level, although this is not a requirement. You can use the Custom Join feature to join any field against another table's primary key, and display the resulting join as a compact join.

Actual foreign key fields, which are declared as such in the underlying data source, show a references <table name> indication in the data type box next to the formula bar:

The latter indication is shown only for single-column foreign keys. It is also shown for formulas that propagate unmodified foreign key values.

Recursive Field Selection

In some cases, primitive fields selected for display may themselves be eligible for display as a compact join. You may select fields through any number of single-column primary key joins:

Display Format

In a compact join, visible fields are shown in a space-separated manner. For example, two fields First Name and Last Name with the values “John” and “Smith” will be displayed as “John Smith”. You can reorder fields if desired.

Custom Format

To use a different format, you can add a calculated field with a formula that creates the desired display value, and select that field for display in the compact join:

The format shown in cells is also used by the dropdown menu that appears during data editing.

Non-Matching Values

If a referencing value has no matching row in the referenced table, then a question mark will be displayed in place of any fields selected from the latter.

Effects on Other Features

Compact joins interact with other Ultorg features in several ways, summarized below. For the main description of each feature, see their individual documentation page.

Sorting

When you sort on a compact join, the default behavior is to sort left-to-right on the displayed fields. If desired, you can use other fields from the referenced table instead.

To customize the fields to use when sorting on a compact join:

1. Right-click the compact join field and click Show Explicit Join.
2. Use the Sort actions as usual on the relevant field(s) in the referenced table.
3. Right-click the any primitive field in the referenced table, and click Show Compact Join.

Subqueries that are eligible to be displayed as compact joins are always single-row subqueries.

Filters

If you open the Filter popup on a compact join field, the filter options list will include the same related fields as are selected for display in the compact join:

The filter itself, however, will be associated with the underlying primitive field and its referencing values. So in the example above, we can search and display users with First Name and Last Name while filtering, but the actual resulting filtering is done in terms of the Owner User ID. If we later select other fields for display, the filter remains unaffected.

Formulas and Custom Group

The result of a formula may be displayed as a compact join if it is joined against a table's primary key. In this case the field selector shows a referencing formula icon (). This case may occur e.g. when the Custom Group action has been used to group by a foreign key value.

If a Custom Group with a compact join is used as part of a crosstab heading, then the value in the heading continues to behave like a compact join with respect to field selection and filtering.

Data Editing

The fields selected for display in a compact join are also used for search and display purposes when values from the same field are edited with the Edit from Dropdown feature. See the latter page for more details.

In Many-to-Many Relationships

Ultorg perspectives may traverse multiple levels of nested joins, sometimes in the one-to-many direction, where many child rows may occur for each parent row, and sometimes towards a primary key, where at most a single child row may occur.

When traversing a many-to-many relationship, a Compact Join is often useful in the last level. In the following example, a many-to-many relationship exists between Sections and Instructors, with Instructor Assignments serving as the “junction” table.

During data editing, this visualization allows new records to be inserted and referenced values to be picked from a dropdown menu.