Subquery on InvcDtl to InvMisc

Epicor ERP 10
1

Having a hard time with a subquery. I am trying to return a “modified line total” as ExtendedPrice + MiscChrg (but, MiscChrg needs to be based on only certain MiscCodes, requiring a subquery).

I added the InvcDtl table to my query with an InnerSubQuery connected to InvcMisc on Company, InvoiceNum, InvoiceLine. In this subquery I display (grouped by) Company, InvoiceNum, and InvoiceLine, along with a calc field for sum of MiscChrg (the table criteria selects the MiscCode we need).

Now, the issue: For each InvcDtl result that has a value in the subquery, my “modified line total” returns as desired. HOWEVER, for each result that has a null in the subquery, the modified line total simply displays a blank.

My modified line total calc looks like: InvcDtl.ExtPrice + SubQuery2.Calculated_TotalMiscChrg

Any thoughts?

2

The relationship between InvcDtl and Subquery is “All rows from InvcDtl”. The calc:

InvcDtl.ExtPrice + SubQuery2.Calculated_TotalMiscChrg

displays a blank whenever the Subquery returns a null value for Calculate_TotalMiscChrg.

3

In my calculated field I needed to add a null check:

InvcDtl.ExtPrice + isnull(SubQuery2.Calculated_TotalMiscChrg, 0)

1 Like