BAQ: Calculated Field Concatenate Rows to single field

(Wesley Stevens) #1

Is there a function/query that will allow multiple returned rows to be combined into a single field?
I’m looking thru the PO Tables and finding all PO numbers for a specific job. The job may have 3 POs.
PO Number : Job Number
70001 : 00245
70010 : 00245
70008 : 00245
I want to combine/concatenate these to be something like
Job Number : PO Numbers
00245 : 70001,70010,70008

Concatenating operations from a Method
[SOLVED] Group multiple results into one line
(Jose C Gomez) #2

You can use one of these methods to do it I’ve done it with the XML method in E10

BAQ Concatenate Rows Columns to Field
BAQ's in E10 using multiple views (CTE's & Recursive CTE's)
(Wesley Stevens) #3

Thanks. Looking at the XML method, it shows examples of the SQL code, but I don’t have a way to manually change the SQL “Query Phrase”, I have to use the GUI "Query Builder."
Do I set the query type as a CTE type or keep as TopLevel / InnerSubQuery?
How to I get the SQL code " FOR XML PATH(’’), TYPE).value(’.’, ‘varchar(max)’) "?

(Jose C Gomez) #4

You can write that exact thing with the query builder using subqueries

(Jose C Gomez) #5

Here is an example @WesAtITC
CustOrder.baq (17.6 KB)

SQL help! Comma separated operations list by assembly using FOR XML PATH
Top 5 Products sold
Multiple lines in BAQ to consolidate into 1 line item
(Mark Bernhardt) #6

Awesome Jose this is quite a useful query. I have one question. What if you are trying to join something that has a multiple index? For example to concatenate the reference designators on a Job material, for each material one would need to link JobNum,AssemblySeq and MtlSeq.

(Ken Jarman) #7

I did something similar for Job Operations on the Subquery, using OprSeq and OpCode:

CAST(JobOper.OprSeq as varchar) + ’ ’ + CAST(JobOper.OpCode as varchar) + ’ ’

And then concatenate the field in the same way on the top level query. Yours should just take one extra level from there.

(Ken Nash) #8

This fixed my issue.

You can just keep adding rows as well in the sub query. The last row has the XML at the end.

SQL works in SQL Editor getting Possible unauthorized query In BAQ
(Ken Nash) #9

Just to make this code even better. I was able to remove the last comma (,) on the string with the following.

REVERSE(stuff(REVERSE(REPLACE(REPLACE({JobnECR} , ‘</Calculated_Calculated_ECR>’,’’),’<Calculated_Calculated_ECR>’,’’)), 1, 1, ‘’))


REPLACE(REPLACE({JobnECR} , ‘</Calculated_Calculated_ECR>’,’’),’<Calculated_Calculated_ECR>’,’’)

(Mark Bernhardt) #10

Ken, thanks for the help. I was able to narrow it down so that only reference designators related to the material line are brought in.

Getting rid of the superfluous comma also worked great. I learned that pasting straight into BAQ Designer requires replacing all of your single quotes or an error is seen. Also, I think where it says Calculated_Calculated_ECR it should read Calculated_ECR. At least I got correct results when I changed it for my query.

Again, thanks to everyone for taking the time to help with this, it’s super useful, especially with BoMs.

(Ken Nash) #11

I called my calculated field Calculated_ECR, so it was turned into Calculated_Calculated_ECR lol.

Glad it worked out for ya.

(Peter Pacholski) #12

Hi Jose, It looks like you wrote a BAQ for calculated field that would concatenate fields. I can’t open that BAQ. I am looking to concatenate reference designations into one field in BAQ. Would you be able to help? I ma running 9.06.702A progress. Thank you

(Jose C Gomez) #13

You can’t do this in 9 in a BAQ

(Peter Pacholski) #14

So I have to do it in the report? Are you able to help?

(Jose C Gomez) #15

Yes you’ll likely have to do it in the report. You can post your questions to the list @Piotr_Pacholski (new topic please) and someone generally will try to help, there are hundreds of wonderful members on this forum eager and able to help.

(Brandon Anderson) #16

I just have to tell you, I think I owe you some beer for this example. VERY useful. Thanks for posting it.

(Caleb) #17

I couldn’t figure out how to message you; so sorry for digging up an old thread…

My question is, can I use your example with the REPLACE calc field and add a sum to it? Like in your example, it would be adding up all the order numbers together…

In my example it would be adding up all OnHandQtys for the different locations.
Here’s my query results:

How can I add all the quantities together? Or should I use a different method all together? My goal with this one was to make it all one line instead of having duplicate rows for parts in different locations, and have the total onhand in the last row.

Edit: Also is it possible to remove the zeros at the end of each quantity?

(Brandon Anderson) #18

If it was me, I would do another subquery where I used the sum() function and grouped by part number, then you can bring in that subquery like a table and just display the one quantity/part number.

Or grab the OnHand quantity in the Part warehouse table that already has the on hand quantity summed up (although it’s not 100% positive that it matches bin Quantities.)

(Caleb) #19

The top part was it. As usual I was making things more difficult then they had to be.

Thanks Brandon

(Evan Dlugopolski) #20

Hey mark, Were you ever able to figure this out? I am trying to do the same…

I want to relate with multiple indexes for example part, rev, altmethod. Then show the operations for each unique combination of those three. Nothing I try is working!