Siebel Developer's Reference > Operators and Expressions > Operators >

Using the EXISTS Operators with Multivalue Groups

This topic describes how to use the EXISTS operator with multivalue groups.

Using the [NOT] EXISTS Operator with Multivalue Groups

To reference a multivalue group field, you can specify the [NOT] EXISTS operator in a query by example or in the Search Specification property of an object. Siebel CRM uses a multivalue group field to display the child records of a parent record in the parent record applet. For example, assume the following:

• Opportunities are a separate entity.
• Contacts are a separate entity.
• The Opportunity business object references the Opportunity and Contact business components.
• A many-to-many relationship exists between opportunities and contacts:
  • One or more contacts can modify an opportunity.
  • A contact can modify one and only one opportunity.
• A form applet displays and manages opportunity information and displays any contact information that is specific to the opportunity. This applet includes the following fields that reference the Opportunity business component:
  • Opportunity Name
  • Contact First Name
  • Contact Last Name

    This form applet displays the opportunity name in a predefined text box control. Siebel CRM defines the contact first name as a multivalue group field and the last name as a multivalue group field instead of as predefined edit controls. If you use query by example on a multivalue field (MVF), then you must include only multivalue fields that Siebel CRM displays in the originating business component.

Assume you set the search specification for the opportunity name to the following value:

Wine Festival

In this example, the Opportunity business component returns all opportunities that contain a name of Wine Festival.

Assume you set the search specification for the contact last name to the following value:

Smith

In this example, the Opportunity business component returns all opportunities that include a contact with a last name of Smith. This situation occurs because you defined the search specification on the Opportunity business component and not on the Contact business component.

Examples of Using EXISTS and NOT EXISTS

A query by example that resides in the last name field in the client must use the following format:

EXISTS(Smith)

A predefined query where the Opportunity is the business component must use the following format:

Opportunity.Search = "EXISTS ([Last Name] = ""Smith"")"

A search specification that resides in the Search Specification property of the business component or applet must use the following format:

EXISTS ([Last Name] = 'Smith')

The following example chooses records according to multiple child and grandchild criteria. It defines a more complex query and demonstrates that a query can use all the business components involved in the view without specifying the business component that contains the field:

EXISTS ([ChildField1] = 'X' AND [ChildField2] = 'Y')

EXISTS ([GrandchildField1] = 'A' AND [GrandchildField2] = 'B')

You can add NOT in front of EXISTS to query for everything other than the value that you include after EXISTS.

Using the EXISTS Operator with a Primary

If a multivalue field includes a primary ID field, and if the Use Primary Join property of the Multi Value Link that this multivalue field references contains a check mark, and if the search specification:

• Includes EXISTS. The query results include every record in the multivalue group that matches the search specification.
• Does not include EXISTS. The query results include every primary record in the multivalue group that matches the search specification.

If you do not specify a primary ID field for the multivalue group, or if the Use Primary Join property does not contain a check mark, then the query must include EXISTS.

If you specify a query that does not use EXISTS, then the object manager automatically inserts EXISTS as part of the search specification. If the field value for a child record is empty, then the object manager uses the EXISTS clause to get the parent record. It does not get any parent record that does not include a child. You can use the following code to query for parent records that do not include a child:

NOT EXISTS(*)

To query a multivalue group, Siebel CRM specifies a value for a multivalue group or multivalue field for the primary value, by default. For example, if you use the following value to query the Account Team, and if the multivalue group supports a primary, then Siebel CRM returns all records that contain VSILVER as the primary position on the team:

VSILVER

If a view includes sales team visibility, then you must not use query by example to constrain the account team. Instead, you can use a view that includes the All visibility filter. For example, assume you log in as SADMIN, navigate to the My Accounts view, and then query the Account Team with a login name, such as VSILVER. In this situation, Siebel CRM does not return all accounts where SADMIN is on the team and VSILVER is the primary.