Tech Tuesday: Querying Business Central Payments

Exporting Business Central payments to another system such as a CRM system is a common requirement, we see on Business Central integrations. The need is simple but retrieving all the data can be challenging due to the table structure of the Customer Ledger.

The Challenge

Recently I was working on a Business Central and ConnectWise integration where the Invoices were initially created in ConnectWise, but payments would be processed in Business Central. After a payment has been posted in Business Central it needs to be imported to ConnectWise to reflect the correct remaining balance.

If we check the Cust. Ledger Entry table we can see the payment with the Document No (Posted Invoice Number) and the Description (‘Invoice’ + Pre-assigned No). In my scenario I needed the External Document No from the Posted Invoice which is not available on the Cust. Ledger Entry table.

The Resolution

When working with ledger and historical transaction tables we need to be careful because these tables can become very large and if we are not filtering our sources correctly, we can see performance issues on Business Central and SmartConnect when large data sets are processed. Below are two approaches that can be used.

Use a SmartConnect Query

We can create a bulk SmartConnect data source, that combines the Customer ledger Entries (25) page with the Posted Sales Invoices (143) page to pull the needed data. This requires SmartConnect to load every Posted Invoice that may have received a payment which could potentially be data over a year ago depending on the business processes. Make sure filters are added to the services as shown in the below screenshot to prevent large historical data sets when possible. The date filters can be made incremental by using Global Variable Filters.

Pros:

• No custom development
• Quick to implement

Cons:

• Limited to default fields on Cust. Ledger Entry page
• No Created DateTime Stamp
• Unnecessary data loaded by SmartConnect
• Increased Business Central API usage

Write a Query in AL

Business Central Queries require custom development, but the power and flexibility provided by a custom query can provide a boost to the overall effectiveness of the integration. When using a query, we can request only the records needed from related tables. An example query for exporting Payments can be found on GitHub

In the example below I am exporting the payment along with the invoice data so I have all the information needed to perform a writeback to the external system.

Pros:

• Fast Performance
• Minimum possible Business Central API usage
• Data source aggregation

Cons:

• Requires custom development

Bonus Tip

On a project I was working on a while back we were writing total amount paid on Invoices to a Salesforce Opportunity. We did not want to store each payment in Salesforce, but rather recalculated the total paid each time a new payment was applied. There are two options to do this with SmartConnect. If you are planning to write an AL query, then I suggest AL Aggregation to offload the processing to the Business Central SQL Server rather than your SmartConnect server.

AL Aggregation

I added a new data item to me query to calculate the sum of every payment on an invoice related to the recently applied payment. This allows me to take the new payment and lookup all related payments quickly and return the total.

dataitem(TotalPayment; "Detailed Cust. Ledg. Entry")
    {
        DataItemLink = "Cust. Ledger Entry No." = Payment."Cust. Ledger Entry No.";
        SqlJoinType = InnerJoin;
        DataItemTableFilter = "Document Type" = filter(Payment);
        column(TotalPayments; "Amount")
            {
                Method = Sum;
            }
    {

SmartConnect Aggregation

SmartConnect also supports data aggregation. If you have a data source with all your payments for an invoice you can use the grouping of your destination to determine the aggregation.

Take the below example:

If I group my data source InvoiceNo and set the “Function” on PaymentAmount to “Sum” I will create one destination record with the total PaymentAmounts sent when I map the PaymentAmount Field.


If you have any questions, feel free to email me at ethan.sorenson@eonesolutions.com