Filter value should be specified Parameter @CustomerID and the Value column from your baq should be CustID
Thank you so very much Greg!! This is now functioning as we hoped.
Happy Monday! Starting on the struggle bus over here…
Can anyone help me identify why my calculated field for OnHandQty isn’t accurate?
Working on same BAQ from above.
Calculated field = SUM(PartBin.OnHandQty)
A qty shouldn’t be nvarchar. I’m surprised that BAQ runs at all.
export the query and send it here.
EXC_CustInvTest.baq (10.0 KB)
you have multiple orders for the same part and since your PartBin query isn’t a subquery it’s summing the current on-hands for every line that had that part.
we only have 55 on hand for this certain part so here is what it’s doing.
not grouped.
Grouped
standby for an example to fix
Add an InnerSubQuery. This is where you will get your on-hand quantity for the PartBin. (I left the original PartBin table on here for reference, but you should remove it)
Assuming you have one company…
Inside the new SubQuery you want to add Company, PartNum, and your calculated field SUM(PartBin.OnhandQty). Group By Company and PartNum.
*Edit - you also want to add Bin Num and group by that. I didn’t realize you had it in there.
slap that baby on your top level, connect it to OrderDtl the same way you have it with the original PartBin table. Add the calculated field from the subquery and bada bing bada boom.
You also may want to consider taking picked, allocated, & reserved quantities into account too.
You can do the same concept to the other calculated field (Calculated_OrderedQty).
Doing the SUM in a subquery removes the necessity to have all the field GroupBy on your top level query.
You’ll notice in my example I sent back, the new calculated field is also grouped in your top level query. This was only to make it run without changing too much.
If you move both calculated fields to subqueries, you will not need the top level to be grouped, unless that’s how you want your data to be displayed. But that’s up to you and would need to be tested to verify your results are what you’re looking for.
Be careful with your Group By LineDesc. Part number descriptions can change at the part master as well as on the SO. If you group by description you may get multiple results for the same part number.
test_epiusers.baq (40.1 KB)
Because I don’t feel like doing what I’m supposed to be doing. Here’s an example of the entire query that uses the two sub queries that I was talking about. You just need to add your parameter back in because I had to delete it since I don’t have the BAQ you used for it.
Also, the group by line description issue I was talking about…exactly that happened to me, where there is one part# with multiple descriptions, so be careful!
So here’s another copy of the query that is an example that would filter out all of the different descriptions and use the description from the most recent order.
I’m sure there is a better way to do this, but I am far from a SQL/BAQ expert but it works.
*edit: in the sub query - ‘LastOrderSub’, I have them all grouped. This is unnecessary, however it does not affect the date for this example.
Test_EpiBAQFiltered.baq (60.9 KB)
You are awesome!! Will take a look and see if any questions arise. Have an amazing day.