ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> Records selection
http://www.istechforum.com/YaBB.pl?num=1118953262

Message started by deburr on 06/16/05 at 12:21:02

Title: Records selection
Post by deburr on 06/16/05 at 12:21:02

I want to write a report that will search BKAR_INVL_PDESC in BKARHINVL for all records related to that product code. I want all lines for that item on the invoice to show. The problem I have is the pcode only exists in the first line of the group of records I want to select. How would I select the remaining lines for that item.

Thanks,

Steve

Title: Re: Records selection
Post by aricon on 06/16/05 at 12:34:11

You need to create the table twice with the second instance being aliased and then link the tables on a common field. In this case I think it would be on Invoice or SO number and then use the part number field as your selection criteria.

Title: Re: Records selection
Post by Lynn Pantic on 06/16/05 at 15:12:48

As I understand it, you want all the descriptions of the multiple lines related to a single item line such as specs, manually entered comments, second line of item description, etc. Unfortunately, there is no common field among those records that makes them tied to the item line yet unique from other lines on the invoice.

Title: Re: Records selection
Post by aricon on 06/16/05 at 18:58:13

OK, since it has been so long since I have actually seen the tables, if that is the case then the ONLY method I could think of would be to use Access and bring the records in there and then with some VBA code behind it add the part number to each line in the Access table and then report off it.

Title: Re: Records selection
Post by Danimal on 06/17/05 at 08:46:14

With the same understanding as Lynn, it is possible to report those related lines using the following method... buckle up! ;)

Create your report using the BKARHIVL table. Create a parameter field (PartNumber) to prompt for the part number you are reporting on, or create a formula field (PartNumber) and store it there.

Create a running total field (Related) that summarizes BKAR_INVL_INVNM by type "count", evaluate using a formula as such...
Code:

{BKARHIVL_B.BKAR_INVL_PCODE} = {?PartNumber}

...and reset using a formula as such...
Code:

{BKARHIVL_B.BKAR_INVL_PCODE} <> "" and {BKARHIVL_B.BKAR_INVL_PCODE} <> {?PartNumber}

Create a formula field (Related) containing something like...
Code:

if {#Related} > 0 then "R"

Then, to have the report show only those lines that are "related," you must format your details section to suppress those lines where...
Code:

{@Related} <> "R"

...and there you go!

Field names and other parts of this solution are suggested and subject to change according to your configuration and/or style, but should give you a general idea of how to accomplish the task. HTH! 8)

Title: Re: Records selection
Post by deburr on 06/17/05 at 09:13:41

Thanks all for the help! You guys are great!

Title: Re: Records selection
Post by premiermop on 06/27/06 at 10:41:49

Hi,
I'm brand new to this forum, but I have a similar problem. Unfortunately, I'm working with old versions - DBA v 2002.4 and Crystal Reports for betrieve v 6. I've written a crystal report that lists every open order summarized by 1 row per order with total number of cases, total weight, etc. I'm trying to include all relevant "notes" from the BKARDESC_B.BK_DESC file but I want them to print in the same single row. Unfortunately, only the first note line with my criteria is being printed. Using a subreport will include all "note" fields but they list down a column and the report spacing gets crazy. I'm only interested in notes with the following criteria :
"*BALANCE*", "*DEL*", "*REQ*", "*SHIP*", "*W/C*", "*LABELS*", "*STICKERS*", "*TAGS*"]. Any suggestions?

Title: Re: Records selection
Post by dameng on 07/04/06 at 16:49:56

that would have to be in your subreport, and the record select would be to use the LIKE operand, and the fomatted words you already list with a wildcard.

i suggest formatting your subreport 1st to get the record selection down and the formatting the way you want it.

then when you link the subreport to the main report, chances are you will join those by sonum and line item.