About quick find queries

Чланак
02/29/2024

Model-driven apps provide experiences to quickly find records using quick find search or Grid search. With these experiences, users have a single text input that can be applied to multiple columns in a single table.

Model-driven apps also provide a search box that uses the Dataverse search APIs when Dataverse search is enabled. With Dataverse search, the results can include results from multiple tables for a more relevant search capability. When Dataverse search isn't enabled, model-driven apps provide a multiple-table quick find (categorized search) experience that combines results of up to 10 quick find queries. Learn more about search options available for model-driven apps.

Note

Quick find queries might not provide usable experiences for for every situation. See Limitations

Consider using the Dataverse search APIs instead of quick find queries for the following scenarios:

What is a quick find query?

Quick find queries use the following pattern:

They include a single filter using an 'OR' filter operator.
- With FetchXml, the filter element type attribute is set to 'or'.
- With QueryExpression, the FilterExpression.FilterOperator is set to LogicalOperator.Or.
You can add more filters, but they are evaluated only after the quick find filter results are processed.
The 'OR' filter is marked as a quick find filter:
- With FetchXml, the filter element isquickfindfields attribute is set to '1'.
- With QueryExpression, the FilterExpression.IsQuickFindFilter is set to true.
The filter has more than one condition. When only one condition is evaluated, the query has better performance when it's processed as an ordinary query.
All of the conditions within the filter use the 'Like' operator:
- With FetchXml, the condition element operator attribute is set to 'like'.
- With QueryExpression, ConditionExpression.Operator uses ConditionOperator.Like.
The 'like' operator requires a search string that ends with %.

Note

The 'Like' operator is the only supported operator for quick find queries. It is the only operator necessary for the application scenarios quick find queries are designed to support. Other operators have not been tested.

Examples

How you write a quick find query depends on whether you're using QueryExpression or FetchXml.

Each of the examples in the following tabs do the same thing:

Accept a single search string parameter. This value could be a partial name, telephone number, email address, or account number.
Create a quick find query that tests the search string against the following account table columns:
- telephone2
- telephone1
- emailaddress1
- accountnumber
- name
Filters out accounts that aren't active.
Orders the results by account name.

QueryExpression
FetchXml

You can use QueryExpression with the Dataverse SDK for .NET.

/// <summary>
/// Returns active accounts using quick find filter
/// </summary>
/// <param name="service">The authenticated IOrganizationService instance to use.</param>
/// <param name="searchString">The string to search for</param>
/// <returns>Collection of matching account records</returns>
static EntityCollection QuickFindActiveAccountsQueryExpression(
    IOrganizationService service, 
    string searchString)
{
    // Wildcard required for ConditionOperator.Like
    if (!searchString.EndsWith('%'))
    {
        searchString += '%';
    }

    QueryExpression query = new("account")
    {
        ColumnSet = new("accountid",
                        "name",
                        "accountnumber",
                        "primarycontactid",
                        "address1_city",
                        "telephone1",
                        "emailaddress1"),
        Criteria = new()
        {
            Filters =
            {
                new (LogicalOperator.Or)
                {    
                    // Specify Quick find filter
                    IsQuickFindFilter = true,
                    Conditions =
                    {
                        { new (attributeName: "telephone2", 
                            ConditionOperator.Like, 
                            value: searchString) },
                        { new (attributeName: "telephone1", 
                            ConditionOperator.Like, 
                            value: searchString) },
                        { new (attributeName: "emailaddress1", 
                            ConditionOperator.Like, 
                            value: searchString) },
                        { new (attributeName: "accountnumber", 
                            ConditionOperator.Like,
                            value: searchString) },
                        { new (attributeName: "name", 
                            ConditionOperator.Like,
                            value: searchString) }
                    }
                },
                // Condition to be evaluated after Quick find filter
                new (LogicalOperator.And)
                {
                     Conditions =
                    {
                        {new(attributeName:"statecode",
                            ConditionOperator.Equal, 
                            value: 0)}
                    }
                }
            }
        },
        Orders = {
            {
                new(attributeName: "name", 
                    orderType: OrderType.Ascending)
            }
        }
    };

    return service.RetrieveMultiple(query);
}

You can use FetchXml with the Dataverse SDK for .NET or Dataverse Web API.

With the SDK for .NET, this static QuickFindActiveAccountsFetchXml method shows how to use FetchExpression with a quick find query.

