Microsoft Dynamics 365 Business Central (BC) does not provide a built-in report that clearly shows how a payment is applied across multiple invoices. This article explains how to create a Business Central cash receipts report using Popdock Query Builder.
Follow these steps to build a report that shows customer payments applied against invoices in Popdock Query Builder.
1. From the main menu, select Connectors. Find your Business Central Sandbox connector that contains your Query Builder, and then select the Edit icon.
2. Select Lists and then select Query builder. Then select Add list.
3. In the Name field, enter Cash Receipts Report. Select Financial for the Group. Then select Add.
4. Select Query builder and then select Add a table.
5. Select Cust. Ledger Entry. In the Query editor pane on the right, select Fields. Then select the following fields:
- Amount
- Applies-to Doc. No.
- Applies-to Doc. Type
- Applies-to Ext. Doc. No.
- Credit Amount
- Customer Name
- Customer No.
- Debit Amount
- Dimension Set ID
- Document Date
- Document No.
- Document Type
- Due Date
- Entry No.
- External Document No.
- Global Dimension 1 Code
- Global Dimension 2 Code
- Pmt. Discount Date
- Posting Date
- Reason Code
- Remaining Amount
6. Select Fields and then Show default fields. Then select Add default fields. Add the fields shown below.
7. Select Restrictions and then select Add a restriction.
8. In the new restriction row, set the following:
- Field: Document Type
- Restriction Type: Equals
- Value: Payment
- Applies To: Everyone
9. Next you will need to create a second Query Builder list. Select the Business Central connector in the breadcrumbs. Select Lists and then select Query builder. Then select Add list.
10. Add a new list named DCL Detail and then select the Sales group. DCL (Detailed Customer Ledger) is an example name for this list. You can use a different name if you want.
11. Select Query builder and then select Add a table.
12. Select Detailed Cust. Ledg. Entry and then add the fields shown below.
- Amount
- Applied Cust. Ledger Entry No.
- Credit Amount
- Currency Code
- Cust. Ledger Entry No.
- Customer No.
- Debit Amount
- Document No.
- Entry Type
- Reason Code
- Source Code
13. Select the Cust. Ledger Entry No. field. In the Query editor pane, select Restrictions. Add the restriction below.
14. Select Fields and then select Show default fields. Then select all fields.
15. Switch back to the Cash Receipts Report created in step 3.
16. Select Details, and then select Display a related list.
17. In the Detail setting, set the following:
- Name: Applied to
- Connector: Business Central Sandbox
- Group: Sales
- List: DCL Detail
- Display method: Below the record
18. You’ve now created a Cash Receipts Report with details. To view the Cash Receipts Report, go to the homepage and select Add a new tab. Select your Business Central connector and then select the Cash Receipts Report.
19. In the list, you can select a record and then select Applied to in the lower right to see details for the selected record.
Your report will clearly show you how a payment is applied across multiple invoices.
Having trouble creating this report? Email support at support@eonesolutions.com
