Skip to main content

Howdy all,

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!
 

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.

@Gabriel Michaud That did the trick! Thanks for the details and for your generous answer.

Awesome, thanks for letting me know! I was in a rush and couldn’t prepare a working sample for that but would have been happy to do so :sunglasses:

Hi Arline,

Can you please write the JOIN over here please?

Thank you

Jennifer

@jennifer39 I missed this a while ago. Here are the joins & grouping I used to make this work.

Table joins from ARInvoice to ARBalances

 

 

Adding ARBalances.CustomerID as a grouping element

 

 

Hope that helps!

Hey everyone,

 

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?

 

Thanks!

Beck

I just wanted to update. The AR Balance table did not work for me. I had to use the ARBalancesByBaseCuryID. 

Reply


Terms & ConditionsCookie settings