Category: Microsoft

Is there a POP Historical Received Not Invoiced Report Available?

Ian Grieve2 minute read0 comment

Microsoft Dynamics GPI’ve had a couple of people recently ask if it is possible to get a historical version of the Shipments Received but not invoiced* SmartList favourite (located under Purchasing » Receivings Line Items).

Within SmartList, this is not possible as the point-in-time calculations required for a historical report are too complex for a SmartList, even one driven by a view.

While historically this report wasn’t available in Microsoft Dynamics GP, this changed in the Microsoft Dynamics GP 2015 R2 release when a Reporting Services Report version of a Historical Received Not Invoiced Report was introduced. It seems though that many people are remain unaware of this new Historical RNI report.

As long as you have the Reporting Services Reports from the Reporting Tools Setup window deployed you can launch the report from the Purchasing series Reporting Services Reports navigation pane:

Historical Received Not Invoiced

This report allows you to enter criteria including a Date as of.

PrintFriendlyLinkedin

Currency ID Missing From Batches Integrated Via eConnect

Ian Grieve2 minute read0 comment

Microsoft Dynamics GPWe’ve been doing some work with a client recently creating a number of integrations in SmartConnect. One of these was creating General Journals into a consolidation company from other Microsoft Dynamics GP databases.

Everything looked fine from a SmartConnect perspective; success reported for the integration and no warnings or errors. However, when we looked at the resulting journal, there was no Currency ID:

General Transaction Entry showing blank Currency ID field

The problem here isn’t actually a SmartConnect one, but an eConnect one which has been known for a long time. There are a number of reports of this, but the one we found when researching was on the Dynamics Blogger.

The solution is to create a chequebook (checkbook) for the Functional Currency.

The company we were importing into, didn’t have one as only the GL was going to be used; once the chequebook was created, the integration worked fine.

PrintFriendlyLinkedin

Implementing SmartList Builder: Review

Ian Grieve2 minute read0 comment

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

Of all of the ISV products I have used with Microsoft Dynamics GP, SmartList Builder is the one I have worked with the longest, and is the one I like the most (looking back I’m surprised it’s taken me this long to do a series like this).

It is easy to use, flexible and far better at creating new SmartLists than the in-built SmartList Designer product which Micro created when they passed SmartList Builder back to eOne Solutions.

If you like SmartLists, then I would encourage you to take a look at SmartList Builder; it does take a somewhat technical user to create and maintain SmartLists through it, but they are easy to create and much more flexible than those possible through SmartList Designer. The hardest job is sometimes discovering how the data hangs together.

There are resources such as the GP Tables section of Victoria Yudin’s website or my azurecurve|Microsoft Dynamics GP Table Reference which you can use to find the tables you need.

PrintFriendlyLinkedin

Implementing SmartList Builder: Security

Ian Grieve2 minute read1 Comment

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

The final element to readying SmartList Builder for use is to configure security so that users without the POWERUSER* role can access it. While this step isn’t absolutely necessary, I would encourage people not to over use the POWERUSER* role.

A better approach would be to create a Super User role instead. Both this role and standard user roles will need to have access to the SmartList Builder windows granting.

This is easy to do, as SmartList Builder ships with some default security tasks and a role:

Security Role Setup

Assign this role to the relevant user or assign the tasks to existing roles and user will, next time they login, be able to access the SmartList Builder window.

PrintFriendlyLinkedin

Implementing SmartList Builder: Register

Ian Grieve2 minute read0 comment

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

If you’re testing SmartList Builder, you can use it in the Famrikam Sample company without entering a registration key. However, yo use it on a normal company, even a test one, you will need to input the key.

To do this, log into Microsoft Dynamics GP as a user with the POWERUSER* role and open the SmartList Builder Register window (Administration area page » Setup » SmartList Builder » Register SmartList Builder) and enter the key in the Registration Key field:

SmartList Builder Register

Click Save to sdave the key and close the window.

PrintFriendlyLinkedin

Implementing SmartList Builder: SmartList Builder vs. SmartList Designer

Ian Grieve3 minute read0 comment

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

As mentioned in the last post SmartList Builder was directly sold by Microsoft under a licensing agreement for many years until, with the launch of Microsoft Dynamics GP 2013 SP2, the product was released back to the original ISV; a Fargo based ISV called eOne S0lutions.

I understand this was because the new top person in charge of Microsoft Dynamics GP did not like licensing products from the ISVs and release quite a few back to the original developer. However, this left a gap in Microsoft Dynamics GP in that users could no longer create new SmartLists. And so, SmartList Designer was created.

In theory it fulfils the same job as SmartList Builder; you can create new SmartLists by joining tables together or by linking in a SQL view. When it was first released, I took a look at it as an alternative to SmartList Builder on the basis it was free as opposed to needing to buy SmartList Builder.

However, in every area, it is an inferior product. It is not as functional and it is not as easy to use.

Continue reading “Implementing SmartList Builder: SmartList Builder vs. SmartList Designer”

