Select records based on record condition 1

[YANKRAY]

Using CR 10.0.

I have a list of part numbers. The parts are assigned to different projects, meaning the same part number can have more than one project.

I want to select part number from a particular project and only show the part number if it is also listed with a different project (the project could be a NULL value).

For example, if I pull all the part numbers, my result would look like this.

Part number Project Qty
0001 999 10
0001 100 15
0001 20
0002 999 11
0002 300 12
0003 999 16
0004 999 15

I would like to select all the part numbers in project 999 only if they appear in other projects as well, so my result would look like this;

Part number Project Qty
0001 999 10
0001 100 15
0001 20
0002 999 11
0002 300 12

Is there a way to select this way?

Thanks

 

[lbass]

Insert a group on {table.partno} and then go to report->edit selection formula->GROUP and enter:

distinctcount({table.project},{table.partno}) >= 2

You can suppress the part number group header and footer if you wish.

-LB

 

[YANKRAY]

Thanks LB,

How do I filter that I only want Project 999 items that meet this condition?

I put in the Group Selection you sent, but my result contains all part numbers that have more than one project.

I only want the Project 999 parts that meet this condition.

 

[lbass]

Create a formula {@999}:

if {table.project} = 999 then 1

Then change the group selection formula to:

distinctcount({table.project},{table.partno}) >= 2 and
sum({@999},{table.partno}) > 0

-LB

 

[YANKRAY]

Perfect solution LB!

Thank-you.

Ray