Top Categories:
Dynamics GP
ClassicPress

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”

Error opening Word templates from Microsoft Dynamics GP Workflow Action emails

Microsoft Dynamics GPA client reported a problem with workflow approvers opening the batch edit list on a Microsoft Dynamics GP General Ledger Batch Approval workflow action email this morning; only a few users are having problems while everyone else is working without problem. The users with the problem are getting this error when trying to open the Transaction Edit List report on the email:

Word experienced an error trying to open the file...

Microsoft Word

Word experienced an error trying to open the file.
Try these suggestions.
* Check the file permissions for the document or drive.
* Make sure there is sufficient free memory and disk space.
* Open the file with the Text recovery converter.
(C:...transaction Edit List.docx)

I did some research and found there was a recent post on the Dynamics GP Support and Services Blog where they covered this same issue. The blog post was only four days ago and they acknowledge the issue is ongoing and offer the workaround of downgrading Microsoft Office to work around the issue (I did this recently when sending emails using MAPI caused Dynamics GP to crash).

The problem does not just affect the Word template reports from Workflow Batch Approval emails, but also the Word templates used for sending purchase or sales orders, remittances and so on. For the non workflow Word templates, you could reconfigure the templates to use PDF, or one of the other formats, instead of Word, but, unfortunately, this option is not available for the batch edit lists.

Following the initial report, we’ve had a number of other clients report the issue, so it may be worth preempting problems and block Office updates for Dynamics GP users until a fix is in place from Microsoft.

Upcoming Microsoft Dynamics GP Webinars from ISC Software

ISC Software SolutionsI’ve been quite busy both in and out of work recently and have lagged in the writing of blog posts, so I thought I’d let you know of the three upcoming monthly webinars I’ll be delivering for ISC Software.

We run these webinars on a monthly basis, with occasional extra webinars added to the schedule so it is worth checking the Webinar Schedule page every so often.

The upcoming webinars are:

Favourite Add-ons Webinar
Next week is Our Favourite Dynamics GP Add-ons; discover our favourite add-ons to extend the functionality of Dynamics GP.

Tue, Sept 15th, 2020 4:00 PM – 4:45 PM BST

Register Here

Favourite Add-ons Webinar
In October is Budgets in Microsoft Dynamics GP; learn how to manage your budgets in Microsoft Dynamics GP (covers budget creation, maintenance and reporting on budgets).

Tue, October 20th, 2020 4:00 PM – 4:45 PM BST

Register Here

Favourite Add-ons Webinar
In November is Prepayments and accruals in Dynamics GP; discover how to improve your processing of prepayments and accruals.

Tue, November 17th, 2020 4:00 PM – 4:45 PM BST

Register Here

Show Workflow Approval Status of posted Microsoft Dynamics GP General Ledger batches

Microsoft Dynamics GPI recently deal with a support call for a client where they were having problems creating a report which showed the approval status of GL batches. Building the WfBusObjKey was more complicated than I expected as it requires the date and time from the batch; for an unposted journal this can easily be retrieved from the PA Report Posting Definitions Master (SY00500) table, but for posted batches I had to do some exploring to find where the data was stored.

The table was the Posting Definitions Master History (SY30500); I’ve obviously never written a query which used this table as the name dones;t look familiar, but once I had the tale, it was a simple task to concatenate the fields together to make the WfBusObjKey and pass it to my function which returns the Workflow Approval Status.

I’ve used the SQL query to create a view so that it could easily be hooked into SmartList Builder, Refreshable Excel or other reports::

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_GeneralLegderBatchApprovalStatus', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GeneralLegderBatchApprovalStatus
GO
-- create view
CREATE VIEW uv_AZRCRV_GeneralLegderBatchApprovalStatus AS
SELECT
	['Year-to-Date Transaction Open'].OPENYEAR AS 'Open Year'
	,['Year-to-Date Transaction Open'].JRNENTRY AS 'Journal Entry'
	,['Year-to-Date Transaction Open'].SEQNUMBR AS 'Sequence Number'
	,dbo.uf_AZRCRV_GetWorkflowApprovalStatus(
												'General Ledger Batch Approval'
												,CAST(RTRIM(['Posting Definitions Master History'].BACHNUMB) AS VARCHAR(15))
													+ '~' + RTRIM(['Posting Definitions Master History'].BCHSOURC) 
													+ '~' + FORMAT(['Posting Definitions Master History'].CREATDDT, 'yyyy/M/d') 
													+ '~' + FORMAT(['Posting Definitions Master History'].TIME1 ,'HH:mm:ss')
											) AS 'Wordflow Approval Status'
FROM
       GL20000 AS ['Year-to-Date Transaction Open'] WITH (NOLOCK)
