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

Searching Multi-Value Groups with [NOT] EXISTS

You can specify the [NOT] EXISTS operator in a QBE or Search Specification referring to a multi-value group field. A multi-value 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:

NOTE:  When using QBE on multivalue fields (MVF), include only those MVF that are exposed 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 multi-value group fields. Contact's first and last names are defined using multi-value 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 multi-value 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')

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 it will find every record where 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, it will automatically be assumed and inserted as part of the search spec.

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), it will return the records where VSLIVER 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.


 Siebel Developer's Reference 
 Published: 23 October 2003