Concatenate multiple rows into one row, with criteria

Epicor ERP 10
1

I have created a BAQ to list multiple PO Numbers in one Row, separated by a comma as follows:

image
image.png1144×645 16.1 KB

I created this as follows:

image
image.png1246×808 65.8 KB

image
image.png1313×749 34.4 KB

image
image.png1534×857 67.6 KB

SQL Query Phrase:
select
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
(REPLACE(REPLACE(((select
(CAST(PODetail.PONUM AS VARCHAR) + ‘,’) as [Calculated_PONum]
from Erp.PODetail as PODetail
where PODetail.PartNum = OrderDtl.PartNum FOR XML PATH(’’))) , ‘</Calculated_PONum>’, ‘’),’<Calculated_PONum>’,’’)) as [Calculated_POs]
from Erp.OrderDtl as OrderDtl

The problem is, it’s showing every PO ever created based on each Part Number, but I want it to only show POs that are currently Open, not closed. In other words, where PODetail.OpenLine = 1 (true).

Does anyone have any suggestions how to filter by this criteria? Any advice you can give would be much appreciated. Let me know if you need more info.

Thanks!

2

You can add filters to the tables, look at the criteria tab. This will add a where clause to the query.

Brett

3

That’s what I was thinking originally, but when I try that I receive the following error message.
Severity: Error, Table: , Field: , RowID: , Text: Incorrect syntax near the keyword ‘FOR’.

Am I doing something wrong here?

image
image.png1437×771 35.7 KB

4

I figured it out! I had to add a SubQuery Criteria (not Table Criteria), and I had to order it before the “FOR XML PATH” expression. Now it’s working perfectly. Thanks for the help!

image
image.png1157×761 33.2 KB

1 Like
5

Ed,
I am trying to do something similar - any chance you can send me or post your working BAQ so I can see all the details on how you built this?

Russell

6

Check out this thread. @josecgomez posted and example BAQ in there that you can look at.

1 Like