Customer tracker with invoice subtotals?

Finance looks at customer tracker > Invoices > All to get a sense of how much business a particular customer does and how much credit they need if we offer terms. Unfortunately, the invoices subtotal doesn’t show up(see screenshot below). I looked at Field Help and the subtotal appears to be a calculated field, also looked at InvcHead and InvcDtl and I can’t seem to figure out where the $897.75 figure is coming from. The InvcDtl.DocExtPrice is $855. Finance wants that $897.75 figure to show up on customer tracker, but I don’t know how to do that(ideally unhide some field just don’t know what it is) or even create them a new BAQ but I can’t find that data in the DB. Can someone help me out?

Alternatively is there another report they can / should be looking at that already provides this information?

Right Click on one of the columns, and turn on Summaries. Then click on the Σ symbol on Amount, and select =SUM.

Then Right Click on one of the columns, and turn on Group By. Drag the “Sold to Customer ID” column up to the Group by area that was created.

That’ll group everything by the customer id, and sum the amount column.

You can click the + to expand the details.

@TommyD the problem I’m having is in the screenshot invoice 80409 has a subtotal of $897.75, but in customer tracker invoice 80409 shows $0 values for all fields. None of the fields displayed show a dollar value > $0.

That’s weird. Does the Cust Amount column contain the data?

@tommyD no, see screenshot it’s $0. I wrote a BAQ looking at InvcHed and InvcDtl and can’t figure out where the $895.75 comes from in the database.

Take a look at the stock BAQ - “zES_Invoice” . See if you can see the data in there. It looks like it’s in “InvcHead_DocInvoiceAmount”

Subtotal is just the Extended Prices of the Invoice Lines plus any Miscellaneous Charges

Hi Eddie,

That is odd; we have it on ours in version 10.1.600.xx


1 Like

I just ran into a similar thing on the Vendor side. The ExtCost for every PO - prior to a certain date - is zero.


It shows on the PO Tracker too…


At first it looked like it aligned with a recent upgrade (10.1.400 -> 10.2.300) on 12/15/2019. But there are PO’s creted before that date that do properly calc the Ext Cost.

They should probably want to see the Invoice Amount for each invoice.
Because that would keep Adv Billing from being added in twice.


It looks like your issue is with the fact that a Cash deposit was applied to the invoice, thus reducing the Total.

@ckrusen thanks for your reply. Sorry I’m not very familiar with finances so I’m not following your reply. Are you suggesting this is a bug I should take to support(Why is the Invoice Amount $0?) or is this expected behavior?

I did figure out how to show the extPrice for finance(minus taxes, and misc charges I understand), but I didn’t figure out what fields will give me the illusive $897.75 value I see in the subtotal of AR Invoice Tracker…

This could be different depending on which version you are, but the basic formula is:
SubTotal = Invoice Amount + Prepaid Deposits + Cash Deposits + Lines Advance Billing - Taxes - Rounding

So in your example it is basically SubTotal = Cash Deposits

At least on the most recent version you can find this amount in InvcHead.DocDepositCredit, doesn’t look like Prepaid Deposits are kept on InvcHead but rather calculated from other tables.

The help from Invoice Tracker shows:

Invoice Amount

Displays the total amount that this customer owes or is credited on the invoice. This amount is calculated by subtracting the Less Deposits from the Line Amounts and calculating the tax.


Sales Tax = (Line Amounts – Less Deposits) x Sales Tax % Total = (Line Amounts – Less Deposits) + Sales Tax

Meaning that is what the customer initially owed on that invoice.

Advanced Invoices are made prior to the shipment or final billing.

Inv 1000 - An Advanced Invoice on Order 1234, of $500
Packer 4321 - Packer for Order 1234
Inv 1001 - A Shipment invoice for packer 4321. The order that packer 4321 refrenced would make $500 of billing. But since $500 has already been invoiced under Inv 1000, Inv 1001 nets to $0.

We upgraded from 10.1.600, and it broke the PO totals. In 10.2.300 onwards there is a built in fix for it:


Thanks for the explanation of how one can calculate an Invoice SubTotal. Unfortunately I don’t know where those fields live in the DB, so I’m still stuck. I just looked through all the example BAQs(starting with z) and don’t see any that demonstrate how to calculate an Invoice Subtotal.

Is is possible you could break down the calculation to DB table & Field(s), or is there a reference of some sort I can look at to see how to calculate an invoice subtotal?

This formula seems to work for the few Invoices I’ve tested: InvoiceSubtotal = InvcDtl_DocExtPrice + InvcDtl_DocTotalMiscCharge

For what your Finance Team is trying to accomplish they may want to consider the Aging tab in Customer Tracker > Billing, there are summaries there that may be helpful.
Also, check out what Customer Credit Manager can offer.
In most cases I have found that Finance is better off using Customer Credit Manager instead of Customer Tracker for 90% of their tasks.