BAQ equivalent for this SQL Query?

I am attempting to add some business logic to a BAQ that drives a dashboard. Below is a SQL query that I wrote as a proof of concept, but I’m new to Epicor and BAQs, so I’m not sure how to accomplish this.

In summary, the user wants to know whether non-backflushed components for a given job have all been issued complete. Assuming that I can get these counts, I intend to use a calculated field to return a 1 if the IssuedComplete count matches the other two counts and otherwise return a 0.

What I have tried so far is to add a subquery to the existing BAQ, but I keep getting a ‘Caclulated Field not Found’ error when I Analyze or get a list of the items. Long story short, I haven’t gotten very far. This is being added to an existing BAQ with lots of joins and calculated fields - so I’m hesitant to start from scratch before having strong confidence that this approach is feasible.

Thanks in advance for any insights the community can add.

(SELECT COUNT() FROM Erp.JobMtl AS c WHERE c.JobNum = a.JobNum) AS MatlCount,
) FROM Erp.JobMtl AS d WHERE d.JobNum = a.JobNum AND d.BackFlush = 0) AS BackFlushCount,
(SELECT COUNT(*) FROM Erp.JobMtl AS e WHERE e.JobNum = a.JobNum AND e.BackFlush = 0 AND e.IssuedComplete = 1) AS IssuedCompleteCount
FROM Erp.JobHead AS a
LEFT JOIN Erp.JobMtl AS b ON a.JobNum = b.JobNum
WHERE a.Company = ‘mycompany’
AND a.JobFirm = 1
AND a.JobReleased = 1
AND a.JobComplete = 0
AND a.ProdQty > a.QtyCompleted

You need to do a sub query that returns just the count in that sub query add a subwuery filter that is conditional on the top level query

Then in the calculated field in the top level query drag the entire subwuery in {subwuery1}

1 Like