Join forms

You can create a join form to combine information from multiple AR System  forms. This composite form includes fields derived from other existing forms. Use join forms to avoid data redundancy (information is stored in only one form) and maintain data integrity (information updated through the join form is updated in all other places).  For example, you can combine the information from the Help Request and the Employee ID forms (as shown in the following figure) into a join form that displays information from both forms without duplicating employee information in every help request. You can combine a join form with other forms, or you can join a form to itself.

How joins work in  AR System

Primary and secondary forms in the join form

When creating join forms, you designate one of the underlying forms as the primary form and the other as the secondary form. Primary forms are used when determining which extra entries are included in outer join forms (see Inner and outer joins below) and the execution order in workflow (see Filters and join forms below).

Join criteria

Join criteria define the link between the two underlying forms. Join criteria are values common to the forms that you want to join. For example, if a help desk form and an employee record form both have an employee ID field, the two can be joined by the equality relationship between them, as shown in the following figure. In database terms, this is an equal join.

Join criteria in  AR System

Try to use indexed fields in the join criteria. A join, like any other query of the database, should be optimized for best performance results. For information about indexing fields, see Defining indexes.

Including fields in the join form

You select the fields from the primary and secondary forms that will be part of the join form. If you select two fields that have the same field ID, the system provides a new mapped field ID for one of the fields because duplicate field IDs are not allowed in a form. The new field ID is mapped to the actual field ID in the underlying form when operations are performed.

In a join form, Remedy Developer Studio tries to preserve the name and field ID of fields from the primary form.

You can change the display properties for fields in a join form and set permissions for the join form itself. After creating a join form, you can add display-only fields to the form. For information, see Creating and managing fields.

Inner and outer joins

You can create two types of join forms:

• Inner join—Selects entries (or rows) only when corresponding values exist in both forms. For example, to retrieve only the entries from one form that have matching entries in another form, use an inner join. If an entry in one of the forms does not have a corresponding entry in the other form, the data is omitted.
• Outer join—Includes all of the entries from the form that you select as primary, even entries for which there are no matching entries in the secondary form. For example, to see all submitted help requests, including those that have no specific employee information connected with them, create an outer join.

An outer join in AR System  is what relational database administrators call a left outer join. Selecting the left (or primary) form includes all of the entries associated with that form.

The following figure illustrates the concept of inner joins. The Library Catalog form is the primary form. The Customer Checkout form is the secondary form. The join criteria is the ISBN (International Standard Book Number).

Because an inner join creates a form that contains only the entries in which the join criteria exists in both the primary and secondary forms, the join form produces a report that shows only the titles that are checked out.

Example of an inner join

If the library had produced the same report using an outer join, it would be a comprehensive listing of all the catalog items in the library, regardless of whether they had corresponding entries in the other form. The the following figure shows an example of an outer join.

Example of an outer join

When determining whether to create an inner join or an outer join, one approach is to base your choice on how much data you want to see. Inner joins are more useful for ad hoc queries and selection lists, while outer joins are more useful for special reports that are comprehensive by nature.

Joining three or more forms

To join three forms, you must first join two forms and then join the resulting form to the third, creating a hierarchy of joins. Joining multiple forms in a hierarchical order makes it easier to provide a consistent workflow.

If you need to combine data from three or more forms, you can do so by creating a series of two-way joins. As shown in the following figure, you can join two regular forms, a regular form to a join form, or two join forms.

Joining several forms

Add only as many join layers as you need, and make sure that your join criteria is efficient. The practical upper limit for combining forms is about six layers. This is because each join form is created by querying the database--which ultimately affects system performance. In addition, the workflow attached to each form in multiple layers of joins can also severely impact performance.

You cannot create a join form using a display-only form.

Self-join forms

You can join a form to itself. This is also known as a "Cartesian Join." This functionality is useful when comparing data from the same form or when preparing reports. Suppose that you want a report of all of the managers, the managers' phone numbers, the employees they supervise, and the employees' phone numbers. Assume also that the employees and managers both exist in this form.

