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

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(’’), TY…

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

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

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.

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.

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_Calculate…

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 i…

I called my calculated field Calculated_ECR, so it was turned into Calculated_Calculated_ECR lol. Glad it worked out for ya.

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

BAQ: Calculated Field Concatenate Rows to single field

Experts' Corner

knash (Ken Nash) August 31, 2017, 10:18pm 8

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.

Topic		Replies	Views
BAQ Concatenate Rows Columns to Field Epicor ERP 10	14	10673	April 30, 2018
Concatenate multiple rows into one row, with criteria Epicor ERP 10	5	3368	January 8, 2019
BAQ - Concatenate results from subquery into one field Epicor ERP 10 baq	7	3008	August 30, 2023
What's going on here? Epicor ERP 10 e10 , baq	10	869	October 26, 2021
Concatenate OP's in order of OP Sequence Epicor ERP 10 baq	9	898	January 30, 2024

BAQ: Calculated Field Concatenate Rows to single field

Related topics