Script to Remove Advanced Bank Reconciliation Transaction Matches

Microsoft Dynamics GPI do demonstrations of quite a bit of functionality in Microsoft Dynamics GP which often necessitates restored databases or otherwise reversing transactions and processing. One of the modules I’ve done some demonstrations of recently is the Advanced Bank Reconciliation module from Nolan Business Solutions. One of the features of this module is allowing you to atch statement entries with transactions in Dynamics GP.

If you use the auto-reconcile rules to do the matching and then want to undo the match, you need to select each matched transaction and unmatch them and then do the next and so on, which can take quite a while. The below script will remove the matches from both the NC Matched Statement Trx 11003k (NCABR011) and NC_Matched_GL_TRX (NCABR012) tables:

Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant ( This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
UPDATE NCABR011 --NC Matched Statement Trx 11003k (NCABR011) SET NC_Matched = 0 ,NC_Match_Number = 0 WHERE NC_Matched = 1 GO UPDATE NCABR012 --NC_Matched_GL_TRX (NCABR012) SET NC_Matched = 0 ,NC_Match_Number = 0 WHERE NC_Matched = 1 GO

This script was created for use ona demo/test system and appears to run without adverse effect, but I would recommend caution (via a backup) and thorough testing before using it on any system.

Our Favourite Add-ons from the recent ISC Software Webinar

ISC Software SolutionsIn our most recent webinar, on Our Favourite Add-ons, we took a look at six of the add-ons which are preferred solutions from our senior consultants. They all meet a requirement which numerous clients have given to us at different times. Usually, there are several plugins which offer similar functionality which would meet these requirements, but these six add-ons are the ones which we recommend time after time. If you want to catch up on, or any other, webinar, you can do so here.

Before I give a brief overview of each of the add-ons, a quick introduction. Microsoft Dynamics GP is a full-featured ERP system with many modules, but one of the original founding design decisions was to support the enhancement by way of third party add-on modules, which closely integrate into the software and in many cases are indistinguishable from the core module. These add-ons are available from many, many vendors around the world (although many of them are located near the birth-place of the software in Fargo, North Dakota).

Each consultant will usually have a list of go-to add-ons which they’ve recommended to a number of clients, and this list is derived from these lists from each of the consultants (there was a lot of overlap between us). This is not to say that there are not other great, competing, add-ons; there are but we had to limit the list to six in order to cover them in a thirty minute webinar.

SmartList Builder

Smartist Builder is the add-on with which I have the most and longest experience. Created by eOne Solutions, it allows for the creation of new SmartList objects to meet needs not covered by the out-of-the-box SmartList objects; this often covers reports such as vendors with email and EFT details or Payables Distributions. It also supports the creation of SmartList objects using SQL views such as Budgets By Month or Sales By Customer By Fiscal Year.

While you can also do this using the SmartList Designer, which comes as a standard part of Microsoft Dynamics GP, SmartList Builder is much more flexible and easy to use and has a lot more functionality; I have a blog from May 2019 covering the differnces between SmartList Builder and SmartList Designer.

I have previously blogged about implementing SmartList Builder.

Continue reading “Our Favourite Add-ons from the recent ISC Software Webinar”

Setting Bank Account In ABR To Extract Automatically In Reconcile Bank Transactions

Microsoft Dynamics GPConfiguring Nolan’s Advanced Bank Reconciliation (ABR) to automatically extract transactions when reconciling a bank account is not complicated, but I keep on forgetting where the option is located. That gets a little embarrassing when you tell a client, “yes, you can do that” and then have to spend five minutes looking for the option.

So by writing it down here, I am hoping to be able to commit it to memory.

this option is per bank account and is located on the Bank Account Setup window (Financial >> Setup >> Advanced Bank Reconciliation >> Bank Setup).

Click the Options menu and then select Extract options:

Bank Account Setup

Continue reading “Setting Bank Account In ABR To Extract Automatically In Reconcile Bank Transactions”

Extract ABR Transactions For Import As Statement

Microsoft Dynamics GPthis is a simple script, but I’ve written it about four times now so I figured I’d post it so I can find it easily next time I lose my local copy.

Perfect Image are resellers of the Advanced Bank Reconciliation module from Nolan Business Solutions (along with the other add-ons they’ve written for Dynamics GP) and I often need to demo this replacement for the standard Bank Rec module.

One item I typically show is the auto-Propose function which matches transactions against statement lines in the Reconcile Bank Transactions window (Transactions » Financial » Advanced Bank Reconciliation » Reconcile Bank Transactions).

To do this I need to be able to import statement lines which match the transactions in Dynamics GP; the easiest way of doing this is to extract the transactions.

This can be done with a very simple SQL script:


Once the data has been extracted it can be imported during the demo using the standard ABR Import Statement routine.