BAQ calculated field help (how to sum)

ok, it works where there are no gaps

It’s not handling the NULL’s.
Create a calculated field for each PartWhse.OnHandQty then add them across in a calculated field.
You will also get 0.00’s to display across.
IF PartWhse1.OnhandQty > 0 then PartWhse1.OnhandQty else 0

Patrick Winter

Done!!!

Have you tried to SUM (IsNull(calculated.partbin,0))

I had a similar issue and this is how I resolved it.

I actually took the part class id and did a sum field based on a criteria.

Sum (IsNull(Case when (Part.ClassID IN ‘PP01’,‘PP02’) materialmtlcost else 0 end),0))

Does that help?

The Part Bin does not look like a summary table in phrase builder - first pic. They usually change color - a darker shade of green with a summation sign. Hope you can see the screenprint, the PartWhse is being summarizied.

I’m also on 9.05.701. I’ve received that error message in BAQs when I check syntax, but the BAQ often executes without errors. I pretty much ignore the “tables marked as summary while no aggregate functions are used” error, it’s seems it’s usually a buggy error, not a real one!

Sue

Hi Mark, I am trying to get this data summarized as well like Mark. I used an inner subquery but still seeing duplicates because of the partwhse table.
I am trying to sum up all the qty’s on the partwhse table so i can see it in one line.
I know I am missing something and I’d appreciate any help!BAQ

You have two links to SubQuery2 from JobMtl, you will need to delete one of them.

That worked. I did have to change the query a bit too. Thank you very much!