Siebel Developer's Reference > Operators, Expressions, and Conditions > Search Syntax >

About Searching Multivalue Groups with [NOT] EXISTS

You can specify the [NOT] EXISTS operator in a QBE or Search Specification referring to a multivalue group field. A multivalue group field is the user interface mechanism for displaying the child records of a parent record within the parent record's applet. For example, assume the following:

• Opportunities are a separate entity and business component.
• Contacts are a separate entity and business component.
• Both the Opportunity and Contact business components are included in an Opportunity business object.
• There is a many-to-many relationship between opportunities and contacts (that is, opportunities can be worked by one or more contacts, but a contact can work one and only one opportunity).
• A form applet views the Opportunity business component with the following fields: Opportunity Name, Contact First Name, and Contact Last Name.
• The form applet is opportunity-focused. That is, the purpose of the form applet is to display and manage opportunity information (any contact information displayed is specific to the opportunity).

NOTE:  When using QBE on multivalue fields (MVFs), include only those MVFs that are revealed in the originating business component.

Because the form applet is opportunity-focused, the opportunity name is a standard text box control, whereas the contact's first and last names are defined as multivalue group fields. The contact's first and last names are defined using multivalue group fields instead of standard edit controls, because the only way to display multiple contacts for an opportunity in an opportunity-focused applet is through a multivalue group field.

When you enter "Wine Festival" as a search specification in the opportunity name, you are asking the Opportunity business component to return all opportunities that have a name of "Wine Festival." When you enter "Smith" as a search specification in the contact last name, however, you are asking the Opportunity business component (not the Contact business component) to return all opportunities that have contacts with a last name of "Smith."

This multivalue query transcends business components and, therefore, requires the [NOT] EXISTS keyword, as shown in the following syntax examples:

Syntax for QBE (placed directly in the last name field in the user interface):

EXISTS(Smith)

Syntax for a predefined query (Opportunity is the business component):

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

Syntax for a search specification (placed directly in the Search Specification property in the business component or applet):

EXISTS ([Last Name] = 'Smith')

Select records based on multiple child and grandchild criteria:

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

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

About Searching on Primary Fields

If you have an MVF with a primary ID field specified and the Use Primary Join attribute checked, then querying without EXISTS finds all the records where the primary record in the MVG matches that particular search spec.

If you specify EXISTS, then the result set consists of every record for which any of the records in the MVG match the search spec. If you do not specify a primary ID field for the MVG or set the Use Primary Join attribute to unchecked, then the only available query is one that uses EXISTS.

In this case, if you specify a query that does not use EXISTS, then EXISTS is automatically assumed and inserted as part of the search spec. The object manager uses an EXISTS clause that retrieves parent records where the field value for one of the child records is NULL. It does not retrieve parent records with no children. To query for parent records with no children, use NOT EXISTS(*).

The default behavior for querying on multi-value groups is that specifying a value for a MVG or MVF queries for the primary value.

For example, if you query on the Account Team with the value VSILVER (and the MVG has been configured to support a primary), then all records are returned for which VSILVER is the primary position on the team.

NOTE:  In a view with sales team visibility, do not attempt to constrain the account team by using query by example. Use a view with All visibility. For example, you are logged in as SADMIN and you are on My Accounts view. When you now create a new query where a login name is entered for Account Team (for example, VSILVER), you cannot expect to receive all accounts where SADMIN is on the team and VSILVER is the primary.