Hi guys, i’m hoping this is a quick one…I have a BAQ where one of the tables is PARTBIN, I have criteria applied to the table as per the screenshot below…i basically want the BAQ to display a SUM of the partbin.onhandqty of the warehouse codes applied
At the moment, i have just brought in the field into the BAQ and it brings the correct data, but multiple lines as it brings in a line for each binnum.
All, i want is one summed total (so add onhandqty for the 4 warehouse codes and return me the answer)
I tried making the PARTBIN table “issummary” but got the error “Possible erroneous query detected. One or more tables marked as summary while no aggregate functions are used in calculated fields.”…i then tried taking the field out and adding a calculated field using the below…but then the numbers it returned were nonsense…i couldnt make sense of them at all.
I think i’m doing something basic wrong, but not sure what.
Many thanks in advance.
We’re on 905702B. I find the BAQs buggy with summary statements as well as attempt to use first or last instead of each record.
For these cases or cases requiring some decent calculations I end up making a view on the SQL server and hit it with an external query to get what I need. We have a test server which allows me to develop before asking our admin to put the view on the production server.
I struggled for years with the darn Epicor BAQ designer, always annoyed to not be able to do some generally simple SQL and having timeout issues over some fairly simple queries. Discovery of ability to make views on the SQL server and hit them with External queries opened up a whole new world for us!
for each JobHead no-lock , each JobMtl no-lock outer-join where (JobHead.Company = JobMtl.Company and JobHead.JobNum = JobMtl.JobNum ) , each PODetail no-lock outer-join where (JobMtl.Company = PODetail.Company and JobMtl.PartNum = PODetail.PartNum ) and PODetail.OpenLine = true Or ISNULL(PODetail.OpenLine) , each POHeader no-lock outer-join where (PODetail.Company = POHeader.Company and PODetail.PONum = POHeader.PONUM ) , each Vendor no-lock outer-join where (POHeader.Company = Vendor.Company and POHeader.VendorNum = Vendor.VendorNum ) , each PORel no-lock , each Part no-lock , each PartBin no-lock outer-join where (Part.Company = PartBin.Company and Part.PartNum = PartBin.PartNum ) and PartBin.WarehouseCode = ‘MAIN’ Or PartBin.WarehouseCode = ‘METAL’ Or PartBin.WarehouseCode = ‘INSP’ Or PartBin.WarehouseCode = ‘FIGOO’ outer-join where (PODetail.Company = PORel.Company and PODetail.PONum = PORel.PONUM and PODetail.POLine = PORel.POLine ) outer-join where (JobMtl.Company = Part.Company and JobMtl.PartNum = Part.PartNum ) by JobHead.PartNum by JobHead.JobNum by JobMtl.PartNum .
i am looking to get the total on hand quantity from the following warehouse codes…main, insp, figoo, metal…i want it to return it the TOTAL on one line.
You could also skip using the total function.
Create left outer joins to each PartWhse
Then in a Calculated field add them up. PartWhse.OnHandQty + PartWhse1.OnHandQty etc…
Looks like that is progress DB. I am no help there. Sorry.
Just trying to help, but I am sure you have tried this already. Create a new BAQ to get the total of what you are looking for first. Then build the rest of the BAQ around it?
