I am a developer working with Crystal 11 in a slightly "indirect" sense.  Application generates an Access MDB file which the crystal report is designed around (links in database expert) for a traditional sort of crystal report with cascading detail rows, etc.

Now, we have recently introduced a new table of raw EDI data to the crystal report.  Essentially, the data is comprised of several rows per order (Group Header level of the report) which are "linked" to the order by an order number.  So we may have 20 records with the same order number, but different qualifiers to tell us what type of data the row corresponds to.  The data is all dynamic and created at the time of the report's generation.

I had a test MDB with records in this table for only a SINGLE order, which I was testing with successfully using the following SQL expression:

(
 SELECT `report_oe_pt_850_segments`.`raw_data`
 FROM   `report_oe_pt_850_segments`, `report_oe_pt_order`
 WHERE  `report_oe_pt_850_segments`.`order_num`=`report_oe_pt_order`.`order_num` AND
        `report_oe_pt_850_segments`.`segment_name`='BEG'
)

So if I have several segment rows with the same order number, but different segment names, this one would fetch the string of the field "raw data" for the row having segment name "BEG" because in my example there were only rows for a single order number.

Now, I ran our report for a range and now the table has multiple BEG rows with different order numbers.  I want the SQL expression, when used on the report or in formula, to pull the CORRECT record, SPECIFIC to the order number that the report is currently resolved to at the group level.  Unfortunately, when I try to preview the report now, I get the error: "At most one record can be returned by this subquery", I assume this is because the order_num clause is being ignored for some reason, and since there are multiple BEG rows with different order numbers, all of them get returned by the query. 

My GOAL again is to be able to access the BEG row belonging to a particular Order Number, where that "order number" is the current order number at the Group level in my report.  Just the same way that I can drag the order number field onto the report, and as long as the links are correct, crystal has selected the correct record to display the correct order number.

I know that you can't have true parameters in SQL expressions, but that's not what I thought I was doing here.  Evidently I have crossed the line as far as crystal is concerned.  The table is not linked to the rest of the report in any way other than Order Number = Group's order number, and I also need to be able to access a specific record having a particular segment_name value.

EDIT: Forgot to mention that the # of rows in the EDI table itself is dynamic.  Not for the BEG segment, but for example there is a segment with the name 'PO1' which can appear multiple times (differentiated by a line number) so I can't really have too many expectations about the contents of this table... I just need to be able to access a particular row by the current Group's order number, and an additional hardcoded qualifier.


Edited by apprise_user - 15 Mar 2013 at 7:48am

Hi,

I tried to do a subreport with the link as described linking order-number to the EDI-elements table.  However when defining the SQL expression in the subreport I ran into the same problem of "more than one record" being returned.  I removed order number from the query, thinking that maybe the only records that will be returned are the ones which correspond to the order passed in through the subreport link.  However this appears to not be the case.