Solved

Subquery Totaling PartBin Quantities

  • 2 November 2021
  • 9 replies
  • 558 views
T
Userlevel 3

Kinetic 2021.2.3 SaaS

For a part I am trying to have a Subquery return the Sum of Quantity Onhand for whatever bins the part is in.  The subquery is returning a row for each bin and the Sum being calculated shows just that bin quantity.   I think I have something backwards because I am sure this sort of thing is done routinely.  I am getting:

But I need one row with TotOnHand = 32.  The subquery links to the top query by PartBin.Company and PartBin.PartNum.  The aggregate calc in the top level SubQuery1 is
Sum(PartOnHand.PartBin_OnhandQty).

Any hints on getting the correct Sum and one row per part?

Thanks,
-Todd C.

icon

Best answer by mikeb 4 November 2021, 16:16

View original
Todd Caughey Epicor Contract Consultant, Randstad USA, Plymouth, MN

9 replies

J
Userlevel 2

Try marking the company and part number as “GroupBy”

 

Joe

D
Userlevel 2

Adding to what Joe suggested, you want to check the checkboxes that say Group By in your subquery. Then on your top query you can join by company and partnum. Top level does not need to be checked group by.

T
Userlevel 3

At the moment EVERY display field (except the Calc Sum) is checked as “group by” - both Subquery1 and the lower level subqueries.  I will try unchecking the top level display fields and have only the PartBin subquery grouped by.

Todd Caughey Epicor Contract Consultant, Randstad USA, Plymouth, MN
T
Userlevel 3

When I unchecked all the display fields in the top level query I started to get the infamous:

Column 'Erp.Project.ProjectID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

As I checked each one and Analyzed again it did the same for the next field.  When I checked them all I got Syntax OK and the query runs.  The thought occurs to me that I might need some sorting, perhaps on PartNum.

Todd Caughey Epicor Contract Consultant, Randstad USA, Plymouth, MN
D
Userlevel 2

Did it output results that made sense? 

T
Userlevel 3

Yes, individually the rows are correct and seem to check out.  But they are not what I want to report at the part level.  I get varying numbers of rows depending on how many bins a part is found in versus just one row with the Sum of the quantities. 

This is just the start for what will be a very complicated query structure - the ultimate goal is to compare the required material quantities with what has been issued to a job (Project really) and determine what percentage of materials  have been purchased as a way to gauge overall jobs completion percentage for billing.  Some jobs take 18 months or more to complete so the contracts have provisions for intermediate billing.

Todd Caughey Epicor Contract Consultant, Randstad USA, Plymouth, MN
mikeb

You will need to remove the BinNum from your results/display set.  With that in there, you are grouping by the bins individually.  So each Bin with the same part number will be its own line.  If you take that out, it will be grouping by PartNum and summing up all the bins for that PartNum.

Michael Barsteika, TMF Center Inc.
T
Userlevel 3

Thanks Mike,  I stumbled onto exactly that yesterday but without realizing why it started working better.  Now I understand the reason.  I inherited the BAQ from the client contact and started with everything but the kitchen sink included.  I have been paring it back gradually to simplify the query and took out the subquery for PartBin and instead added a direct table connection with the Sum() on quantity.  I am going to do the same thing for POs and PO Suggestions.

Todd Caughey Epicor Contract Consultant, Randstad USA, Plymouth, MN
T
Userlevel 3

Thanks to all who assisted this is now in production.  Accounting (with spreadsheets) now has the info it needs to calculate percent of completion on projects.

Todd Caughey Epicor Contract Consultant, Randstad USA, Plymouth, MN

Reply


Terms & Conditions