Duplicate Lines in Report
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22907
Printed Date: 04 May 2024 at 9:03pm
Topic: Duplicate Lines in Report
Posted By: Frank in VA
Subject: Duplicate Lines in Report
Date Posted: 14 Oct 2020 at 4:42am
Hi All,
I needed to modify an existing report by adding an indicator field and now I am receiving duplicate lines per customer.
I added a new table, linked it by customer number. I created a formula field to check the new table to see if a customer has an active policy. If so, I place a 'Y' in this new indicator field. The new table has multiple records per customer. Here's my formula:
If {INSURANCE_SYSTEM} = 'COVERAGE' and
{POLICY_STATUS} = 'ACTIVE' Then
'Y'
Even though the formula is working, displaying the 'Y' when a policy is active, my problem is that if a customer has 5 records in this new table, I am printing 5 lines for the same customer. I need to print the customer only once, and populate the indicator field if there is an active policy in the new table within those 5 records.
Thanks in advance for any help you can provide!
Frank
-------------
Frank
|
Replies:
Posted By: lockwelle
Date Posted: 20 Oct 2020 at 5:36am
It seems to me that the join is creating the duplicates.
The simplest solution, is to create a group on the customer field, and place everything from the detail line into the group header, then suppress this new group's detail and footer...poof, only 1 line per customer.
HTH
|
Posted By: Frank in VA
Date Posted: 09 Nov 2020 at 9:02am
Hi HTH,
Thanks for your reply! Sorry I haven't replied sooner, always have new tasks popping up. This report already has three groups, one for county and two more for sort options. I created a new group for the customer number but that didn't work, no matter what group level I placed it at. I'm currently working with the link options to see if that does anything.
Thanks.
Frank
-------------
Frank
|
Posted By: kevlray
Date Posted: 10 Nov 2020 at 4:35am
|
Did you move the information in the details to one of the groups and suppress the details?
|
Posted By: Frank in VA
Date Posted: 12 Nov 2020 at 9:44am
Thanks for mentioning that, I missed that part. It did work when I moved the details up. However, my formula field where I'm trying to catch if a policy for the customer is active, only gets set if the last customer record is active. How can I display that value (flag) if say the second of four records is the active one?
Thanks!
Frank
-------------
Frank
|
Posted By: kevlray
Date Posted: 13 Nov 2020 at 5:42am
Well that can be a bit tricky. I am assuming that there is a date field that you can sort on. And if the data is sorted and you have your 'details' in the Group Footer, then the formula should trigger on the last customer record.
|
Posted By: Frank in VA
Date Posted: 13 Nov 2020 at 8:23am
What I am trying now is a running total field to count the status code, resetting it's value on a customer number change. I'm hoping it'll have a value greater than zero if it finds an active policy, and then using the formula field to display a "Y" on the report. But for some reason, it's not working for some customers. I found a few customers that have an active policy but the count is showing zero.
-------------
Frank
|
Posted By: lockwelle
Date Posted: 13 Nov 2020 at 10:17am
you could try a formula...it is the same as a running total, but you control everything.
They come in sets of 3 formulas...reset, increment, display.
Reset would go in your group header
shared booleanvar hasPolicy := 0;
"" //hides the reset
the increment would go in your details section
shared booleanvar hasPolicy;
if {table.field} = valueYouWant then hasPolicy := true;
"" //again to hide the value
finally, the display, in your group footer:
shared booleanvar hasPolicy
A running total does similar logic, this give you more control and the ability to see if it is working as desired (just remove the "" at the end of the formula and you will see the values being set)
HTH
|
|