INNER JOIN
	SY30500 AS ['Posting Definitions Master History'] WITH (NOLOCK)
		ON
			['Posting Definitions Master History'].TRXSORCE = ['Year-to-Date Transaction Open'].TRXSORCE
GO
GRANT SELECT ON uv_AZRCRV_GeneralLegderBatchApprovalStatus TO DYNGRP
GO

What’s New in the Microsoft Dynamics GP October 2020 Release

Microsoft Dynamics GPI’ve been keeping my out for any news of the next version of Microsoft Dynamics GP but haven’t seen anything on the official blogs (if I’ve missed it, I’d appreciate someone pointing me in the direction), but there have been some recent articles from other partners listing some of the upcoming new functionality.

One such article is from Aisling Dynamics Consulting, LLC on the ERP Software Blog.

Since the introduction of the modern lifecycle the official name will remain Microsoft Dynamics GP and the technical name being Microsoft Dynamics GP 18.3, I will be referring to it as the Microsoft Dynamics GP October 2020 Release from here on.

I don’t know if this is the complete list of new functionality for the Microsoft Dynamics GP October 2020 Release, but the new features listed by Aisling are:

  • System

    • Ability to disable the system print dialog box when printing a Word Template
    • Maximizing GP reports on “print to screen” rather than needing to resize the window
  • Financial

    • A New User Defined Field is available in GL Transaction Entry
    • Limiting Excel Copy/Paste decimal places to what is in Currency Setup
    • Reconcile all years in the General Ledger Reconcile utility, instead of one year at a time
  • Purchasing

    • Copy/Paste transactions in Payables transactions (as per GL transaction Entry)
    • Two new user-defined fields on Payables Transaction Entry (adding to the long description on Payables Transactions added in the October 2019 Release)
    • Added the DBA Name Field on the Vendor Card and 1099 form updates
    • The 1099-NEC form! For payables Non-Employee Compensation reporting requirements starting at the end of this year, Microsoft will include the new 1099-NEC form
  • Human resources

    • Ability to mask the Social Security Number on HR reports

Included in this fix, as always, will be a number of bug fixes.

Keep an eye on the Product Lifecycle page to make sure you remain on a supporte dversions of Microsoft Dynamics GP; GP 2010 will be out of extended support as of October 2020, so any site still using this versions should be looking to upgrade as soon as possible.

Microsoft Dynamics GP Workflow Payables Batch Approval email not including Word template

Microsoft Dynamics GPA client has recently been working on a project to implement approval workflows in a few modules of Microsoft Dynamics GP, but had an issue with the Payables Transaction Approvals notification emails not always including the Word template which all of the steps are marked to include.

There appeared to be no pattern on which approval emails either did or did not include the Word template; a user would receive the Word template on to emails, then not on the third, but again receive it on the fourth.

Doing some searching online found a post by Rob Klaproth of Armanino where he had the same issue and confirmed that there is a known issue logged with Microsoft whereby when the user submits a document for approval, and clicks submit on the comment box before the Word template has finished generating the email will not include the Word template.

The workaround for this issue is to make sure that users wait until the Word template has finished processing, as shown by the Template processing message at the bottom of the main Microsoft Dynamics GP window. If they wait until this processing has completed before hitting submit, the Word template will be included on the notification email.

This issue affects all of the edit list Word template attachments through all of the Workflow approval types.

Notepad++ Find and Replace numbers in square brackets

Useful ApplicationsI was working with some text the other day which included a lot of references using numbers in square brackets; I needed to remove this text and wanted an easy way of doing it.

I did a little research and found that Notepad++, an application I already use, can do regular expression find and replace.

The regular expression for a number surrounded by square brackets is:

\[(\d+)\]

To use the regular expression in find and replace, enter the regular expression into the Find what box and change the Search Mode to Regular expression:

Regul;ar expression find and replace

When you hit Replace, or Replace All, the regular expression will be used to repalce the matching text with the supplied replacement value.

Books referenced by Bernard Cornwell in the Sharpe series’ historical notes

LiteratureThe Richard Sharpe series is a series of historical fiction novels and short stories by Bernard Cornwell centred on the character of Richard Sharpe, charting Sharpe’s progress in the British Army during the Napoleonic Wars, starting from his early career in India through to the Battle of Waterloo and beyond (further details on the series can be found on Wikipedia).

In some of the books, Bernard Cornwell mentioned books he used as references when writing the series. I’ve not found a list of these online, so on a recent reading kept a list myself, but hadn’t actually done anything with the list. On a recent call with a client, I mentioned looking to book sightseeing holidays in Italy and Portugal. He recommended a book on the Roman period for me, Rubicon: The Triumph and Tragedy of the Roman Republic by Tom Holland, which reminded me that I’d been planning to look up the books on the Peninsular War mentioned by Cornwell.

