If you use AND groupings when you are doing an Advanced Find query, you may have unexpected results. For example, suppose you create a query to locate all records of a certain type, such as sales orders, and you add a link to another entity. You then add two conditions for the same attribute but specify different values that the attribute can be equal to. Finally, you group the condition together with an AND grouping. The results that are returned are not expected.
In the case of sales orders you may be looking for all sales orders that include two specific products listed as sales order products.
Look for: Sales Orders
Sales Order Products
Product Description Equals Product Description 1
AND
Product Description Equals Product Description 2
This query results will include all sales orders that include Product Description 1 and all sales orders that include Product Description 2, but not be limited to just those sales orders that include both.
To create a query that returns only the sales orders that include both products, you need to build it using the sub query approach. The format of the Advanced Find query should look like the following:
Look For: Sales Orders
Sales Order Products
Product Description Equals Product Description 1
Sales Order
Sales Order Products
Product Description Equals Product Description 2
Using this approach, first the query will find all sales orders that have products with a description of Product Description 1, and then search those sales orders for Product Description equal to Product Desciption 2. Your results will be sales orders that include both products.
Related Links
Quick tip: Sort records by multiple columns
Using Advanced Find
