Error In SmartList Excluding One Category

Microsoft Dynamics GPA client reported an error to our service desk recently when they were working with an Account Transactions SmartList which they were trying to filter to exclude a particular Account Category. When they entered the search criteria they received a string of error messages.


Microsoft Dynamics GP - GPS Error: 58
Microsoft Dynamics GP

GPS Error: 58

Continue reading

Hands On With Microsoft Dynamics GP 2015 RTM: Install Trusted Configuration

Microsoft Dynamics GPNow that Microsoft Dynamics GP 2015 RTM is available, I thought I could follow my usual pattern of posting a series of posts on how to install and configure both Dynamics GP, but also the additional products which ship with it. You can find the series index for these posts here.

The Microsoft Dynamics GP web client is as functional as the desktop one, even to the extend to which it integrates with Microsoft Office. However, whenever a user wants to integrate with Office on their machine, they first need to install a new component which allows the Silverlight web client to interact i the user’s installation of Microsoft Office.

To do this click on the padlock/globe/page icon next to the blue help icon in the bottom right of the window and then click on the Install Trusted Configuration link:

Trusted Application

Continue reading

SQL View To Select User, Company and Security Role Assignment

Microsoft Dynamics GPWe had a user recently who wanted to quickly see which users had access to which companies and the Security Roles which had been granted. While Dynamics GP does have a number of security reports, they are the standard ones which, while readable in the usual screen or printer output, cannot easily be exported to Excel. However, the client is on Microsoft Dynamics GP 2013 R2 and therefore has access to SmartList Designer.

While I could have done this entirely as a SmartList Designer report, I already had the majority of the SQL needed to generate this as a SQL View which SmartList Designer can access:

CREATE VIEW uv_PI_UserAccessAndGrantedSecurityRoles AS
	['User Master'].USERID AS 'User ID'
	,['User Master'].USERNAME AS 'Username'
	,['User Master'].USRCLASS AS 'User Class'
	,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description'
	,ISNULL(['Company Master'].INTERID, '') AS 'Intercompany ID'
	,ISNULL(['Company Master'].CMPNYNAM, '') AS 'Company Name'
	,ISNULL(['Security Assignment User Role'].SECURITYROLEID, '') AS 'Security Role ID'
	,ISNULL(['Security Roles Master'].SECURITYROLENAME, '') AS 'Security Role Name'
	SY01400 AS ['User Master']
	SY40400 AS ['Class Master']
		ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
	SY60100 AS ['User-Company Access']
		ON ['User-Company Access'].USERID = ['User Master'].USERID
	SY10500 AS ['Security Assignment User Role']
		ON ['Security Assignment User Role'].CMPANYID = ['User-Company Access'].CMPANYID
			AND ['Security Assignment User Role'].USERID = ['User-Company Access'].USERID
	SY09100 AS ['Security Roles Master']
		ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID
	SY01500 AS ['Company Master']
		ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID

Of course, the other reason I used the SQL rather than recreating entirely in SmartList Designer is that I can use this SQL in future, but a SmartList Designer is only usable on the system on which it is created as there is no import/export functionality.

MDGP 2015 Feature of the Day: Create Refreshable Excel Reports In SmartList Designer

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series of posts on the new features of, the soon to be released, Microsoft Dynamics GP 2015. As I have done for the last two releases, I intend to shadow these posts and add my own opinions on these new feature; the series index is here.

The twentieth Feature of the Day is Create Refreshable Excel Reports in SmartList Designer. This feature will enable users to create refreshable Excel reports using SmartList Designer. Reports created from SQL views may be published to the Excel report library.   

By publishing a refreshable report, the user can open the report in Excel and the data will always be up to date:

I like the look of this feature. The out-of-the-box Excel Reports are of somewhat limited utility as they return all data for the report which means that for a transaction SmartList there can be vast sums of data returned. But with these SmartList Designers you should be able to define a filter in the report so it only includes, for example, the current or previous months transactions.

The roles targeted by this feature are:

  • All

Click to show/hide MDGP 2015 Feature of the Day Series Index

SmartList To Show Transactions Included On VAT 100 Return

Microsoft Dynamics GPOne of the problems with the standard Tax Reports (Administration area page >> Reports >> Company >> Taxes) in Microsoft Dynamics GP is that they will include only items requested for the date range, but the VAT 100 Return itself will select everything in the specified date range and also anything dated before this period which has not been included in previous VAT Returns.

This is a problem as it means you can not run a report before generating the VAT Return to see what would be picked up. However, a little thought and consideration of how the SmartList wildcards work and a workable solution presented itself.

In SmartList select the Tax Detail Transactions SmartList Favourite under Company and click on the Search button:

Search Tax Detail Transactions

Under Search Definition 1 enter Tax Return ID in the Column Name. Set the Filter to begins with and enter [^2] in the Value.