The books Cornwell mentioned in the Sharpe series are:

The Sharpe books in which the above are referenced were:

  1. Sharpe’s Tiger
  2. Sharpe’s Eagle
  3. Sharpe’s Escape
  4. Sharpe’s Waterloo
  5. Sharpe’s Devil

I’ve picked up the Elizabeth Longford book on Wellington as a starting point along with Donald Tomas’ book on Thomas Cochrane, who had a varied and controversial career which I’m sure is going to be a fascinating read.

Berard Cornwell has himself written a book on the Battle of Waterloo: The History of Four Days, Three Armies and Three Battles which has been in my “to read” stack for quite a while, being joined now by my recently purchased copy of Tom Holland’s book.

Find text in any SQL object

Microsoft SQL ServerI needed to find custom triggers or views which referenced a certain table and although I thought I had a script which would find text in a SQL object like a trigger, view or stored procedure, I couldn’t find one when I searched my site the other day.

It only took me a few minutes to write one; the first highlighted text is the text to search for and the second a limitation on the name of the SQL objects to check.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
DECLARE @Search varchar(255) SET @Search='PM00200' SELECT DISTINCT ['SQL Objects'].name AS Object_Name ,['SQL Objects'].type_desc FROM sys.sql_modules AS ['SQL Modules'] INNER JOIN sys.objects AS ['SQL Objects'] ON ['SQL Objects'].object_id=['SQL Modules'].object_id WHERE ['SQL Objects'].name LIKE 'u%_AZRCRV_%' AND ['SQL Modules'].definition LIKE '%'+@Search+'%' ORDER BY ['SQL Objects'].name ,['SQL Objects'].type_desc

SQL script to create macro to delete items in Microsoft Dynamics GP

Microsoft Dynamics GPI’ve been doing some work with SmartConnect for a client recently where one of the integrations was creating new items. As the project progressed, some of the items which had been imported needed to be replaced with different item numbers. To ensure we didn;t cause problems, I didn’t want to delete items through SQL directly, due to the number and variety of tables involved, so needed to come up with a way of generating the macro.

Macros are useful ways of repeating an action, such as deleting items, but any variation to the data and the macro will fall over. One variation was that some items had posted transactions against them so I needed to avoid these transactions.

I created the below script to create the macro for me, with SSMS set to output to text, and built in joins and checks on all of the relevant tables in Purchase Order Processing, Inventory and Sales Order Processing which may have contained data. The script is probably a little overkill on the checks it does, but I wanted to make sure it caught as much as possible.

Once the script has been run, you can copy the macro text into a file and play the macro to delete the items (if you have a lot of items, you can play the macro fast).

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT '# DEXVERSION=14.00.0085.000 2 2 CheckActiveWin dictionary ''default'' form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' TypeTo field ''Item Number'' , ''' + CAST(RTRIM(['Item Master'].ITEMNMBR) AS VARCHAR(50)) + ''' MoveTo field Inactive # ''FALSE'' CommandExec dictionary ''default'' form ''IV_Item_Maintenance'' command ''Delete Button_w_IV_Item_Maintenance_f_IV_Item_Maintenance'' # Are you sure you want to delete this record? NewActiveWin dictionary ''default'' form DiaLog window DiaLog ClickHit field OK NewActiveWin dictionary ''default'' form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' ' FROM IV00101 AS ['Item Master'] LEFT JOIN ( SELECT ITEMNMBR FROM POP10110 UNION SELECT ITEMNMBR FROM POP30110 UNION SELECT ITEMNMBR FROM POP10210 UNION SELECT ITEMNMBR FROM POP30210 UNION SELECT ITEMNMBR FROM SOP10200 UNION SELECT ITEMNMBR FROM SOP30300 UNION SELECT ITEMNMBR FROM IV30300 ) AS ['Used Items'] ON ['Used Items'].ITEMNMBR = ['Item Master'].ITEMNMBR INNER JOIN IV00102 AS ['Item Quantities'] ON ['Item Quantities'].ITEMNMBR = ['Item Master'].ITEMNMBR AND ['Item Quantities'].LOCNCODE = '' LEFT JOIN IV10301 AS ['Stock Count Line'] ON ['Stock Count Line'].ITEMNMBR = ['Item Master'].ITEMNMBR WHERE ['Used Items'].ITEMNMBR IS NULL AND ['Item Quantities'].QTYONHND = 0 AND ['Item Quantities'].QTYRTRND = 0 AND ['Item Quantities'].QTYINUSE = 0 AND ['Item Quantities'].QTYINSVC = 0 AND ['Item Quantities'].QTYDMGED = 0 AND ['Item Quantities'].ATYALLOC = 0 AND ['Stock Count Line'].ITEMNMBR IS NULL

If when you run the script you only get part of the macro text, you can change the query results length.