Optevia
asked on
Duplicates in Address Label Crystal Report
I have a crystal report that is using ODBC to link a number of tables.
The report itself is an 'address label' style report, so the data that gets pulled back MUST go in the details section of the report and NOT a group (othwerwise I wouldn't be asking this question) ie. The group section does not have a 'layout' tab which would enable me to replicate the address label etc
The table structure is as follows (TABLE - Columns):
CONTACT
ID, Prefix, First_Name, Last_Name
LIST
ContactId, List_Name
(There are other tables but these are not causing the dupes)
-----------------
One contact can belong to many lists. At runtime, the user is prompted to select one or more lists. If they select two or more lists that the contact is a member of, the contact is returned multiple times in the details section.
Unfortunately, Crystal places the List_Name column in the SQL Query even though it is not output on the report itself, as it is a parameter.
Is there any way of removing that column from the SQL query?
I cannot write a view or sproc to replace the report as they can select n number of lists when the report is run.
HELP!
The report itself is an 'address label' style report, so the data that gets pulled back MUST go in the details section of the report and NOT a group (othwerwise I wouldn't be asking this question) ie. The group section does not have a 'layout' tab which would enable me to replicate the address label etc
The table structure is as follows (TABLE - Columns):
CONTACT
ID, Prefix, First_Name, Last_Name
LIST
ContactId, List_Name
(There are other tables but these are not causing the dupes)
-----------------
One contact can belong to many lists. At runtime, the user is prompted to select one or more lists. If they select two or more lists that the contact is a member of, the contact is returned multiple times in the details section.
Unfortunately, Crystal places the List_Name column in the SQL Query even though it is not output on the report itself, as it is a parameter.
Is there any way of removing that column from the SQL query?
I cannot write a view or sproc to replace the report as they can select n number of lists when the report is run.
HELP!
ASKER
When I try to add your line to my RECORD SELECTION formula I get an error:
" The Function cannot be used because it must be evaluated later "
Here is the rest of my formula:
{lists.listname} = {?List} and
(if {?Party} = 88 then true
else if {?Party} = 99 and isnull({contact.affiliatio n}) then true
else false) and
{contact.gender} = {?Gender} and
({contact.id} <> previous(contact.id))
" The Function cannot be used because it must be evaluated later "
Here is the rest of my formula:
{lists.listname} = {?List} and
(if {?Party} = 88 then true
else if {?Party} = 99 and isnull({contact.affiliatio
else false) and
{contact.gender} = {?Gender} and
({contact.id} <> previous(contact.id))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks frodoman...
Glad to help - frodoman
({contact.id} = previous({contact.id}))
frodoman