/// <summary>
/// Returns active accounts using quick find filter with FetchXml
/// </summary>
/// <param name="service">The authenticated IOrganizationService instance to use.</param>
/// <param name="searchString">The string to search for</param>
/// <returns>Collection of matching account records</returns>
static EntityCollection QuickFindActiveAccountsFetchXml(IOrganizationService service, string searchString) {
    
    // Wildcard required for ConditionOperator.Like
    if (!searchString.EndsWith('%'))
    {
        searchString += '%';
    }

    string query = string.Format(@"<fetch>
           <entity name='account'>
              <attribute name='accountid' />
              <attribute name='name' />
              <attribute name='accountnumber' />
              <attribute name='primarycontactid' />
              <attribute name='address1_city' />
              <attribute name='telephone1' />
              <attribute name='emailaddress1' />
              <filter type='or'
                 isquickfindfields='1'>
                 <condition attribute='telephone2'
                    operator='like'
                    value='{0}' />
                 <condition attribute='telephone1'
                    operator='like'
                    value='{0}' />
                 <condition attribute='emailaddress1'
                    operator='like'
                    value='{0}' />
                 <condition attribute='accountnumber'
                    operator='like'
                    value='{0}' />
                 <condition attribute='name'
                    operator='like'
                    value='{0}' />
              </filter>
              <filter type='and'>
                 <condition attribute='statecode'
                    operator='eq'
                    value='0' />
              </filter>
              <order attribute='name'
              descending='false' />
           </entity>
        </fetch>", searchString);

    return service.RetrieveMultiple(new FetchExpression(query));
}

This Get-QuickFindActiveAccounts PowerShell function demonstrates how to use FetchXml with the Dataverse Web API. The $baseURI and $baseHeaders variables are scoped outside this function. Learn more about creating reusable PowerShell functions that used the Web API

function Get-QuickFindActiveAccounts {
   param (
      [Parameter(Mandatory)] 
      [String] 
      $searchString
   )
   #  Wildcard required for ConditionOperator.Like
   if (!$searchString.EndsWith('%')) {
      $searchString += '%'
   }

   $query = "<fetch>
      <entity name='account'>
         <attribute name='accountid' />
         <attribute name='name' />
         <attribute name='accountnumber' />
         <attribute name='primarycontactid' />
         <attribute name='address1_city' />
         <attribute name='telephone1' />
         <attribute name='emailaddress1' />
         <filter type='or'
            isquickfindfields='1'>
            <condition attribute='telephone2'
               operator='like'
               value='{0}' />
            <condition attribute='telephone1'
               operator='like'
               value='{0}' />
            <condition attribute='emailaddress1'
               operator='like'
               value='{0}' />
            <condition attribute='accountnumber'
               operator='like'
               value='{0}' />
            <condition attribute='name'
               operator='like'
               value='{0}' />
         </filter>
         <filter type='and'>
            <condition attribute='statecode'
               operator='eq'
               value='0' />
         </filter>
         <order attribute='name'
         descending='false' />
      </entity>
   </fetch>" -f $searchString

   $uri = $baseURI + 'accounts?fetchXml=' + [uri]::EscapeUriString($query)
   # Header for GET operations that have annotations
   $getHeaders = $baseHeaders.Clone()
   $getHeaders.Add('If-None-Match', $null)
   $getHeaders.Add('Prefer', 'odata.include-annotations="*"')
   $RetrieveMultipleRequest = @{
      Uri     = $uri
      Method  = 'Get'
      Headers = $getHeaders
   }
   Invoke-RestMethod @RetrieveMultipleRequest
}

Quick find record limits

Within the Power Platform admin center, there's a Dataverse setting called Quick Find record limits which is turned on by default. The organization table QuickFindRecordLimitEnabled column stores this setting.

Important

We strongly recommend that you leave the Quick Find record limits setting enabled. This setting protects you from system slowness and potential service disruptions when quick find queries exhaust available resources.

Because quick find queries support specific user experiences in applications, they must return results or fail quickly. The user won't wait a long time for results and these queries can use a lot of system resources because they can have conditions on multiple columns of the table.

When the Quick Find record limits setting is enabled, quick find queries return an error when the number results exceed 10,000 rows. The error returned is:

Name: QuickFindQueryRecordLimitExceeded
Code: 0x8004E024
Number: -2147164124
Message: The number of records for this search exceeds the Quick Search record limit. Please refine your query and try again.

You don't need to display this error in your application, but you should expect that it can occur. You can mitigate this by:

Limiting the number of fields searched by your query.
Include limiting conditions in your query.
Require the user to enter more characters in the search box to provide fewer total matches.

Whether the query succeeds might depend more on the number of records in the table than how the query is defined. To understand this, you need to understand how the search item limit is calculated.

How the search item limit is calculated

The search item limit is calculated using ONLY the items in the quick find filter. When processing the query, Dataverse detects whether there's a quick find filter and processes it first, even before applying security filters. If the results of the quick find filter exceed 10,000 rows, Dataverse throws the QuickFindQueryRecordLimitExceeded exception and no other filters are processed. Adding more filters to get a smaller total number of records returned won't decrease the potential of the QuickFindQueryRecordLimitExceeded exception. Someone querying a table without privileges to view all the matching records can get this error.

Bypass the quick find record limit

When the Quick Find record limits setting is enabled, and you need to test a query that exceeds the quick find limit on a temporary basis, use FetchXml to compose the query and set the filter element overridequickfindrecordlimitdisabled attribute to '1'.

Apply the quick find record limit

When the Quick Find record limits setting is disabled, and you need to test a query with the limits applied on a temporary basis, use FetchXml to compose the query and set the filter element overridequickfindrecordlimitenabled attribute to '1'.

Limitations

Quick find queries might not provide usable experiences for every situation.

Because the search item limit is calculated before applying security filters, the total number of matching records in the system can exceed the 10,000 record limit when the table contains large numbers of records, regardless of how many records the calling user has security privileges to view. Refining the query or using a more specific search criteria might not be enough to provide a usable experience for a user.

In the worst case scenario, users see a QuickFindQueryRecordLimitExceeded exception unless they enter a specific search string, which doesn't provide the expected 'quick find' experience.

Делите путем