Sorting Items in a Report

[Former Member]

I feel sure I'm missing something easy but I can't figure out how to sort and group items.

Here are the relevant columns:

InvoiceNumber

LineItemSequence (LNITMSEQ)

ComponentSequence (CMPNTSEQ)

ItemNumber (ITEMNMBR)

The ComponentSequence will be a 0 for the items being sold.

The ComponentSequnce will be a non-0 number for the components of an item. Some of the items are kits and will have components and some will not be kits and will not have components.

The LineItemSequence will be different for each group of items. Here is some sample data:

sopnumbe l Lnitmseq cmpntseq itemnmbr

STDINV2282 16384 0 WIRE100

STDINV2282 32768 0 HDWR-DCD-0001

STDINV2282 32768 16384 ITCT-CIR-CD85

STDINV2282 32768 32768 RESR-TRR-68KM

STDINV2282 32768 49152 RMTL-CAP-10MF

STDINV2282 32768 65536 TRAN-STR-N394

STDINV2282 49152 0 PHSY-DEL-0001

STDINV2282 49152 16384 PHON-GTE-3458

STDINV2282 49152 32768 ACCS-HDS-2EAR

STDINV2282 49152 49152 PHON-BUS-1250

STDINV2282 49152 65536 WIRE-MCD-0001

STDINV2282 49152 81920 SPLN-TWO-0001

In the above example, the first item is not a kit because it has no components for the same LNITMSEQ column.

The two kits in the above example are HDWR-DCD-0001 and PHSY-DEL-0001.

The part I'm struggling with is I need to sort the items with a cmpntseq value of 0 in alphabetical order by itemnmbr. Thus, the report would be sorted as HDWR-DCD-0001, PHSY-DEL-0001, WIRE100.

For the two items defined as kits, their components would be listed after they are printed. Thus, the report's data would be sorted as follows:

HDWR-DCD-0001

*ITCT-CIR-CD85

*RESR-TRR-68KM

*RMTL-CAP-10MF

*TRAN-STR-N394

PHSY-DEL-0001

*PHON-GTE-3458

*ACCS-HDS-2EAR

*PHON-BUS-1250

*WIRE-MCD-0001

*SPLN-TWO-0001

WIRE100

The only thing I can think of is to use a subreport. If that's the best way, then that's fine. Otherwise, if there's a way to group the data without a subreport, that'll work, too.

Thank you

[Former Member · Accepted solution]

Sub Report would work okay.

Might be quicker to create a SQL View of the Kit Records and another view for the Component records and then join them together for the report so that you get a combined line for each match. Make sure it is a left outer join from the kit records to the components so you return the kit record with no components. You should then be able to sort this ok in crystal.

If you are clever with SQL or SQL commands you could probably do this in one view or command by creating sub queries for the kit and component records.

Hope that helps.

Richard