Trouble Adding Up Fields

NateS · February 26, 2026, 8:17pm

Ive got this BAQ that estimates the cost of a single scrapped part at any given operation. The BAQ works alright. I get a few columns to add up at the end.

It should be easy to add up the values for these 4 columns, to show a single total per row. But this is not working like I expect. Some rows dont get a total at all, and some do.

The formula for the field in question is a simple sum:

SubQuery1.Calculated_NewLaborCostTotal + SubQuery1.Calculated_MaterialTotal + SubQuery1.Calculated_SubContractTotal + SubQuery1.Calculated_BurdenCostTotal

Here is my BAQ:

select  
	[SubQuery1].[JobOper_JobNum] as [JobOper_JobNum], 
	[SubQuery1].[JobHead_PartNum] as [JobHead_PartNum], 
	[SubQuery1].[JobOper_AssemblySeq] as [JobOper_AssemblySeq], 
	[SubQuery1].[JobAsmbl_RequiredQty] as [JobAsmbl_RequiredQty], 
	[SubQuery1].[JobAsmbl_PartNum] as [JobAsmbl_PartNum], 
	[SubQuery1].[JobOper_OprSeq] as [JobOper_OprSeq], 
	[SubQuery1].[JobOper_OpDesc] as [JobOper_OpDesc], 
	[SubQuery1].[JobOper_ActProdHours] as [JobOper_ActProdHours], 
	[SubQuery1].[JobOper_ActSetupHours] as [JobOper_ActSetupHours], 
	[SubQuery1].[JobOper_SubContract] as [JobOper_SubContract], 
	[SubQuery1].[JobOper_ActLabCost] as [JobOper_ActLabCost], 
	[SubQuery1].[JobHead_ProdQty] as [JobHead_ProdQty], 
	[SubQuery1].[JobOper_QtyCompleted] as [JobOper_QtyCompleted], 
	[SubQuery1].[JobOper_RunQty] as [JobOper_RunQty], 
	[SubQuery1].[JobOper_QtyPer] as [JobOper_QtyPer], 
	[SubQuery1].[Calculated_MyQty] as [Calculated_MyQty], 
	[SubQuery1].[Calculated_NewLaborCostTotal] as [Calculated_NewLaborCostTotal], 
	[SubQuery1].[Calculated_MaterialTotal] as [Calculated_MaterialTotal], 
	[SubQuery1].[Calculated_SubContractTotal] as [Calculated_SubContractTotal], 
	[SubQuery1].[Calculated_BurdenCostTotal] as [Calculated_BurdenCostTotal], 
	(SubQuery1.Calculated_NewLaborCostTotal+SubQuery1.Calculated_MaterialTotal+SubQuery1.Calculated_SubContractTotal+SubQuery1.Calculated_BurdenCostTotal) as [Calculated_SinglePartTotal] 

from  (select distinct 
	[JobOper].[JobNum] as [JobOper_JobNum], 
	[JobHead].[PartNum] as [JobHead_PartNum], 
	[JobOper].[AssemblySeq] as [JobOper_AssemblySeq], 
	[JobAsmbl].[RequiredQty] as [JobAsmbl_RequiredQty], 
	[JobAsmbl].[PartNum] as [JobAsmbl_PartNum], 
	[JobOper].[OprSeq] as [JobOper_OprSeq], 
	[JobOper].[OpDesc] as [JobOper_OpDesc], 
	[JobOper].[ActProdHours] as [JobOper_ActProdHours], 
	[JobOper].[ActSetupHours] as [JobOper_ActSetupHours], 
	[JobOper].[SubContract] as [JobOper_SubContract], 
	[JobOper].[ActLabCost] as [JobOper_ActLabCost], 
	[JobHead].[ProdQty] as [JobHead_ProdQty], 
	[JobOper].[QtyCompleted] as [JobOper_QtyCompleted], 
	[JobOper].[RunQty] as [JobOper_RunQty], 
	[JobOper].[QtyPer] as [JobOper_QtyPer], 
	(iif(JobOper.QtyCompleted=0,0,JobOper.QtyCompleted)) as [Calculated_MyQty], 
	(iif(MyQty=0,0,iif(JobOper.SubContract=0, JobOper.ActLabCost/ MyQty, 0))) as [Calculated_NewLaborCostTotal], 
	(iif(MyQty=0,0,sum(JobMtl.TotalCost) / MyQty)) as [Calculated_MaterialTotal], 
	(iif(MyQty=0,0,iif(JobOper.SubContract=1, JobOper.ActLabCost/ MyQty, 0))) as [Calculated_SubContractTotal], 
	(iif(MyQty=0,0,JobOper.ActBurCost / MyQty)) as [Calculated_BurdenCostTotal] 

from Erp.JobHead as [JobHead]
inner join Erp.JobAsmbl as [JobAsmbl] on 
	  JobHead.Company = JobAsmbl.Company
	and  JobHead.JobNum = JobAsmbl.JobNum
	and ( JobAsmbl.PartNum = @DetailPart  )
inner join Erp.JobOper as [JobOper] on 
	  JobAsmbl.Company = JobOper.Company
	and  JobAsmbl.JobNum = JobOper.JobNum
	and  JobAsmbl.AssemblySeq = JobOper.AssemblySeq
	and ( JobOper.OprSeq <= @ThruOp  )
left outer join Erp.JobMtl as [JobMtl] on 
	  JobOper.Company = JobMtl.Company
	and  JobOper.JobNum = JobMtl.JobNum
	and  JobOper.AssemblySeq = JobMtl.AssemblySeq
	and  JobOper.OprSeq = JobMtl.RelatedOperation
inner join Erp.JobOpDtl as [JobOpDtl] on 
	  JobOper.Company = JobOpDtl.Company
	and  JobOper.JobNum = JobOpDtl.JobNum
	and  JobOper.AssemblySeq = JobOpDtl.AssemblySeq
	and  JobOper.OprSeq = JobOpDtl.OprSeq
where (JobHead.JobNum = @MyJob)
group by 
	[JobOper].[JobNum], 
	[JobHead].[PartNum], 
	[JobOper].[AssemblySeq], 
	[JobAsmbl].[RequiredQty], 
	[JobAsmbl].[PartNum], 
	[JobOper].[OprSeq], 
	[JobOper].[OpDesc], 
	[JobOper].[ActProdHours], 
	[JobOper].[ActSetupHours], 
	[JobOper].[SubContract], 
	[JobOper].[ActLabCost], 
	[JobHead].[ProdQty], 
	[JobOper].[QtyCompleted], 
	[JobOper].[RunQty], 
	[JobOper].[QtyPer], 
	(iif(JobOper.QtyCompleted=0,0,JobOper.QtyCompleted)), 
	(iif(MyQty=0,0,iif(JobOper.SubContract=0, JobOper.ActLabCost/ MyQty, 0))), 
	(iif(MyQty=0,0,iif(JobOper.SubContract=1, JobOper.ActLabCost/ MyQty, 0))), 
	(iif(MyQty=0,0,JobOper.ActBurCost / MyQty)))  as [SubQuery1]

