I have been asked to create an auto-updating dashboard that we can load on a large display in our Assembly department which shows only open assembly jobs which have all materials issued complete. I have this created and working fine with one exception: it shows one line/record for each material so if one Operation has 4 materials, that Operation appears to be listed 4 times. In most scenarios this would be ok (an end user can simply use the grouping feature), but for an automated billboard display this is less than desirable because only a fixed number of lines can show on the billboard. If we have two jobs with 10 materials, the billboard will only show 2 jobs. If I build the grouping into the dashboard, then the line details are not displayed without some clicking.
Is there a way to do what I am trying to do? My understanding is that I am limited by the BAQ logic since the requirement to filter by materials issued complete requires a record for each material.
One thought that came to mind is to hire a developer to pull my BAQ via REST API and load the results into the webpage, and then have them build some logic into the webpage to only show each Operation once, regardless of how many materials that it has. I am open to any suggestions. Maybe I am missing something. Thank you in advance for your help!
If you are querying job material maybe you should make a subquery on that table and group by the company,jobnum,asm seq, and related operation. Then make two calculated fields. One will be a sum of the field IssuedComplete and the other will be a count of mtlseq.
Now if you join that subquery to your top query on joboper.company = subquery.company and joboper.jobnum = subquery.jobnum, and joboper.asmseq = subquery.asmseq and joboper.oprseq = subquery.oprseq… you will have one line.
at this point if the subquery.Calculated_SumIssued complete = Calculated_Count(MtlSeq) then you know that all of the materials have been issued complete since the sum of issued complete is the same as the count of materials.
I do not, I have done many BAQs but never a subquery. I have clicked through the subquery tabs but they don’t make any sense to me and looking at the Epicor Help on subqueries has left me even more confused. This topic is a little over my head.
Nate, thank you for that. I was getting hung up on a low-level GUI issue: how to switch to the subquery. Now that I realize I can use the left and right arrows to go between the two queries, suddenly subqueries don’t seem so complicated like you suggest.
I am thankful for people getting me this far, but so far everyone has been pointing out how to concatenate rows into a single field. I can do that, but it doesn’t solve my problem. If I have a job with four materials and three are issued complete but the fourth is not, doing a concat on the material is simply going to return a concat’d value with three of the four materials and it will show up as a result. The need here is to only show jobs where material is issued complete for all materials on the job, and this concat and subquery stuff isn’t getting me there.
You still have to filter the subquery to include the rows that you want. This would be the same thing you would do on your top level query that you were doing before, just in the subquery.
Yes I understand that but I want the rows returned if and only if ALL materials on that job are issued complete. I can filter for issued complete, but that just returns a concat’d value with three of the four materials, so does not actually filter in the way that I need it to filter. How do I return a row only if ALL materials are issued complete? That is what I am struggling with.
Do a subquery with a calculated field where a material is issued complete, then return 0 and if not, return 1. Then sum up those numbers and only include rows where the sum = 0. You wouldn’t actually need the sum_agg() function for that, just a subquery to get your list that acts as your filter.
Even easier (in my opinion) would be to create the material query as a CTE and group on job and issued complete and count where issued complete = true and count the material sequences. Then bring the CTE into the next subquery and filter where the count of issued complete = the count of material sequence.
select
[JobMtl].[JobNum] as [JobMtl_JobNum],
(sum(
case
when JobMtl.IssuedComplete = 1 then 1
else 0
end
)) as [Calculated_IssuedComplete],
(count(JobMtl.MtlSeq)) as [Calculated_MtlSeq]
from Erp.JobMtl as JobMtl
where (JobMtl.Company = 'gtr' and JobMtl.JobComplete = false)
group by [JobMtl].[JobNum]
