Join forms

You can create a join form to combine information from multiple BMC Remedy 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 BMC Remedy AR System
 (Click the image to expand it.)



For information about creating and using join forms, see the following sections.

Understanding join forms

Join forms are composite forms that consist of fields derived from other existing forms. A join form can be useful in the following situations:

• When you need to produce reports from data that exists in more than one form.
• When data is stored in multiple forms and you want to display the data in a single form.
• To eliminate the need to enter the same data into multiple forms.

This section helps you understand more about join forms.

A join form in Remedy AR System is similar to joining tables in a relational database. A join form uses searches to combine fields from two forms based on join criteria (see the Join criteria section below). The data in a join form comes from the database tables of the forms that make up the join form.

When you create a Join form you can define fields from both forms that are being joined. You cannot use keywords in a Join form condition. You can use fields or static values to define Join form conditions.

After the join form is created, it behaves similarly to non-join forms. Users can submit data for creation or modification, report from it, select entries from it, use it in workflow requests, define workflow on it, and so on. From the user's perspective, there is no difference between join and non-join forms.

You can use a join form as a member of another join form. For more information, see Joining three or more forms.

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) and the execution order in workflow (see Filters and join forms).

 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 BMC Remedy AR System
 (Click the image to expand it.)

 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.

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.

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
 (Click the image to expand it.)



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
 (Click the image to expand it.)



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
 (Click the image to expand it.)



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.

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
 (Click the image to expand it.)

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
 (Click the image to expand it.)



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. BMC Remedy 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.

Limitations of creating a join form using a vendor form

This section explains 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 BMC Remedy 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.