BAQ calculated field help (how to sum)

Epicor 9.05.701

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.

I just tried writing the below syntax but got an error!

this didn’t work either…

Mark,

Do you have Subqueries available to you in your version of Epicor?

Mark W.

Hi Mark Wonsil,

No i dont believe we do.

Hi Mark,

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!

Nancy

That’s unfortunate as subqueries make this problem dead simple to do… I think Nancy’s solution is the way to go.

Mark W.

This is crazy, what i’m asking for is so simple, i can’t believe a BAQ can’t achieve it

Have you tried a case instead of if?
sum(CASE When partbin.warehousecode in (‘Main’, ‘Metal’, ‘Figoo’, ‘insp’) then Partbin.onhandqty else 0 end)

Hi Knash,
it returns a syntax problem

Can you close the calculated field box. Goto the General Tab. Copy and past the query phase here. That will help.

What is your DB? SQL

hi, we are using epicor 9.05.701

i am unable to paste your query into the Query Phrase area as it is greyed out.

Mark, I meant to copy and paste the Query Phase into your message here.

So we can help debug the BAQ.

Are you looking for the total in each bin in each warehouse or the total for that part in each warehouse? PartWhse.OnHandQty would be another route.

Patrick Winter

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…

Mark,

Thanks,

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?

I’ve gone with Patrick’s suggestion…it gives me more columns - but i can live with that…it seems to work.

thanks for your help guys.

Today, has to be the worst day ever with BAQs!!!
The TotalOnHandQty field doesn’t populate any numbers in the analyze tab!