Strange BAQ Calculated Field Behavior

This is a strange thing I have never come across before. In a BAQ, I am looking at the Order and Job tables, and summing the open value of sales order releases, rolled up by job number. Only one value in this data is throwing me off. Take a look at this screen recording. You can see I am stepping through the BAQ grid looking at each value. When I click on the value over 11million, the 11million disappears and I am left with just the 522k that I expect. I have no idea where the extra 11 million came from. It is not in the underlying data at all. As you can see, the value changes depending on whether I am in the field or not. I don’t even know where to start with this!

The BAQ looks like this:

 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
	[JobProd].[JobNum] as [JobProd_JobNum],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[Customer].[CustID] as [Customer_CustID],
	(sum(OrderRel.OurReqQty- OrderRel.OurJobShippedQty- OrderRel.OurStockQty)) as [Calculated_TotalOpenQty],
	(sum(SubQuery3.Calculated_OpenValue)) as [Calculated_TotalOpenValue]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
	and ( OrderDtl.OpenLine = true  )

inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.OpenRelease = true  )

inner join Erp.JobProd as JobProd on 
	OrderRel.Company = JobProd.Company
	and OrderRel.OrderNum = JobProd.OrderNum
	and OrderRel.OrderLine = JobProd.OrderLine
	and OrderRel.OrderRelNum = JobProd.OrderRelNum
inner join Erp.JobHead as JobHead on 
	JobProd.Company = JobHead.Company
	and JobProd.JobNum = JobHead.JobNum
inner join  (select 
	[OrderDtl1].[Company] as [OrderDtl1_Company],
	[OrderDtl1].[OrderNum] as [OrderDtl1_OrderNum],
	[OrderDtl1].[OrderLine] as [OrderDtl1_OrderLine],
	[OrderDtl1].[UnitPrice] as [OrderDtl1_UnitPrice],
	(sum(OrderRel1.OurReqQty- OrderRel1.OurJobShippedQty -OrderRel1.OurStockShippedQty) * OrderDtl1.UnitPrice) as [Calculated_OpenValue]
from Erp.OrderDtl as OrderDtl1
inner join Erp.OrderRel as OrderRel1 on 
	OrderDtl1.Company = OrderRel1.Company
	and OrderDtl1.OrderNum = OrderRel1.OrderNum
	and OrderDtl1.OrderLine = OrderRel1.OrderLine
	and ( OrderRel1.OpenRelease = true  )

where (OrderDtl1.OpenLine = true)
 and (OrderRel1.ReqDate >= @StartDate  and OrderRel1.ReqDate <= @EndDate)

group by [OrderDtl1].[Company],
	[OrderDtl1].[UnitPrice])  as SubQuery3 on 
	OrderDtl.Company = SubQuery3.OrderDtl1_Company
	and OrderDtl.OrderNum = SubQuery3.OrderDtl1_OrderNum
	and OrderDtl.OrderLine = SubQuery3.OrderDtl1_OrderLine
inner join Erp.Customer as Customer on 
	OrderHed.Company = Customer.Company
	and OrderHed.CustNum = Customer.CustNum
where (OrderHed.OpenOrder = true  and not OrderHed.CustNum in (16, 263, 288, 317, 406))
 and (OrderRel.ReqDate >= @StartDate  and OrderRel.ReqDate <= @EndDate)

group by [JobProd].[JobNum],
having (sum(OrderRel.OurReqQty- OrderRel.OurJobShippedQty- OrderRel.OurStockQty)) > 0
order by OrderDtl.PartNum

Notably, I am filtering the releases for req dates in a given range. I am also excluding a handful of specific customers.

Does anything here stand out to you? Why is the BAQ field showing one value but holding another? What’s going on with that?

i’d say some format error. Do you set format in the calc field? Change it for something longer

The format that was automatically assigned is:


This works for all the other values. As far as I can tell nothing shows an extra 11 million on top of the true value. What should this format syntax be to accommodate 6 or 7 digit currency values?

try something like ->,>>>,>>>,>>>,>>>,>>>,>>9.99

1 Like

