I have created a BAQ to list multiple PO Numbers in one Row, separated by a comma as follows:
I created this as follows:
SQL Query Phrase:
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
(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.