PLEASE HELP

[gard0128]

I am using Crystal 10 to create a report that gives me only the values that don't match, when linking two business views. One view has account information. The other has email information for contacts.

Not all accounts have a corresponding record in the email view, and I'd like to generate a report that shows only these accounts so that we can update the email view.

I've attempted to link the tables and utilize the select expert to pick up records only where the email field is blank in the email view. The problem is, if there is no email...there is no record in the email table - so I get no records.

I know there are a lot of guru's who are laughing at this, seemingly, simple problem. PLEASE stop laughing and HELP?? Thanks.

 

[lbass]

Use a left join FROM the account table to the e-mail table. Then use the following for your record selection formula:

isnull({e-mail.ID})

//where {e-mail.ID} is the field you are linking to (substitute your actual table/field name). Do not use any other record selection criteria on the e-mail table. Then place {account.ID} on your report. Only those with no e-mail info should appear.

-LB

 

[gard0128]

Thanks for the help, LB. The report now works.

However, I would also like to filter the report by salesperson (which is in the account table). When I attempt to run the report after filtering to one salesperson, I get the following error:

Failed to open rowset.

Is this happening as a result of the left join which links the account table to the email table? Do you know how I can accomplish filtering by a field in the account table?

Thanks, again, for your help.

 

[Turkbear]

Hi,
How are you modifying the record selection formula to select just that one salesperson..

If it is in the accounts table it should appear in the report - the Left Outer Join only affects the e-mail table.

 

[gard0128]

I am simply adding a condition to the select expert.

I am picking the Salesperson Name (from the account table) and choosing (is equal to) and picking a name from the drop list.

The formula in the select expert looks like this:

isnull({gcc_vu_EmailView.EmailId}) and
{gcc_vu_Account.SalespersonName} = "Watson, Tim"

As long as I do not use the second criteria, the report works fine. Could this be an issue with our SQL views?

Thanks, in advance, for your help.