This search definition will return all transactions which do not have a Tax Return ID beginning with a 2; this assumes you name your VAT Returns along the lines of 2014-08 to have them sorted by date. If, as some clients I have seen, you create your VAT Returns with the month’s long, or short, name first then replace the 2 with JFMASOND and all items not starting with one of those letters will be returned.

Using SmartList Designer To Create Vendor EFT SmartList

One of the really nice new features in Microsoft Dynamics GP 2013 SP2 is the SmartList Designer. This is, to an extent, a cut down replacement of SmartList Builder, which includes much, although not all, of the functionality and is more user friendly.

It also affords the opportunity to all clients to create a report which has long been missing from Microsoft Dynamics GP; a Vendor EFT SmartList which shows the EFT details against the Vendor Remit To Address.

In this post I am going to step through the process of creating this report.

To start, open SmartList (Microsoft Dynamics GP menu >> SmartList) and you will see two new buttons at the left of the toolbar; New and Modify (ringed in red):


Continue reading

MDGP 2013 R2 Feature of the Day: SmartList Designer Go To’s

The Inside Microsoft Dynamics GP blog started a series of Microsoft Dynamics GP 2013 R2 Feature of the day posts the other day. As they did with the Microsoft Dynamics GP 2013 Feature of the Day posts they are doing them as short posts containing a PowerPoint slide show. I am translating these from the PowerPoints into posts; you can find my series index here.

The first Feature of the Day covered is SmartList Designer Go To’s. SmartList Designer was introduced in Microsoft Dynamics GP 2013 SP2 and providd similar, although a much cut down, functionality of SmartList Builder.

This new feature is GP 2013 R2 adds a very important piece of missing functionlity; the ability to add Go To’s to SmartLists created in SmartList Designer.

When a user creates a new SmartList based off an existing SmartList, the GoTo’s that already exist are copied over to the new list created; when a user creates a new SmartList, there are three types of GoTo’s which can be added to the new SmartList: another SmartList, a navigation list or a web page:

SmartList Designer - Go To

Continue reading

Microsoft Dynamics GP 2013 R2 Coming Soon

The next version of Microsoft Dynamics GP, 2013 R2, is going to be released soon. I can hardly wait, but, unfortunately, I have to :(

The Dynamics GP Support and Services Blog have posted an article today outlining their intended blog post schedule where they are going to run through the upgrade process and all of the new features which are due in the coming version.

  • April 29 – Lifecycle
  • May 1 – Upgrade Planning
  • May 6 – Known Upgrade Issues
  • May 8 – Version Checks & Upgrades
  • May 13 – Database Upgrade
  • May 15 – Modified Dictionary Upgrade
  • May 20 – Upgrade Troubleshooting
  • May 22 – GP is upgraded – what’s next?
  • May 27 – Upgrading Web Client
  • May 29 – Tables Changes in R2 – Review the SDK
  • June 3 – Identity Management
  • June 4 – Azure Backups
  • June 5 – New Workflow
  • June 10 – Requisition Management
  • June 11 – OLE Document Attach
  • June 12 – Human Resource Time Entry
  • June 17 – Project Accounting Time Entry
  • June 18 – General Ledger
  • June 19 – Payables Management
  • June 24 – Receivables Management
  • June 25 – Bank Reconciliation
  • June 26 – Analytical Accounting
  • July 1 – Fixed Assets
  • July 2 – Sales Order Processing
  • July 3 – Inventory Control
  • July 8 – Purchase Order Processing/Encumbrance Management
  • July 9 – Email or Print any report in Word
  • July 10 – SmartList Designer 2.0
  • July 15 – Office 365
  • July 16 –Take Company Offline
  • July 17 – Ribbons in Desktop

Review of Microsoft Dynamics GP 2013 Reporting by David Duncan and Christopher J Liley

I have received a copy of the newly published Microsoft Dynamics GP 2013 Reporting, by David Duncan and Chrisopher J Liley, from Packt Publishing to review. This is an update of a previous book the pair wrote for Microsoft Dynamics GP 2010:

Microsoft Dynamics GP 2013 Reporting

The book covers an extensive range of the reporting tools in, or available with, Microsoft Dynamics GP 2013:

  • SmartList Builder including Excel Report Builder
  • Report Writer and Word Templates
  • Reporting Services Reports
  • Analysis Cubes
  • Management Reporter

Continue reading

Microsoft Dynamics GP SmartList Default Visible To Company

In July, as part of his weekly MS Connect suggestion series, Mark Polino raised the issue of SmartList Favourites and the default Visible To which is set to System.

Mark’s suggestion was to have the default changed to User which would leave a generally tidier list. To be honest I’d rather have a setting which allowed the default to be chosen by each client. The reason for this is we’re currently in the middle of an upgrade project where a few Microsoft Dynamics GP systems will be merged into one and it would be good to have the default Visib le to set to Company.

Following on from my recent post on a fixed width SmartList left pane I decided to have a fiddle and see if I could force the default to something else. And it turns out with some simple VBA that you can do exactly that;


Continue reading