Eliminate Duplicate Records

[donnarenia]

This one may be a bit hard to explain.

I created a commission report that shows the Salesperson 1 and the following information: Invoice Date/Aged Months/Client Name/Invoice #/Invoice Amount/Commission Rate/ Commission Amount

This report works fine. Now I have added a Salesperson 2 and want the same information EXCEPT a different commision rate.

Have duplicated my formulas so I can get the information I want however i am getting duplicate data when I run my report. I added the Salesperson 2 information in to details b and I can see the one instance where Salesperson 2 is correct but I don't want to see it repeated, ONLY Unless there is a Salesperson 2.

My question is how can i get Salesperson 2 information to show only if a second salesperson is entered?

 

[iwm]

Perhaps using the Suppress If Duplicated options from the Format Editor will solve this problem

 

[donnarenia]

I tried to supress but did not get the resuls needed.

I have a commission report built and it works great, but now I have discovered that sometimes the Salespeople share deals and they need to be split between them. Each salesperson has a custom field {CLNTCUS.~Custom3}
and {CLNTCUS.~Custom21}

If only one person has the deal, the commission amount is located in {CLNTCUS.~Custom3})
if there is a second person, the commission amount is in field {CLNTCUS.~Custom22})

On the original report I used the following selection formula:

{CLNTCUS.~Custom3} = {?Employee} and
{@DatesRemaining} < 13.00 and
{AR.InvoiceDate} = {?Invoice Date} and
{AR.Type} = "I"

Which gives me the results I need. How do I add the second salesperson {CLNTCUS.~Custom21} without getting duplicate records, I only want to Salesperson 2 if they were part of the sale.

 

[lbass]

Please show what your data would like at the detail level if there is a shared commission. Please label the fields and show several rows.

-LB

 

[donnarenia]

Actually I mirrored my original detail

Salesperson 1 Details (located in Da)
[Acquited date] [Invoice Date] [Remaining # Day][Client Name] [invoice #] [final bill] [commission amt] [@commission]

Salesperson 2 Details (located in Db)
[Acquited date] [Invoice Date] [Remaining # Day][Client Name] [invoice #] [final bill] [commission amt2] [@commission2]

Result is bascially giving me a record for everyone twice
I attached my report to show you my results there is one highlighted reference that shows that Salesperson 2 with a different Commission amount and rate. The information is blue is showing although there is no commission rate and it is associated with a House account that is default for the field in the database.

 

[lbass]

Where is the ID for the second salesperson? In your report, it looks like you are grouping on salesperson, but I don't see anything that indicates who the second salesperson is.

What fields are you placing in detail a vs detail b for the commission? Are these two different fields?

You reference three custom fields, 3,21,and 22. Please identify them in some sample data that also shows what you would expect to see if there is shared data. Just type it into the thread, please--both labels of actual fields and the corresponding data in this case.

-LB

 

[donnarenia]

The ID for the second Salesperson is
{CLNTCUS.~Custom21} DJM
I put that ID in the Second group heading...just so I can see when it pick up...

The fields that I am placing in detail a is {CLNTCUS.~Custom21} .35 (Salesperson 1 commision rate)
{@commision} $52.50 (salesperson 1 formula)

In detail b,
{CLNTCUS.~Custom22} .15 (Salesperson 2 Commission Rate)
{@commission2} $82.50 (Salesperson 2 formula)

There is only one instance of salesperson 2 that is correct and that is the last line that shows:

12/3/2010 (Acquired Date)
1/15/2011 (Invoice Date)
00119963 (Invoice #)
$550.00 (Invoice Amt)
0.15 (Commission Rate) {CLNTCUS.~Custom22}
$82.50 (Commission Rate) {@commission}

All the duplicate records do not need to be shown because Salesperson 1 was the only on who sold, and the {CLNTCUS.~Custom21}is defaulted to "house account" that has a null value for {CLNTCUS.~Custom22}

I am sorry if I am making this too difficult.

 

[lbass]

Yes, you didn't really provide what I asked, but:

if {CLNTCUS.~Custom22} is null when there is no second salesperson, you can go into the section expert->detail_b->suppress->x+2 and enter:

isnull({CLNTCUS.~Custom22})

-LB