Skip to content
+1-888-319-3663

EONE BLOG

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:

EntryNo

InvoiceNo

PaymentAmount

1

INV001

-789

2

INV001

-90

 

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

Leave a Comment





RECENT POSTS


Employee Spotlight: Giovanni Fragoso, Support
Popdock Bootcamp - Online, Fall 2021
D365 Business Central Integration Bootcamps - Online, Fall 2021
10 Reasons Why SmartConnect Users Won't Want to Miss the eOne Event 2021!
Dynamics GP Integration Bootcamp - Online, September 2021

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.