If you filter on a field from a table that isn't involved in a report, you may end up with duplicate records. For example, say your report displays the Company Name and Contact Name from the Customers table. If you filter on the Product Name from the Products table being "Apricot Jam" (in other words, you want a list of customers who bought that product), you'd end up with each customer showing up once for every order they placed for that product. So, if Sam's Grocery ordered it 25 times, they'd appear on the report 25 times. That isn't typically something you'd want, so Stonefield Query eliminates these duplicate records by automatically adding a DISTINCT clause to the query in that case.

However, there may be times when this isn't the correct behavior. For example, if you want to show fields from the Orders table (Order Date, Product Name, and Quantity Ordered) but filter on Company Name is "Sam's Grocery," Stonefield Query eliminates what it thinks are duplicate records, such as two orders for the same product and same quantity on the same day. Clearly, this isn't right—you'd end up with some missing data. The Add DISTINCT to SQL statement setting in the Customize dialog allows you to change that.

In certain types of applications, such as an accounting system, you may want the default behavior to not add the DISTINCT clause under these circumstances (that is, have the Add DISTINCT to SQL statement setting default to No) and allow the user to turn that setting off in the Customize dialog for a particular report. The Default for Auto-Adding DISTINCT configuration setting controls that default.