This sort of works, in that it now shows the 11 million, but it doesn’t change when I click in it. The value should be 522k, not 11mil. If I open the grouping, I can see there is only one value being totaled in the sum. I have no idea how it is getting up to 11 mil. The entire list of values only totals a little over 1 mil.
EDIT: ohh Im wrong! The underlying value should be about 886k, not 522k.

yes, UI changes were because of too short format. It of course did not change the query result, it is just presentation problem.

1 Like

Ahhh I found it! My sum calculation is basically multiplying the value I want by the number of releases. I think I can fix this.
Thanks Olga! That format thing really threw me off!

1 Like

I am having a similar issue with a calculated sum field in one of my BAQs but in E10. Can you share how you fixed this issue on your calculated field? It may be completely different than what I’m seeing on my end but worth a shot.

I had mine calculating fine until I added in a new calculated field for the max PORel promise date, now I get an odd number for my on-hand quantity calculated field. It’s the last two in the listing below; very simple calculated fields. Also played around with joins on tables and nothing seems to be working to get the correct on-hand quantity to populate.


  • Disclaimer!!!
  • This is not a real query being executed, but a simplified version for general vision.
  • Executing it with any other tool may produce a different result.

[JobMtl].[PartNum] as [JobMtl_PartNum],
[JobMtl].[Description] as [JobMtl_Description],
[JobMtl].[RevisionNum] as [JobMtl_RevisionNum],
[PODetail].[VenPartNum] as [PODetail_VenPartNum],
[JobMtl].[JobNum] as [JobMtl_JobNum],
[Customer].[Name] as [Customer_Name],
[JobMtl].[AssemblySeq] as [JobMtl_AssemblySeq],
[JobMtl].[MtlSeq] as [JobMtl_MtlSeq],
[JobMtl].[RequiredQty] as [JobMtl_RequiredQty],
[JobMtl].[IUM] as [JobMtl_IUM],
[JobMtl].[IssuedQty] as [JobMtl_IssuedQty],
(JobMtl.RequiredQty - JobMtl.IssuedQty) as [Calculated_Open_Qty],
[JobMtl].[ReqDate] as [JobMtl_ReqDate],
[JobMtl].[EstUnitCost] as [JobMtl_EstUnitCost],
(JobMtl.EstUnitCost * JobMtl.RequiredQty) as [Calculated_Estimated_Total],
[JobMtl].[TotalCost] as [JobMtl_TotalCost],
[OrderHed].[NeedByDate] as [OrderHed_NeedByDate],
[PartPlant].[LeadTime] as [PartPlant_LeadTime],
[PartPlant].[NonStock] as [PartPlant_NonStock],
[Part].[ClassID] as [Part_ClassID],
[Vendor].[Name] as [Vendor_Name],
[PORel].[PONum] as [PORel_PONum],
[PORel].[POLine] as [PORel_POLine],
[PORel].[PORelNum] as [PORel_PORelNum],
[POHeader].[OrderDate] as [POHeader_OrderDate],
[PORel].[NeedByDate] as [PORel_NeedByDate],
[PORel].[DueDate] as [PORel_DueDate],
[PORel].[PromiseDt] as [PORel_PromiseDt],
[JobHead].[DueDate] as [JobHead_DueDate],
[JobProd].[OrderNum] as [JobProd_OrderNum],
[JobProd].[OrderLine] as [JobProd_OrderLine],
[JobHead].[JobEngineered] as [JobHead_JobEngineered],
[JobHead].[JobReleased] as [JobHead_JobReleased],
[JobMtl].[IssuedComplete] as [JobMtl_IssuedComplete],
[JobAsmbl].[Description] as [JobAsmbl_Description],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[RevisionNum] as [JobAsmbl_RevisionNum],
[JobHead].[ProdTeamID] as [JobHead_ProdTeamID],
[Part].[CommodityCode] as [Part_CommodityCode],
[JobHead].[JobClosed] as [JobHead_JobClosed],
[JobHead].[JobComplete] as [JobHead_JobComplete],
(max(PORel1.PromiseDt)) as [Calculated_Promise_Date_Mtil],
** (sum(PartBin.OnhandQty)) as [Calculated_On_Hand_Qty]**

I just wanted to send a follow-up. I was able to fix my issue by adding a subquery for the additional information for the PO release dates.

1 Like