Sage 100

How would one write a Crystal Report to show you only those customers who do NOT have a tax exempt number? Looks like no record is put in the AR_CustomerShiptoTaxExemption file unless there is an exemption. I struggle with these kinds of ""proving a negative"" reports.

Would the IsNull work for you?

What if you do a Not-Equal Join/Link between the Customer file and the CustomerShiptoTaxExemption file? Does that provide a listing of the Customers who do not have a record in the Exemption file?

Isnull doesn't appear to work because the entire record isn't even there. I tried the Not-Equal and it brought up everyone over and over, but maybe I need to do some sort of refining to that. But not sure what because if you pick a record in the Exempt file, all but 1 record in the customer file WILL be NOT EQUAL. Hmph.

Here's what I'd try. I'd make a left outer join to the exemption file, Group by customer, create a record count on a field in the exeption table, then do a conditional supression on the group or record based on whether the exeption field count is not equal to zero. You should end up with only customers with no exeption records.

I setup a new customer with no tax exemption number. When I do a left outer, they don't show up at all.

Beth, you need to use the Crystal Function IsNull in your selection criteria. Specifically: IsNull({AR_CustomerShipToTaxExemptions.TaxExemptionNo}). I threw a quick report together using data from ABC that I hope helps explain a little better.

BTW, I expanded upon the previous commenters suggestions. Use a Left Outer Join between AR_Customer and AR_CustomerShipToTaxExemption when building the base report. I don't use the selection criteria in the expert as 'Funtion' isn't an option. However, if you use the Selection Expert in design mode, it is the last choice listed. Using this, you can define the custom selection criteria I showed above. I hope this helps.

Thanks, @ShawnSlavin - I had a little too much in my Select statement. I think it's working now. THANK YOU!

Oops, spoke too soon. Have some showing up that do have exemptions. Argh. Starting over with yours, Shawn, to see what happens.

Oh my bad - they had a record but still had no tax exemption number, so that will still OK. Back to thinking it's working. ... :-)

You can add an 'or' clause to your selection criteria and exclude blanks ''. However, now you get into data scrubing issues. Is this truely an exemption but we didn't record the number? Was it fat fingered? yada, yada, yada!