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 th…

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 quer…

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], [I…

Do you know how to do a subquery? this post shows how to make a sub query if you don’t. [image] BAQ results on two lines rather than on 1 line ERP 10 Let us know if you need a step by step walk through. It’s really very simpl…

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

It’s still not working as needed. [image] 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.

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?

The Subquery’s Display Fields: [image] [image] and finally [image] I’ve never made sub querys so excuse any ignorance

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 tryin…

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 …

See my edit. You need to filter in the sub

[image] Caleb323: 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. …

Calculated Field Issue

Epicor ERP 10

Banderson (Brandon Anderson) August 16, 2017, 6:10pm 4

Do you know how to do a subquery?

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

Topic		Replies	Views
Subquery on InvcDtl to InvMisc Epicor ERP 10 baq	2	491	February 19, 2019
Calculated Field Help Epicor ERP 9	5	824	January 5, 2017
SQL Query Help Epicor ERP 10 e10 , sql	7	4028	November 11, 2017
Calculated Field Conversion Epicor ERP 10	4	1074	June 14, 2017
Trying to add a Shipped Qty Total to the bottom of the AR Invoice Report Kinetic ERP ssrs	17	664	April 10, 2024

Calculated Field Issue

Related topics