Configuring Siebel Business Applications > About Business Components, Fields, Joins, and Links > About Joins >

How Siebel CRM Creates a Join

Figure 17 describes how Siebel CRM creates a join.

Figure 17. How Siebel CRM Creates a Join

Explanation of Callouts

Siebel CRM uses the following objects to create a join:

1. Business component. The business component is the parent of the join. Because of the join, a field in the business component that Siebel CRM joins can represent a column from the joined table.
2. Joined field. A joined field is a business component field that represents a column from a table other than the base table of the business component. For more information, see How Siebel CRM Uses a Joined Field.
3. Join. A join is a child of the business component. The join uniquely identifies a join relationship for the parent business component and provides the name of the joined table. The Table property of the join identifies the joined table. The join includes a child join constraint, which is an object that contains a constant value search specification that Siebel CRM applies to a column during a join. Siebel CRM uses it with an outer join. For more information, see Options to Filter Data That Siebel CRM Displays In an Applet.

   If Siebel CRM must get all records in the business component even if the joined fields are empty, then you must make sure the Outer Join Flag property contains a check mark.

4. Join Specification. The join specification is a child of the join. It identifies the foreign key field in the business component and the primary key column in the joined table. For more information, see How Siebel CRM Uses the Join Specification.
5. Foreign key field and foreign key column. The Source Field property of the join specification identifies the foreign key field. It represents a foreign key column that resides in the base table and it references the rows in the table that Siebel CRM uses in a join. For example, the foreign key field to the join on accounts data in the Contact business component is the Account Id field. This Account Id field references the PR_DEPT_OU_ID column in the base table.
6. Joined table. The joined table is the parent table in the parent-child relationship. It provides columns to the business component through the join. The Table property of the join identifies the joined table.
7. Primary key column. The Destination Column property of the join specification identifies the primary key column in the joined table. Each table in Siebel CRM includes a ROW_ID column that uniquely identifies the rows that the table contains. ROW_ID is the destination in most joins.
8. Mapped column. Columns in the joined table are available for use in fields in the business component.

How Siebel CRM Uses a Joined Field

A joined field gets values through a join. Siebel CRM includes the name of the join in the Join property of the field. The Join property and Column property together identify the column and how to access it. If you create a joined field in a business component, then you can modify the Type property from the default DTYPE_TEXT to a more appropriate type. For example, if you join a table column that contains phone numbers, then you can modify the Type field to DTYPE_PHONE.

How Siebel CRM Uses the Join Specification

The Source Field property of the join specification identifies the foreign key field that resides in the business component. If left empty, then the Source Field is the Id field that indicates a one-to-one relationship between the business component and the joined table. Siebel CRM sometimes defines a system field as the foreign key field in the Source Field property. The Created By and Updated By fields are examples of system fields. For more information, see System Fields of a Business Component.

The Destination Column property identifies the primary key column in the joined table. If the join occurs on a column other than ROW_ID, then the Destination Column property must not be empty. An empty value in the Destination Column property indicates that the destination column is ROW_ID that is typically the primary key in a table.

In rare situations, multiple join specifications can exist in a single join. For example, the Sub Campaign business component includes a join to the S_LANG table with two join specifications. In this situation, the source fields in the join specifications must reference the same table. For more information, see Join Specification.

How Siebel CRM Filters Duplicate Records From a Join In an Applet

A join between two business components can return one or more records. For example, if the joined table is an intersection table. In the applet, Siebel CRM displays only the first record in the result set. An applet that references a business component cannot display duplicate records from the base table of the business component.

For example, a many-to-many relationship exists between the Service Request and Organization business components. The link between these business components is Service Request/Organization. This link uses the S_SRV_REQ_BU table as the intersection table. In the Service Request business component, you can add a join to the S_SRV_REQ_BU table and a related joined field. If the user queries the business component to get a service request, and then the SELECT statement gets all the organizations that Siebel CRM associates with the service request. Siebel CRM displays only one service request record in the Siebel client. To view all the organizations that Siebel CRM associates with the service request, the user can open the multi-value group applet that references the Organization business component.

For more information, see Guidelines for Naming an Object.