BAQ: Calculated Field Concatenate Rows to single field


(Mark Bernhardt) #21

I did get it working @edlugoposki and for next time I put a little guide in my cheat notes. As mentioned previously this only works in Epicor 10+
image



(Evan Dlugopolski) #22

Thank you very much! (Go Pack Go :stuck_out_tongue_winking_eye:)


(Calvin Krusen) #23

Exactly how is the

where OrderHed.CustNum = Customer.CustNum FOR XML PATH('')

added to the query phrase?

I downloaded your sample of CustOrder.baq, and see it in the Query Phrase, but don’t see what creates it.

Do I have to export my BAQ, manually edit the export, and then re-import it?

Or am I missing something simple that lets me edit directly in the Query Phrase field of the General tab?


(Jose C Gomez) #24

You have the sample BAQ there are several subqueries. Nothing creates it, you type it. its in a subquery I think


(Calvin Krusen) #25

Sorry for being so daft… But where do I type it?

I see the two sub queries and looked at the ‘SubQuery Options’ tabs, and saw nothing.

Neither of the 2 sub queries calculated fields have the “FOR XML PATH(’’)” part

SubQuery1
Calc Field ‘Orders’ = REPLACE(REPLACE({SubQuery2} , </Calculated_OrderNum>’,’’),’<Calculated_OrderNum>’,’’)

SubQuery2
Calc Field ‘OrderNum’ = CAST(OrderHed.OrderNum AS VARCHAR) + ‘,’

In fact, I don’t know where the WHERE OrderHed.CustNum = Customer.CustNum comes from as the desginer shows no links between Subquery 1 and Subquery2


(Calvin Krusen) #26

I found it.

Its in the SubQuery Criteria of SubQuery2.


(Metesh) #27

Hi,

I have a similar request like this, but is it possible to put each PO# into it’s own column instead for it being separated by a comma?


(Jose C Gomez) #28

You can’t dynamically generate columns… I mean you could but that would be brutal.


(Wangchiuwen) #29

Hi Jose, I download your your example and just change
quert1 customer–>jobhead
REPLACE(REPLACE({SubQuery2} , ‘</Calculated_Pa>’,’’),’<Calculated_Pa>’,’’)

query2 orderhed–>jobmtl
CAST(JobMtl.PartNum AS VARCHAR) + ‘,’

however I can’t get the result, may you help to check where is the problem?
I aml very apprepicate. Thank you.

2
4


(Jose C Gomez) #30

What’s the error?


(Wangchiuwen) #31

Only 19 material, but it shows over the 100 material


(Wangchiuwen) #32

the result show 13 row jobnum and each row should be only with 19 material. how ever it shows 13*19 material with each row.


(Calvin Krusen) #33

Mine has an inner sub query (1 in the pict below)for the special case
(2) You’ll need to join the tables
(3) The SubQuery Criteria might also need other lines (if your trying to select specific records for appending.

The above appends the LineNum for records with matching Product Code AND LineShipStatus_c AND KitParent Line

The result in the Dashboard is to make a field(“Product Group and Lines”) that list the lines of an order that are for the same ProdGrp and ship status (a custom field) and Parent Kit (just to filter out the kit’s components)


(Brandon Anderson) #35

Asking another question on an old thread.

I want to add a table criteria in the subquery that is getting the items to concatenate. If I have no createria on the table (PartBin in this case) it works just fine, other than it has bin numbers that I don’t want. If I add a criteria, it Bombs out. Am I not allowed to add a criteria for what I want returned?

image

This is criteria I want to add.

Here is the SQL.

select 
	[Summary].[PartBin_PartNum] as [PartBin_PartNum],
	[Summary].[Part_PartDescription] as [Part_PartDescription],
	[Summary].[Calculated_Qty] as [Calculated_Qty],
	(Replace(Replace(((select 
	(CAST(PartBin1.BinNum AS VARCHAR) + ',') as [Calculated_Bins]
from Erp.PartBin as PartBin1
where (PartBin1.BinNum like '%402044'  or PartBin1.BinNum like '%402047')
 and PartBin1.PartNum = Summary.PartBin_PartNum FOR XML PATH(''))) ,'</Calculated_Bins>',''),'<Calculated_Bins>','')) as [Calculated_Bins]
from  (select 
	[PartBin].[PartNum] as [PartBin_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	(sum(PartBin.OnhandQty)) as [Calculated_Qty]
from Erp.PartBin as PartBin
inner join Erp.Part as Part on 
	PartBin.Company = Part.Company
	and PartBin.PartNum = Part.PartNum
where (PartBin.BinNum like '%402044'  or PartBin.BinNum like '%402047')
group by [PartBin].[PartNum],
	[Part].[PartDescription])  as Summary

(Jose C Gomez) #36

Try putting the critteria in the Subquery Criteria tab


(Brandon Anderson) #37

I did. still bombs out…


image

select 
	[Summary].[PartBin_PartNum] as [PartBin_PartNum],
	[Summary].[Part_PartDescription] as [Part_PartDescription],
	[Summary].[Calculated_Qty] as [Calculated_Qty],
	(Replace(Replace(((select 
	(CAST(PartBin1.BinNum AS VARCHAR) + ',') as [Calculated_Bins]
from Erp.PartBin as PartBin1
where PartBin1.PartNum = Summary.PartBin_PartNum FOR XML PATH('')  and (PartBin1.BinNum like '%402044'  or PartBin1.BinNum like '%402047' ))) ,'</Calculated_Bins>',''),'<Calculated_Bins>','')) as [Calculated_Bins]
from  (select 
	[PartBin].[PartNum] as [PartBin_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	(sum(PartBin.OnhandQty)) as [Calculated_Qty]
from Erp.PartBin as PartBin
inner join Erp.Part as Part on 
	PartBin.Company = Part.Company
	and PartBin.PartNum = Part.PartNum
group by [PartBin].[PartNum],
	[Part].[PartDescription])  as Summary

(Jose C Gomez) #38

Error?


(Jose C Gomez) #39

oh I see yeah the subquery criteria in this instance uses that XML thing… Instead you should be able to add anotehr subquery drag part bin in, filter it, then use that subquery instead of your PartBin1 as the source.


(Brandon Anderson) #40

Well, it doesn’t bomb out, but the bin locations come back null. I thought I changed what I needed to, but I must have missed something.


(Jose C Gomez) #41

ok in your subquery you were using partbin table (aliased as partBin1)

Add a new Subquery, and Drag PartBin into it and filter it. Then in the above original query delete PartBin1 and drag in your subquery then apply the same XML thing to it.