Combining parts of two records into one detail line

[Snakeroot]

CR XI (11.5.11.1470)
SQL Server 2008

I'm having a problem getting the exact result I'm looking for and could use some help.

I have 5 fields:
CSize, Serial, WO, RefT, Lbs

I have two types of records that are being displayed currently:
Cyl record and a Ref Record

This is the current output:
[tt]CSize Serial WO RefT Lbs[/tt]
[tt]3/5 100001 238T 1[/tt]
[tt]R-99 238T R-99 Desc 16[/tt]

I would like it to look like this:
[tt]CSize Serial WO RefT Lbs[/tt]
[tt]3/5 100001 238T R-99 Desc 16[/tt]

I was able to get the empty RefT field to display the value of the next record by using this formula and putting it in the RefT column in the details section:

 if next({Command.ItemRef_FullName}) LIKE 'Dynam%' then next({Command.CSize})

That gives me this output:
[tt]CSize Serial WO RefT Lbs[/tt]
[tt]3/5 100001 238T R-99 Desc 1[/tt]
[tt]R-99 238T 16[/tt]

I could do something similar for the Lbs column, but I'm stuck on how I would then get rid of the 2nd line displaying in my report. This one:

[tt]R-99 238T 16[/tt]

Can anyone help me out on this one? Thanks!

 

[lbass]

What fields are you grouping on? Are there more than these two records per group?

-LB

 

[Snakeroot]

Grouping on a particular template, then on customer name, then on date of transaction, then on wo#

On this particular template type, there are only the two types of records that I indicated earlier.

 

[lbass]

Since WO# is your innermost group, I think you could just use a detail suppression formula like this:

{table.WO#} = previous({table.WO#})

-LB

 

[Snakeroot]

Without trying it, wouldn't that give me every other work order rather than every other record in one work order?

 

[lbass]

No, it would suppress all but the first record associated with a particular wo#.

-LB

 

[fisheromacse]

maybe, along with lbass' suppression of all but the 1st dteail, a formula like below for each 'next' detail value you need to show:

{@NextLB}
IF {Table.WO#}=NEXT({Table.WO#})
THEN NEXT({Table.Lbs}) else {Table.Lbs}

 

[Snakeroot]

Sorry, I told you the wrong thing. The last group is actually by txnLineID, not WO#.

This is a new process and I can tweak the input of the data. I'm wondering if it would just make more sense to include the Serial on both entries during the data entry, then do a join in SQL using the matching serial# before the data is brought into Crystal?

 

[lbass]

That sounds like a good idea.

-LB

 

[fisheromacse]

agreed. if you can create a view in the database that contains your information as a single record that would be the best for data integrity as well as report performance.