I have part numbers that end with a

[Crystalfire]

I have part numbers that end with a Letter. Is it possible to show only the highest letter in the alphabet for each part number? EX: 123A-234A
123A-234B
123A-234C I want to show this one only
Thanks!!

 

[AndrewBaines]

Depends what you're doing with it. Easiest ways is to group by partnumber[1 to 8] then show the maximum of the partnumber.

 

[dgillz]

Create a formula field to extract all but the last letter:
left({Item},length({item}-1)

Group by this field, sort ascending, hide the details section and place the database field in the group footer. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Ngolem]

"Create a formula field to extract all but the last letter:
left({Item},length({item}-1)"

Dgillz
this won't work since there is no reference to the rest of the p/n....

I like Andrews idea better but will take is a set further

have 2 group formulas

@group1

left({table.partnumber},8) (ascending)

@group2

right({table.partnumber},1) (descending)

then suppress any section you want where the count is > 1

never tried using 2 groups this way but it might work


Jim
JimBroadbent@Hotmail.com

 

[dgillz]

Jim-

Why won't my suggestion work if he does as I say - place the database field (not the formula field) in the footer? Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Ngolem]

dgillz

Unless I am missing something here....there can be lots of partnumbers that end with the same last letter...So I don't see how grouping on the last letter will help unless it is within the another group based on the the first 8 chars of the number as I have shown...this way you separate the partnumbers from eachother
Jim
JimBroadbent@Hotmail.com

 

[miked123]

DocTec,
This sql stmt returns the distinct information you are seeking, altho in two columns.

Select left(p2.pn,8) as pn, Max(right(p2.pn,1))as rev
From fd_pn p2
Group by left(p2.pn,8)

in CR you could join them.

I tried to getit to come back together as one column, but no success. I'm posting on the NT Server forum, as one those guru's might come up with it.

MikeD Mike Davis
MSsql, VB and Crystal Reports Developer

 

[dgillz]

Jim,

I am suggesting grouping bt the formula field which returns all EXCEPT the last letter. Then when you place the database field {ItemNumber} in the group footer the last alphanumeric item will be displayed while the rest are suppressed in the details section.

This should work, or maybe I missed something. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Ngolem]

Sorry...yes, that should work Jim
JimBroadbent@Hotmail.com

 

[miked123]

Brian Perry from the SQL server forum came up with this Sql stmt to return the data you wish. Typically, I create a view to bring the info back and point crystal to the view.

Select pn+Rev as pn from
(
Select left(p2.pn,8) as pn ,Max(right(p2.pn,1))as Rev
From fd_pn p2
Group by left(p2.pn,8)
) as dt

hope it helps
MikeD Mike Davis
MSsql, VB and Crystal Reports Developer