ScrapCosting.baq (28.9 KB)
Here’s the BAQ file…

In the example above the last column should be my total. You can see only one row where it calculates correctly. The others are missing data comepltely.

I even moved my total to a subquery to make sure that grouping wasn’t causing any problems. This seems like a bug to me. Anyone else found an issue like this? What can we do to fix it?

dcamlin · February 26, 2026, 9:08pm

My best guess is you have some null values floating around.

5 + null + 7 = null

MaterialTotal seems like a good place to start. It is the only value in your above image where the green row is NOT 0 and the math worked. Every other row, the displayed MaterialTotal value was 0 and the math broke down. So, perhaps the grid is displaying 0, but the values are null.

But, you may want to hit each variable with an isnull() expression… so if the value is null, it results in 0 instead of a null value.

For example:

(iif(MyQty=0,0,isnull(JobMtl.TotalCost),0) / MyQty)) as [Calculated_MaterialCost]

kve · February 26, 2026, 9:55pm

Yep, nulls in a decimal field display as 0.00 for some reason, so it’s hard to tell. I usually just wrap everything in ISNULL( field, 0 ):

ISNULL(SubQuery1.Calculated_NewLaborCostTotal,0) + 
ISNULL(SubQuery1.Calculated_MaterialTotal,0) + 
ISNULL(SubQuery1.Calculated_SubContractTotal,0) + 
ISNULL(SubQuery1.Calculated_BurdenCostTotal,0)

tpogue · February 27, 2026, 12:22pm

I reported this to Support and this was their response:

"This is a known issue that development already reported under KNTC-28275. Below is the description of KNTC-28275.
KNTC-28275 - If a field in BAQ designer contains NULL (for example, for left outer joins) grid still shows default values for it - that is Boolean field looks like False (unchecked checkbox), integer field shows 0, and decimal field shows 0.000.
This is incorrect and misleading for BAQ results, smart client showed empty cell in this case "

klincecum · February 27, 2026, 2:21pm

Well for a boolean, that would be correct. Tri-state doesn’t really make sense for one. It’s true, or it’s NOT lol.

I know nullable booleans exist, but really? That’s an edge case lol.

kananga · February 27, 2026, 4:03pm

That’s great news! It’s encouraging to see development patching the frontend domain knowledge gaps. “Frontend dev work is cheap and low skill”, is a thing too often said by PM’s committed to investing in outsized future cost and skill outlays to fix the results.

Nullable booleans are necessary and important! true or false assert a known truth about what the field represents. My boolean klincecumsSocksAreBlueToday should allow null because accessing that data requires Enterprise membership in the panopticon and I honestly just don’t see the ROI for that.

Ernie · February 27, 2026, 8:42pm

oooooo… where do I sign up?

klincecum · February 27, 2026, 9:02pm

Excited Good To See You GIF by Blown Away

NateS · March 2, 2026, 2:21pm

Thanks Everyone! I normally use isnull just to be safe. I am not sure why I blanked it out on this one!