The way to handle this situation correctly
is to check whether or not the Order table has a link for a particular record
before evaluating the Order Quantity.
The ISNULL function can be used to test the
link, but it must be used before any criteria within the Order table is
applied.
So, for our current report requirement:
5. Open the Record Selection Formula
Editor and replace the existing code with:
(
IsNull({Orders.Customer ID})
OR
{Orders.Order Amount} < 500
);6. Save the changes and refresh the
report.
The report will now display the records as
expected because if the Customer ID on the Order is null, then the report will
not even look at Quantity Amount.
This concept can be expanded for more
complex formula just by wrapping up second half of the "OR" in
brackets.
(
IsNull({Orders.Customer ID})
OR
(
{Orders.Order Amount} < 500
AND
year({Orders.Order Date}) = 2010
)
);
This will return any customer with no
Orders, or Orders under £500 placed during 2010.
