Small and medium business | Business Central, N...

Power BI: How to get the Sales Invoice(s) to which a payment was applied

(0) Share

Report

Posted on by Len C

149

The company I am working for pays sales commissions based on received revenue, rather than posted invoices. To get the data needed for a monhtly sales commision calculation, we need to know the customer payments

The Customer Ledger lists received payments, including Salesperson. Looks promising. However, the payment ledger entries do not separate out what part of the payment was tax. Note that the Customer Ledger also contains the Posted Sales Invoices - with base amount and amount with tax separate.

So we could solve our problem if we could retrieve the invoice ledger entries to which a payment was applied. But I can't find that connection in the data model.

How can we retrieve the posted sales invoices to which a Payment was applied?

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (11)

Answers (0)

Suggested answer

Bilal Haider 412 on at

Like (1)

Report

Hi,

You are right and there is a reason why you do not see it in data model. There is no table relation between Customer Ledger Entry and Posted Invoice table. And in data model we see if it has relation based on keys.

In Customer Ledger Entry there is no proper way to get applied invoices for payments in code. There is action on page that can show you Applied entries against a payment.

Another problem is that one customer ledger line of payment type can be applied to several Invoices.

Was this reply helpful? Yes No
Suggested answer

darcnus 247 on at

Like (2)

Report

There really isn't an easy way to do this out-of-the-box. The commission system in BC is extremely basic and would require a ton of customization to be useful.

"Commission Management" is an extension by NAV-X that did everything we needed it to do (including commission down to line-level tracking), but it is kind of expensive.

Was this reply helpful? Yes No
Len C 149 on at

Like (0)

Report

Yes, we are not going to use the out of the box Commission system. That is why we are trying to do what is described in the initial post.

Was this reply helpful? Yes No
Len C 149 on at

Like (0)

Report

I expected there to be relationship tables. How can I find which ones they are?

Note we are using MBC (online), so we are limited to exposing Pages, Queries, and CodeUnits (and not sure what good CodeUnits are in this scenario).

Was this reply helpful? Yes No
MarisolC 103 on at

Like (0)

Report

Did you find a way to solve this?

Was this reply helpful? Yes No
Len C 149 on at

Like (2)

Report

Yes....you have to pull in Detailed Customer Ledger Entries, and use some logic to figure out what payments (and credit memos) are applied to invoices.

There are some guidelines we put on how to 'apply' in MBC, because I could not figure out the logic for all the ways MBC allows you to 'apply'. For example, in MBC, an invoice can be 'applied' to a payment. More interesting, a payment can be applied to anther payment and an invoice. If the three net out to $0, all are considered 'closed'. But my Power BI logic cannot handle that.

So, we created procedures where Payments are always 'applied' to Invoices, not the other way around. Same with Credit Memos - they are 'applied' to Invoices.

I suspect there may be a way to handle the any-to-any approach allowed by MBC, I just did not have time to figure that much out with Power Query & Power BI.

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (1)

Report

Hi

Len provided Power BI method. We have used Jet and Power BI for such a calculation. But finally we switched to commission app named iDyanmics Commission which handle these scenarios better and give us easier method

appsource.microsoft.com/.../PUBID.aitana_management_sl|AID.idynamics_commissions|PAPPID.9809764e-634d-48ba-a656-b7b27e2c96b4

Was this reply helpful? Yes No
MarisolC 103 on at

Like (1)

Report

I ended coding my own solution, it was kind of difficult, but it worked out... It gave me a deeper understanding on how BC applies payments and Credit Memos, BC has some extrange ways to register some things... Once I figure it out, it was sort of easy to pull everything together:

Thanks for your comments, they helped me a lot.

Was this reply helpful? Yes No
MSC6620 5 on at

Like (0)

Report

Could you share some of your logic and approach? We are facing a similar issue. Thank you!

Was this reply helpful? Yes No
MarisolC 103 on at

Like (0)

Report

I'll try to explain this, that for me is very complicated way to register, because it is simple when you have 1 invoice = 1 payment but, when you have 1 invoice, several payments and/or several credit notes, it is a madness.

1. Simple case. 1 invoice = 1 payment or credit note

You have to look into Cust__Ledger_Entry for Customer movements, I did this in two steps, first to identify one payment, and that payment which invoice was closed by.

Document type Entry No. Closed by Entry No. Amonunt

Payment 1 2 1,000

Invoice 2 1 1,000

This is the easiest case.

2. Mix case 1 invoice = several payments and/or credit notes

You have to look into Cust__Ledger_Entry for Customer movements, I did this in two steps, first to identify one payment, and that payment which invoice was closed by, then look for another payment(s) or credit note (s)

Document type Entry No. Closed by Entry No. Amonunt Invoice paid

Payment 1 2 -500 2

Invoice 2 1 1,000 2

Credit Note 3 2 -500 2

What I did after I was able to identify proper relationships was to put right after each movement wich invoice has been paid, this way I could identify all documents related to an invoice, also with this data I was able to sort by customer and invoices with its payments.

My natural thinking was to start by invoice, but right after I started with this approach I realized it was very difficult when I had several related documents either payments or credit notes.

I will be happy to share my code for this if you want it. I have been so lonely and with bad service from my partner that I don't want anyone else going through the same. Besides no one wants to explain or is not documented at all.

Hope this can help you.

Was this reply helpful? Yes No