How to Suppress duplicate record in different group

[ccding]

Crystal Reports XI; SQL Server Database

I want to suppress a detailed record if it falls in more than one Group. so, if a customer is in Group A (The top group) and it also falls in a following group, how can I suppress the record only in the following group.
**Suppress if duplicate** does not work with various groups.

Current Output:
Group A
Customer1
Customer2
Customer7

Group B
Customer2
Customer4
Customer5
Customer9

Expected Output:
Group A
Customer1
Customer2
Customer7

Group B
Customer4
Customer5
Customer9

thanks for your help !

 

[lbass]

Let's assume your customer ID is a three-digit number. Create this formula and place it in the detail section:

whileprintingrecords;
stringvar x;
stringvar y;
if instr(y,x)= 0 then
y := y + x;
x := totext({table.custID},"000");

Then in the section expert->details->suppress->x+2 enter:

whileprintingrecords;
stringvar x;
stringvar y;
instr(y,x) <> 0

-LB

 

[ccding]

Thanks LB !!!
Question... The Unique CustomerId is actually about 12 characters long (mixed with letters and numbers.)

Does this change the above formula?

Thank you.

 

[lbass]

Then it is already a string, so use the following instead of the first formula:

whileprintingrecords;
stringvar x;
stringvar y;
if instr(y,x)= 0 then
y := y + x + ",";//probably should have had the comma in there in the first place
x := {table.custID};

-LB

 

[ccding]

Thanks LB !!
That worked perfectly... No more duplicates.
Thank you Again !!
c

 

[ccding]

Hello, problem with this formula..
The formula above worked very well until it has reached page 30 (out / 56 pages).
Then I received an error of a character limit exceeding 65 thousand...

It would only generate data up to page 30 (when it reached the limit.)

Thanks again.
c

 

[lbass]

I responded initially based on your request, but this is really a solution meant only for a limited set of records. What determines which group is the correct group for a given customer? I think you should use a command to return only one record per customer. Then you can group without worrying about suppression. What is the current group field? Please answer these two questions.

-LB

 

[ccding]

Thanks LBass !!This may be difficult for me to explain in a forum.

First Question: There are 2 main groups. Each group is correct. I have a GroupA and if the Customers fall in GroupA..Sort Ascending.
Then there is GroupB - Customers that fall in GroupB.. Sort Ascending. But, if Customers in GroupA are also in GroupB, then only display that Customer in GroupA.
So the current Group field is CustomerName.

I would like to filter to not choose customer if in groupB, but then that eliminates the customer all together

extra info: There are several tables in this report, thus making command behave similarly.
--
Any ideas?

 

[lbass]

I understand what you are trying to do. I am trying to understand why. Please try again. What is the actual name of the groupfield? If a customer appears in both groups, what is the rule about which group the customer should appear in? It can't just be based on order, since order can change. For example, do you want the most recent date per customer?

-LB

 

[ccding]

Actual name for the first Group Field is PriorityLevel.(PrioLVL1, PrioLVL2, PrioLVL3, PrioLVLOther) Then Grouped by CustomerID.

Rule of which group the customer appears in :
(If customer is in a priority level (1, 2, or 3) then "PrioLVL1" or "PrioLVL2", or "PrioLVL3", else "Other"

 

[lbass]

So you want the customer to appear in the highest priority level if in more than one? Is the priority level field a number field? Use a command as your datasource, something like:

select min(table.`prioritylevel`) as prioritylevel, table.`customerID`
from table
group by table.`customer ID`

Adjust the punctuation as necessary for your datasource. Then you should be able to group on {command.prioritylevel} and return only one record per customer.

-LB

 

[ccding]

Thanks LB. The priority Level field is a VarChar field type.

 

[lbass]

It should still work, as long as the field results are like "1","2","3".

-LB