Calculated Field Issue

Epicor ERP 10
1

I’m having some issues with creating a calculated field that subtracts an InvoiceAmt from any MiscCharges with a “SHIP” Code attached to it.

So say there’s an invoice with 2 Misc Charges on it, one for “TAX” and one for “SHIP”; I want the calculated field to subtract only the “SHIP” MiscAmt from the InvoiceAmt.

As of right now, I have this: case when InvcMisc.MiscCode = 'SHIP' then InvcHead.InvoiceAmt - InvcMisc.MiscAmt else '0.00' end

Which works only when the invoice has one “SHIP” on it… If the invoice has several lines with a “SHIP” Misc Charge on it then it doesn’t work… I thought since it’s not adding the “SHIP” Misc charges together, it needs a SUM… So I tried: (case when InvcMisc.MiscCode = 'SHIP' then sum(InvcMisc.MiscAmt) else '0.00' end) but that ends up summing all the Misc Charge amounts. (and obviously not using InvoiceAmt at all)

So I basically need something like this: case when InvcMisc.MiscCode = 'SHIP' then (InvcHead.InvoiceAmt - sum(ONLY 'SHIP' InvcMisc.MiscAmts) else '0.00' end

Any help would be appreciated.

2

If you are using the BAQ designer, I would do a subquery to add up the Invoice amounts grouped by orders, then another to add up the MIsc Charges summed by order. Then you can filter the table for the Misc charges so you only get the SHIP code lines. Then, on the top level, match up the two sub queries and you can make a calculated field out of that. (the CASE function doesn’t filter things in a calculated field like you think it would, it needs to be filtered before you get to that)

If you aren’t using the BAQ designer, then I’m of no help to you because I don’t know back end SQL. (sorry)

3

I’m using the BAQ Designer…

Here’s my query:
select
[InvcGrp].[GroupID] as [InvcGrp_GroupID],
[InvcGrp].[CreatedBy] as [InvcGrp_CreatedBy],
[InvcGrp].[FiscalYear] as [InvcGrp_FiscalYear],
[InvcGrp].[FiscalPeriod] as [InvcGrp_FiscalPeriod],
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
[InvcHead].[OrderNum] as [InvcHead_OrderNum],
[InvcDtl].[OrderNum] as [InvcDtl_OrderNum],
[InvcGrp].[InvoiceDate] as [InvcGrp_InvoiceDate],
[OrderHed].[OTSZIP] as [OrderHed_OTSZIP],
[Customer].[Zip] as [Customer_Zip],
(case when InvcMisc.MiscCode = ‘SHIP’ then InvcHead.InvoiceAmt - InvcMisc.MiscAmt else ‘0.00’ end) as [Calculated_ItemSales],
[InvcMisc].[MiscAmt] as [InvcMisc_MiscAmt],
[InvcMisc].[MiscCode] as [InvcMisc_MiscCode],
[InvcHead].[InvoiceAmt] as [InvcHead_InvoiceAmt],
((case when InvcMisc.MiscCode = ‘SHIP’ then sum(InvcMisc.MiscAmt) else ‘0.00’ end)) as [Calculated_Test]
from Erp.InvcGrp as InvcGrp
inner join Erp.InvcHead as InvcHead on
InvcGrp.Company = InvcHead.Company
And
InvcGrp.GroupID = InvcHead.GroupID

left outer join Erp.OrderHed as OrderHed on
InvcHead.Company = OrderHed.Company
And
InvcHead.OrderNum = OrderHed.OrderNum

inner join Erp.InvcDtl as InvcDtl on
InvcHead.Company = InvcDtl.Company
And
InvcHead.InvoiceNum = InvcDtl.InvoiceNum

inner join Erp.Customer as Customer on
InvcDtl.Company = Customer.Company
And
InvcDtl.CustNum = Customer.CustNum

left outer join Erp.InvcMisc as InvcMisc on
InvcHead.Company = InvcMisc.Company
And
InvcHead.InvoiceNum = InvcMisc.InvoiceNum

group by [InvcGrp].[GroupID],
[InvcGrp].[CreatedBy],
[InvcGrp].[FiscalYear],
[InvcGrp].[FiscalPeriod],
[InvcHead].[InvoiceNum],
[InvcHead].[OrderNum],
[InvcDtl].[OrderNum],
[InvcGrp].[InvoiceDate],
[OrderHed].[OTSZIP],
[Customer].[Zip],
[InvcMisc].[MiscAmt],
[InvcMisc].[MiscCode],
[InvcHead].[InvoiceAmt]

4

Do you know how to do a subquery?

this post shows how to make a sub query if you don’t.

5

Thank you.

That post helped out a bit. I’ll try the solution as soon as I get some time

6

It’s still not working as needed.

As you can see below, on the 11480 invoice there’s two SHIP charges, but it doesn’t calculate the total on one line, it separates it like before.

7

Are you summing and grouping in the sub query? You must have something in the sub that is unique to each line. What display fields do you have in the sub query?

8

The Subquery’s Display Fields:

image.png921×153 3.29 KB

image.png839×189 2.97 KB

and finally

image.png895×202 4.09 KB

I’ve never made sub querys so excuse any ignorance

9

You should be adding up the totals in the sub query, so you should have calculated fields in the the sub queries. It doesn’t look like you are summing and grouping in the sub, so it will return multiple rows (like you are seeing). This would be an example of one of the sub-queries that you are trying to do. You use the criteria in the InvcMisc to only return the SHIP code (we have different codes so that won’t work for our system, but you get the point) Then you add up grouping by only the things you need to identify the matches. It’s easy to get multiple rows by including things you don’t need.

image.png1209×183 4.51 KB

image.png825×401 6.63 KB

image.png774×629 32.2 KB

10

I believe I see what you’re saying.

I don’t think this will get me what I want. If I sum up the MiscAmts and they contain other Misc Charges then they’ll add into it. I understand you did say filter by MiscCode “SHIP” only on the table, but then I can’t see the other invoices that may have other MiscCharges.

I just wanted to calculate all of the SHIP MiscAmts and put it on one line if it comes up, if it doesn’t come up, then no big deal - I still want to see the info.

11

See my edit. You need to filter in the sub

12

Just bring in the same table on a different level if you want to see all of them.

13

Alright I’ll continue working on this. Thank you

14

Also don’t forget about left, right, inside and outside joins. They have different names in 10.1 but you can see the join type below. So if you don’t have anything in your sub it will still return a row, just the sub rows will be empty. Then you don’t lose any.

image.png897×477 24.3 KB

15

Yep I did a left join for the SubQuerys on the Top Level so that it only shows invoices that are in the InvcGrp or whatever…

So I did all that and still get this:

At the bottom, invoice 11480 still has two different lines for the same ‘SHIP’ charge (Yes they’re on different lines, but I still want them to add up)

16

Well you have line 1 and line 2 showing on the top level, so they are different rows.

17

image.png631×645 18.5 KB

18

take the invoice line out of that sub and it should work then.

1 Like
19

Wow that was a simple little fix.

Thanks Brandon, now the calculated field is working as needed.

Now I need to make it look pretty…