Hi everyone, I am brand new to Epicor and the BAQ designer. I can’t figure out how to recreate this query in the BAQ designer. It seems that I can’t join the subquery on the calculated field, LastDate. [StdPartCosting] Please advise. Thanks!

Can you send a couple screen shots of your table joins in the designer? I’ll take a closer look when I get back to my PC

What are you trying to get to? This looks simple and may not require a subquery.

I’m trying to get the JobNum of the most recently-closed job for each PartNum, so I figured that I had to do a subquery with Max(ClosedDate) and join it on ClosedDate=Max(ClosedDate).

I think you’re on the right track. Keep in mind if you have multiple jobs closed on the same date, you will end up with multiple rows for a given part. We’d want to have something in the query to make sure we only get 1 (specifically the one you want).

Might want to try windows function. ROW_NUMBER() OVER(PARTITION BY jh.PartNum order by jh.ClosedDate desc ) as [Calculated_RowNum] The Calculated_RowNum is the a new ranking of rows based on the ClosedDate of the jobhead record. As Dan mentioned above, just be aware if there are multiple dates t…

If you are going to Insights this month. Check out @Banderson session on BAQs. [image] 2023 Insight Presenters- place to promote your sessions! Off Topic This is mine (if I can got off my peach and finish my first draft of my presentation) Name Customer-Led: Technique…

Join Subquery on Calculated Field

Epicor ERP 10

knash (Ken Nash) May 4, 2023, 1:31pm 7

If you are going to Insights this month. Check out @Banderson session on BAQs.

Topic		Replies	Views
E10 BAQ subquery to get last job? Epicor ERP 10 e10	19	5764	November 29, 2018
BAQ - Get recent date of part purchased and quantity Epicor ERP 10 e10 , baq	10	1507	May 2, 2024
BAQ Help - I need to show the last operation a job was clocked into BY DATE, not operation seq Epicor ERP 10 baq	19	6394	February 12, 2018
Help with BAQ Epicor ERP 10 e10 , baq	5	946	May 5, 2020
Last Quoted, Last Quote price, Last Quote number Epicor ERP 10 baq , aggregate	5	915	November 29, 2018

Join Subquery on Calculated Field

Related topics