PrintFriendlyLinkedin

Implementing SmartList Builder: What is SmartList Builder?

Ian Grieve5 minute read0 comment

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

Before launching into the installation of SmartList Builder, I thought it might be worthwhile to do a post on what it is. All users of Microsoft Dynamics GP will be familiar with SmartList; this is the flexible reporting tool which allows you to generate a report showing a list of master records or transactions. Each series has a set of SmartLists available by default, with predefined favourites where search criteria or columns are supplied already configured.

SmartList favourites can be customised and new ones created quite easily, but new SmartLists themselves cannot be created. To create a new SmareList, you need a tool such as SmartList Builder or, in more recent years, SmartList Designer. In this series, I am focussing on SmartList Builder as this is an additional product available from an ISV for Microsoft Dynamics GP.

Back in 2003 when I started working with Microsoft Dynamics GP, SmartList Builder was available direct from Microsoft. SmartList Builder was originally developed by eOne Solutions and then licensed by Microsoft; in the run up to the release of Microsoft Dynamics GP 2013 SP2, the licensing deal for SmartList Builder was ended and maintenance of the product reverted back to eOne Solutions.

SmartList Builder the product, includes four utilities:

  1. SmartList Builder – SmartList Builder allows you to create both brand new SmartLists or modify existing SmartList. You can link up to 32 tables together. Tables can be standard GP tables, any of the Third Party (ISV) tables, any SQL table, SQL views or SQL Scripts, other SmartLists or Extender resources.
  2. Excel Report Builder – Excel Report Builder generates an Excel Spreadsheet with a live connection back to GP or any other data you include. This means you have live refreshable reports, anytime you need them. You can give all your data from GP to anyone in the organization via Excel — without buying new, full-user licenses of Dynamics GP.
  3. Drill Down Builder – Drill Down Builder completes the functionality of Excel Report Builder by letting you drill from your spreadsheet back into that same record within Dynamics GP. Where Microsoft hard codes a drill down — eOne makes it completely configurable to allow you to drill down to any screen or SmartList in Dynamics GP. This is an essential part of bringing the most common business tools together. Working in GP and Excel all day — it only makes sense to auto-switch between one and the other.
  4. Navigation List Builder – Navigation List Builder allows you to publish your SmartList into the Navigation List user interface.

The standard installation of the SmartList Builder product, gets you all four of the above utilities.

PrintFriendlyLinkedin

Implementing SmartList Builder: Series Index

Ian Grieve2 minute read13 Comments

eOne SolutionsI’ve posted series on a few different ISV addons for Microsoft Dynamics GP over the last few years, but, somewhat ironically I guess, have not posted all that much about the addin I’ve used the most over the years.

That addin is SmartList Builder from eOne Solutions.

SmartList Builder allows you to create both brand new SmartLists or modify existing SmartList. You can link up to 32 tables together. Tables can be standard GP tables, any of the Third Party (ISV) tables, any SQL table, SQL views or SQL Scripts, other SmartLists or Extender resources.

This series is going to cover the implementation of SmartList Builder; I’ll take a look at report builder in a future series.

Implementing SmartList Builder
What is SmartList Builder?
SmartList Builder vs. SmartList Designer
Download and Install
Register
Security
Review
PrintFriendlyLinkedin

SQL Function to Remove Alphanumeric Characters

Ian Grieve3 minute read2 Comments

Microsoft Dynamics GPI created a customisation recently for a client which would generate a Vendor ID based on the name, by removing alphanumeric characters. In order to make it as flexible as possible, I created the function to accept a parameter for type which will cause the function to strip different characters:

  • A – leaves alpha characters only.
  • N – leaves numeric characters only.
  • AN – leaves alphanumeric characters.

The second parameter is the string which should have the characters stripped:

IF OBJECT_ID (N'uv_AZRCRV_StripCharacters', N'FN') IS NOT NULL
	DROP FUNCTION uv_AZRCRV_StripCharacters
GO

CREATE FUNCTION uv_AZRCRV_StripCharacters(@Type VARCHAR(100), @String VARCHAR(MAX))
	RETURNS VARCHAR(MAX)
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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).
*/
BEGIN
	DECLARE @PatIndex VARCHAR(20)
	IF (@Type = 'Alpha' OR @Type = 'A')
		SET @PatIndex = '%[^a-z]%'
	IF (@Type = 'Numeric' OR @Type = 'N')
		SET @PatIndex = '%[^0-9]%'
	IF (@Type = 'AlphaNumeric' OR @Type = 'AN')
		SET @PatIndex = '%[^a-z0-9]%'

	WHILE PATINDEX(@PatIndex, @String) < 0
		SET @String = STUFF(@String, PATINDEX(@PatIndex, @String), 1, '')

	RETURN @String
END

GRANT EXECUTE ON uv_AZRCRV_StripCharacters TO DYNGRP
GO
PrintFriendlyLinkedin