Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Technical Questions
Message Icon Topic: Duplicate Lines in Report Post Reply Post New Topic
Author Message
Frank in VA
Groupie
Groupie
Avatar

Joined: 15 Nov 2007
Location: United States
Online Status: Offline
Posts: 41
Quote Frank in VA Replybullet Topic: Duplicate Lines in Report
    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
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4311
Quote lockwelle Replybullet 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
IP IP Logged
Frank in VA
Groupie
Groupie
Avatar

Joined: 15 Nov 2007
Location: United States
Online Status: Offline
Posts: 41
Quote Frank in VA Replybullet 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
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1537
Quote kevlray Replybullet Posted: 10 Nov 2020 at 4:35am
Did you move the information in the details to one of the groups and suppress the details?
IP IP Logged
Frank in VA
Groupie
Groupie
Avatar

Joined: 15 Nov 2007
Location: United States
Online Status: Offline
Posts: 41
Quote Frank in VA Replybullet 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
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1537
Quote kevlray Replybullet 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.
IP IP Logged
Frank in VA
Groupie
Groupie
Avatar

Joined: 15 Nov 2007
Location: United States
Online Status: Offline
Posts: 41
Quote Frank in VA Replybullet 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.

Edited by Frank in VA - 13 Nov 2020 at 8:27am
Frank
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4311
Quote lockwelle Replybullet 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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.