Hey all,
Man I just cannot get this new BAQ in E10 down. Just struggling my you know what off. This sounds sooo simple but yet I cannot get it to work. They want a dashboard filtered by job showing all purchased materials and the last Unit Cost and PO Date.
I created a BAQ with JobMtl in the top query and it’s criteria.
A SubQuery with PODetail and POHeader. Related the subquery to the top by PartNum. Set the subquery to Top and then TopDistinct… Tons of repeats. Played with GroupBy which makes no sense to me as to do I check it in the Top query or the subquery…
Obviously I’m frustrated and am looking for BAQ courses specific to subqueries.
Any help would be appreciated.
Hey Chris, sounds like you’re on the right track. You’ll want to use group by when you have an aggregate calculated field (sum, min, max, count, etc.).
I would start with your bottom level subquery on the POHeader/Dtl tables (I usually change my subqueries to Top so I can test the results, then change back to innersubquery after I’ve verified they work as expected), and just pull in company and partnum (your group by fields). Then I would do an calculated field for MAX(PONum) to find the last PO’s for each part.
Then you can make another subquery, pull in your original subquery (all the last PONums for each part), and rejoin them to the POHeader/Details table (so you can get all the info related to each PO unit cost date etc)
Then you can finally join your second subquery to your top level job mtl query, and that should get you what you’re looking for.
Thank you for your help. I am trying to work through it here.
So I have subquery containing POHeader with display fields Company and calculated field (max(POHeader.PONum)
I then have another subquery containing PODetail displaying Company, PartNum, PONUM and UnitCost.
I pull the PODetail subquery into the POHeader query and relate on Company and PONum.
In my Top query, I pull in the PODetail subquery and relate to Company and PartNum.
I set the POHeader subquery to Top.
Is this correct? My results show all PO’s that the job materials (PartNums) were purchased on.
In which queries do I check group by on?
Thank you thank you. I was hoping someone would answer and try to help me. I tink once I’ve done it once successfully, I will be well on my way here.
Chris
Definitely headed in the right direction. The first time is always the hardest ;). You’ll only need to check group by in the subquery with the max calculated field (not the calculated field itself though). This will tell the query what to group the maxes by. Since you’re trying to find the latest (aka max) PO for each part (so you can link it later to the jobmtl, and get the unit price/date from it), you’ll need to include the part number in your first subquery. So you’ll want to include PODtl for that. Then that subquery will give you a list of part numbers, and their respective latest (max) POnumber. Switching subqueries to the Top level is super helpful for me to visualize each part in a baq.
What do you mean check the Group By in the sub query but not the calculated field itself?
I also need PODate from POHeader. Would I do this by pulling in another POHeader table or in the subquery itself?
2 subqueries right now total right? PODetail and POHeader? Do I pull them both into the Top query or POHeader into PODetail then PODetail into the Top query with JobMtl?
Results (when switch to Top level query from inner subquery, switch back after)
You start with the PODtl, since that table holds the PartNum (which you need to group by so you can link it to its respective PO). I included the Company column for faster join time later on in the query.
Now that you have the PONum and the part it’s tied to, you can re-join this subquery to the POHeader and PODetail. Since there could be multiple PO Lines with the same part (multiple rows on the PODtl table per part), you’ll want to select distinct in your next subquerey (assuming the line prices are the same)
This will eventually be your second inner sub query, but I set to Top to verify results (then change back to inner subquery). You’ll want to account for the potential multiple PO lines related to the part (for example your last PO for a part actually has that part on 2 different lines, also assume the price would be the same) using the distinct setting on this second subqquery.
Results for second subquery:
Then you can finally join your second subquery in your top level query with the job mtl table!
