I’m looking for support on showing some additional customer aging data on the Invoice Form (AR641000).
Ideally I’d show these fields:
Customer Current Balance
Invoice Amount
Customer Current Balance - Invoice Amount as “Revised Balance”
I *think* what I want to do is add a subreport to the invoice form (AR641000) that references the Customer Documents Total filed from the AR Balance by Customer Report (ar632500). But I’m not clear how to access that exact field value:
But I’m not sure what the best way to proceed is in order to recreate the field “$CustomerBal” and add it to the Invoice as a subreport.
Or, should I try and do the linking within the current report so I can have “$CustomerBal” interact with the invoice total field?
Would be curious to hear what others would do.
Thanks for the ideas!
Best answer by Gabriel Michaud
Hi Arline,
The AR Report looks at many different tables and calculates the balance on the fly based on the returned data -- $CustomerBal is just a property where this balance gets stored. Fortunately, you do not have to replicate all of this in your invoice form.
My suggestion is to add a LEFT JOIN to the ARBalances table (join on CustomerID) and GROUP by ARBalances.CustomerID. The current balance is in the CurrentBal field, since you can have multiple rows because balances are tracked by branch/location you’ll need to use SUM().
Note: you will need to consider the released status of the invoice in your formula to show the accurate balance of the client including the new invoice. If the invoice is not released, it will NOT be included in the CurrentBal field, so you would have to add it yourself.
The AR Report looks at many different tables and calculates the balance on the fly based on the returned data -- $CustomerBal is just a property where this balance gets stored. Fortunately, you do not have to replicate all of this in your invoice form.
My suggestion is to add a LEFT JOIN to the ARBalances table (join on CustomerID) and GROUP by ARBalances.CustomerID. The current balance is in the CurrentBal field, since you can have multiple rows because balances are tracked by branch/location you’ll need to use SUM().
Note: you will need to consider the released status of the invoice in your formula to show the accurate balance of the client including the new invoice. If the invoice is not released, it will NOT be included in the CurrentBal field, so you would have to add it yourself.
I know this was answered a long time ago but I followed the instructions and had a few small issues. Some of the invoices are coming through correct with the SUM but others are duplicating the amount several times. For one invoice, the amounts should be $50k, but the total is coming out to $800k. (The amounts are actually a bit different than that and it is perfectly multiplying the amount.) I have tried several different ways adding the table. I have looked over the table itself and verified the totals there. I have put the SUM field and the regular field in different parts of the report just to see if it reflects different, but not having any success. Does anyone have any ideas?
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