In this example, DemoHD:Staff is the primary (designated as A) and the secondary form (designated as B), and it has the data shown in the following table.

Self-join example 1

If you join the form to itself and specify A.Employee ID = B.Manager ID as the join criteria, you can then add and rename the following fields in the resulting join form:

• A.Employee Name (from the primary form) renamed to Manager.
• A.Employee Phone Number (from the primary form) renamed to Manager Phone Number.
• B.Employee Name (from the secondary form) renamed to Employee.
• B.Employee Phone Number (from the primary form) renamed to Employee Phone Number.

The join form with four fields (or five if you include the composite request ID) contains the following results from an unqualified search.

Self-join example 2

You can include phone numbers for each manager and employee in one entry, even though they come from the same column in the same table. The self-join logically joins two separate forms that contain identical information.

Self-join forms are useful for certain parent-child relationships. In the previous example, the manager-employee relationship is a type of parent-child relationship in which child entries (the employees) belong to a parent entry (the manager).

Requirements for creating a join form using a vendor form

This section explains the requirements for creating a join form using a vendor form.

• Do not use pipe characters in the Entry ID of the vendor form that is a part of the join form. AR System server  distinguishes between the Entry IDs using the pipe characters as a separator. For example, do not use 00001|a4.
• The Entry ID list length of a vendor form must be a fixed length.
• The Entry ID length for all the entries of a vendor form must be the same, that is, a vendor form cannot have different Entry ID lengths. For example, if the Entry ID length of the first entry is 20, the Entry ID lengths of all the remaining entries must also be 20.

Following are the limitations of creating a join form using a vendor form:

• The data from the join form is available only when the form is accessed through the AR System server 's API. For example, the reporting engines that work directly against the database do not receive data from the vendor forms.
• The direct SQL and Set Fields with the SQL data source does not make data available from the joins form.

Tracking entries in join forms

AR System  tracks the entries in the underlying forms that comprise a join form through the request ID of each entry. When you look at the Request ID field in a join form entry, you see that the field contains the Request ID of each underlying entry separated by a vertical bar. For this reason, if you create a join form from a non-join form and a join form (see join form H in Joining several forms), the join form's Request ID field contains three request IDs separated by vertical bars.

The default permissions of the Request ID field in a join form are defined as Visible for the Assignee, Public, and Submitter groups. (For all other fields, the permissions are inherited from the underlying forms and cannot be changed.) However, you can remove the Public permissions of the Request ID field to make row-level security work in AR System . For information, see Controlling access by using implicit groups—Row-level security.

When creating entries through a join, AR System  does not initiate a database operation. Because creating an entry in a join is not a determinate action, the system cannot automatically perform it. However, all filter operations defined for a join are performed, which requires you to define workflow that appropriately creates or modifies entries in the primary and secondary forms. 

For example, if you create a join between a customer and an item purchased, creating an entry through the join can be defined through workflow. With push fields filter actions, the workflow can create an entry for an item purchased or an entry for a customer.

Filters and join forms

When you use join forms in workflow, all of the typical execution conditions that apply to objects in non-join forms also apply. 

To enable the "data entry" functionality of a join form, data created or updated for a particular field must be associated with the appropriate database. The administrator must create workflow that defines how data should be pushed into the database when entered through join forms.

Filters are used to enforce the integrity of the system. When you use filters with join forms, the filters for the join form execute first, and then the filters for the underlying forms execute. The following figure illustrates the execution order of the join forms and their primary and secondary forms. 

Execution order of filters with join forms

As you go down the execution order of the filters, all of the forms on the left side (the primary forms) execute first, and then forms on the right side (the secondary forms) execute. For more information, see Filter processing.

Transaction control in database operations

All of the operations performed on join forms, such as querying, displaying, and workflow operations, are executed as a single database transaction. For example, if you update data in one form that affects the data in multiple forms, all of the changes are performed as a single transaction in the database. This process ensures the integrity of the information. 

The completion of the database transaction for a hierarchy of forms is all or none. For example, in Joining several forms, if a filter returns an error on form I, none of the updates for the other forms is written to the database.