How to avoid the records from doubling?

[Former Member]

Hello,

I have a very simple scenario.

In table 1 I have records displaying account numbers and the dates in which these accounts were contacted. There are multiple records for the same account number as there must of been multiple contacts.

Acct1   7-25-2012

Acct1   7-26-2012

Acct2 .......  and so on

In table 2 I have data related to the sales lines for the whole month of July.

With my current selection the sales lines records are doubled or tripled etc. based on the number of times the same record exists in table 1.

How do I refine the selection criteria to look at the table 1 and display records from table 2 only once?

Both tables are joined by the Account# field.

Any help would be greatly appreciated!

[Former Member]

Hi Jack,

You can follow the steps provided below:

1.Open the report in Crystal Reports designer

2.Click on Report >> Section Expert. It opens the Section Expert in a new window

3. Select Details section on left hand side

4. On right hand side there is 'Suppress(no drill-down)' option on Common tab. Click on [X+2] button in front of it. Formula Editor opens

Write below formula

Previous ({database.field}) = {database.field}

5. Click on 'Save & Close' button to close the formula editor

6. Now duplicate rows are not visible in the report

You can also follow this SAP Note:

1479281 - How to remove duplicate rows from Crystal Reports?

Hope this helps!!

- Kuldeep

[Former Member]

Just adding to my above post Jack, I also found these forum posts.

http://scn.sap.com/thread/1736901

http://scn.sap.com/thread/1935668

- Kuldeep

[Former Member]

Kuldeep, which fields am I writing these formulas for?  table1.acctnmbr  and table2.acctnbr?

I've tried it but it brings the report down to only one sales line. If there are multiple sales lines per order for different products they're not visible, only one record per account shows up?

[Former Member]

Hi Jack,

As table1.acctnmbr  and table2.acctnbr are linked/joined with each other so i thing you can take any of the field.

I guess instead  of doing

Previous ({database.field}) = {database.field}

what you can do for sales with different order is

Previous ({database.field}) = {order} and Previous ({database.field}) = {Sales field}

- Kuldeep

[Former Member]

I don't get it.

[Former Member]

Jack,

This formula:

Previous ({database.field}) = {database.field}

The database field will be the field that is getting repeated like the "Date Field" is getting repeated as we can see in your example. so your formula should be like

Previous ("Date field") = {"Date field"} and Previous ({"order field"}) = {"order field"}

So this formula will ensure that the record set will be suppressed only if we have different same order number and same date.

Now let say if we have multiple orders for same date then it will be shown.

Hope now it make sense .

- Kuldeep

[Former Member]

No no, check the example below, there are multiple order lines to one order but they're getting duplicated. They're getting duplicated because the account number in table 1 (R02-169) is listed multiple times and that's what's feeding data from table 2. If I manually remove multiple entries of R02-169 from table 1, the doubling doesn't happen, but I don't want to do this, I'd like to keep all entries in table 1.

R02-169  <---- Group Heading

R02-169	R02-169	929461	07/27/2012	07/27/2012	PKX21000351	1
R02-169	R02-169	929461	07/27/2012	07/27/2012	PKX301T0373	1
R02-169	R02-169	929461	07/27/2012	07/27/2012	RGR50023020	4
R02-169	R02-169	929461	07/27/2012	07/27/2012	RGR75023020	2
R02-169	R02-169	929461	07/27/2012	07/18/2012	PKX21000351	1
R02-169	R02-169	929461	07/27/2012	07/18/2012	PKX301T0373	1
R02-169	R02-169	929461	07/27/2012	07/18/2012	RGR50023020	4
R02-169	R02-169	929461	07/27/2012	07/18/2012	RGR75023020	2

[Former Member]

Jack,

Could you attach a copy of the report along with save data. I guess I need to have a look at the report design and table joins.

- Kuldeep

[Former Member]

Can I get in touch with you privately?

[Former Member]

Jack,

I guess, I would not be able to share my email directly with you.

Request you to attach a copy of the report with this post. May be if you are unable to upload the report then I can find out a way to upload the reports for us.

- Kuldeep

[Former Member]

Please use this link instead.

https://www.dropbox.com/s/78d13ouqnlnhen7/reportissue.zip

[Former Member]

Kuldeep, any luck?

[abhilash_kumar]

Hi,

Start by creating a group on the fields. That is one way to do this.

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member]

Hi,

They're already grouped by Account number, the report displays everything as I want it but it doubles the records, only on the account numbers that exist in table 1 multiple times.

So I basically tell it to show me the sales lines for july only on records that exist in table 1.