BAQ: Calculated Field Concatenate Rows to single field

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

1 Like

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

2 Likes

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)’) "?

1 Like

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

1 Like

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

26 Likes

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.

3 Likes

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.

2 Likes

This fixed my issue.

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

2 Likes

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, ‘’))

vs

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

4 Likes

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.

1 Like

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

Glad it worked out for ya.

1 Like

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

1 Like

You can’t do this in 9 in a BAQ

1 Like

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

1 Like

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.

2 Likes

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

4 Likes

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:
image

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?

1 Like

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.)

4 Likes

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

Thanks Brandon

1 Like

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!

1 Like