Month: August 2017

SQL View to Return Purchases By Vendor By Year

Ian Grieve2 minute read5 Comments

Microsoft Dynamics GPThere are a number of reports which I keep getting asked for. One of them is a list of the top X number of creditors (or vendors to my American readers).

Rather than keep on reinventing the wheel, I have created a SQL view which sums up the transactions in each year for all vendors:

/*
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).
*/
IF OBJECT_ID (N'uv_AZRCRV_PurchasesByVendorByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_PurchasesByVendorByYear
GO
CREATE VIEW uv_AZRCRV_PurchasesByVendorByYear AS
SELECT
	['Payables Transactions'].VENDORID AS 'Vendor ID'
	,['PM Vendor Master'].VENDNAME AS 'Vendor Name'
	,['PM Vendor Master'].VNDCLSID AS 'Class ID'
	,FORMAT(['Payables Transactions'].DOCDATE, 'yyyy') AS 'Year'
	,CASE WHEN ['PM Vendor Master'].VENDSTTS >= 1 THEN
		'Active'
	WHEN ['PM Vendor Master'].VENDSTTS >= 2 THEN
		'Inctive'
	ELSE
		'Temporary'
	END AS 'Status'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].PRCHAMNT ELSE ['Payables Transactions'].PRCHAMNT * -1 END) AS 'Purchases Amount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TRDISAMT * -1 ELSE ['Payables Transactions'].TRDISAMT END) AS 'Trade Discount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].FRTAMNT ELSE ['Payables Transactions'].FRTAMNT * -1 END) AS 'Freight'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].MSCCHAMT ELSE ['Payables Transactions'].MSCCHAMT * -1 END) AS 'Miscalleneous'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TAXAMNT ELSE ['Payables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].DOCAMNT ELSE ['Payables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
	(SELECT
		VENDORID
		,DOCDATE
		,PSTGDATE
		,DOCNUMBR
		,DOCTYPE
		,PRCHAMNT
		,TRDISAMT
		,FRTAMNT
		,MSCCHAMT
		,TAXAMNT
		,DOCAMNT
	FROM
		PM20000
	WHERE
		VOIDED >= 0
	AND
		DOCTYPE <= 5
	UNION ALL
		SELECT
			VENDORID
			,DOCDATE
			,PSTGDATE
			,DOCNUMBR
			,DOCTYPE
			,PRCHAMNT
			,TRDISAMT
			,FRTAMNT
			,MSCCHAMT
			,TAXAMNT
			,DOCAMNT
		FROM
			PM30200
		WHERE
			VOIDED >= 0
		AND
			DOCTYPE <= 5
	) AS ['Payables Transactions']
LEFT JOIN
	PM00200 AS ['PM Vendor Master']
		ON
			['Payables Transactions'].VENDORID = ['PM Vendor Master'].VENDORID
GROUP BY
	['Payables Transactions'].VENDORID
	,['PM Vendor Master'].VENDNAME
	,['PM Vendor Master'].VNDCLSID
	,['PM Vendor Master'].VENDSTTS
	,FORMAT(['Payables Transactions'].DocDate, 'yyyy')
GO
GRANT SELECT ON uv_AZRCRV_PurchasesByVendorByYear TO DYNGRP
GO

The view can easily be plugged into SmartList Designer, SmartList Builder, a refreshable Excel Report, a SQL Server Reporting Services report or any other type of reporting tool.

Copy Workflow Doesn’t Copy Notification Settings

Ian Grieve2 minute read2 Comments

Microsoft Dynamics GPI made a discovery the other day about the Copy Workflow functionality in Workflow Maintenance (Administration area page >> Setup >> Company >> Workflow >> Workflow Maintenance). I have been working with a client to create a new Purchase Order Processing workflow. After the client did UAT on a test system, we needed to transfer the workflow to the live system.

We did this by copying the Workflow from the test company to the live one using the Coopy functionality. After the copy, users did a little testing on live and found that originators were not receiving notifications, but users were receiving the task assignment emails.

This was not the first email issues we had encountered during the project, but turned out to be the simplest to resolve (although it took longer than it should to identify).

To correct the issue, open the Workflow and click the expansion arrow next to the Send notifications for completed actions checkbox and remark the checkboxes for the notifications which should be sent:

Workflow Email Notification Maintenance

Customising Dynamics GP Workflow Emails: Adding An Image

Ian Grieve2 minute read5 Comments

Microsoft Dynamics GPI thought I had posted about this a littler while ago as part of my Customising Dynamics GP Workflow Emails series, but it seems I missed it. The reason it came back to mind, was when I was onsite with a client doing some training on Workflow, they asked if it was possible to add a company logo to a Workflow task assignment email.

It is. In fact, you can add any image you want to a workflow email. In this example, though, I am going to stick with using a company logo.

In the Message Setup (Administration area page >> Setup >> Company >> Workflow >> Message Setup) window load the message you want to amend and simply add the HTML image tag:

Message Setup

Continue reading “Customising Dynamics GP Workflow Emails: Adding An Image”

Dynamics GP Tech Conference 2017: Power BI Enhancements

Ian Grieve2 minute read0 comment

Microsoft Dynamics GPMicrosoft Dynamics GPThis post is part of the series I am doing on the Dynamics GP Tech Conference 2017.

Power BI is more the area of Belinda Allen or Mark Polino than mine, but I’ll be getting more of a look at it soon as I have one of my team about to take a more in depth look, so it is nice to hear there will be more new stuff coming in the next version of Dynamics GP:

  • Support for paging in OData Services
  • Support for filtering in OData Services
  • Support for OData v4
  • GP Power BI Content Pack – I need more information on this, but it sounds like a set of pre-packaged reports which will be welcome.

Dynamics GP Tech Conference 2017: Comprehensive Doc Attach

Ian Grieve2 minute read0 comment

Microsoft Dynamics GPThis post is part of the series I am doing on the Dynamics GP Tech Conference 2017.

The Document Attachment module has been extended into the action pane in quite a few new windows:

  • Document Attachment on Receivables Transaction Entry
  • Document Attachment on General Ledger Entry
  • Document Attachment on Employee Inquiry
  • Document Attachment in I-9 Form
  • Display Vendor notes on Vendor Inquiry
  • Display Customer notes on Customer Inquiry
  • Display Project Notes in Project Inquiry and PA Project Inquiry
  • Document Attachment in Asset General Information
  • Option to attach new documents in Inquiry windows – there is a setting in the Document Attachment Setup winodw to enable this (and to require a password to be entered).

Dynamics GP Tech Conference 2017: Optimised Human Resources and Payroll

Ian Grieve1 minute read0 comment

Microsoft Dynamics GPMicrosoft Dynamics GPThis post is part of the series I am doing on the Dynamics GP Tech Conference 2017.

Not modules I know anything about, as they are very US and Canada focused, but there are some enhancements coming to Human Resources and Payroll:

  • Turn off garnishment reports in posting setup.
  • Roll down payroll setup options from setup to inactive records.
  • Change department code in all payroll files, including history.
  • Restrict inactive Deduction and Benefit codes in lookups

Dynamics GP Tech Conference 2017: Optimised Financials

Ian Grieve3 minute read0 comment

Microsoft Dynamics GPThis post is part of the series I am doing on the Dynamics GP Tech Conference 2017.

The Financials modules have some enhancements coming which should be of benefit:

  • Copy user access across AA transaction dimension codes – this should make maintaining AA dimension code access easier.
  • Email single statement from customer card and enquiry – handy when you only want to send a single statement to the customer on the phone, rather than to a range of them.
  • PO Generator opens PO List with the new PO’s filter.
  • PO Number display on Requisition List for originator – getting easy access to the PO number for PR orinators will be a good new addition; it will save me writing SmartLists for everyone based off this view.
  • Print Purchase Requisition – I have mixed opinions. Workflow can handle the sending of requisition details to an approver anyway, and I can’t really think of another reason to output one?
  • Display Hold status on Sales Transaction Entry – more information is never a bad thing.
  • One check per invoice settings – allows for more granular control than the setting in the Select Payments window.
  • Save Select Payments settings – are you tired of having to configure your payment run manually every time? Well, Microsoft’s got you covered with this feature.

Hands On With the GP Power Tools – System Module: Setup Backup and Restore

Ian Grieve2 minute read0 comment

Winthrop DCThis post is part of the Hands On With the GP Power Tools (GPPT) – System Module series in which I am taking a hands on look at the various tools offered by GPPT.

The Setup Backup and Restore (GP Power Tools area page >> Routines >> Setup Backup and Restore) window can be used to make, or restore, a complete backup of the GPPT settings:

Setup Backup and Restore

This window should be used with care, as a restore will completely replace the setup with that in the xml file being restored.

There is some overlap between this and the Configuration Export/Import, but this one will do a complete export and import, whereas the Configuration Export/Import allows for a far more granular approach.

This tool, is intended to allow for full backups of the settings to be made on the system you’re working ion and restored if required; the other, is intended to be used for transferring settings between systems.

Dynamics GP Tech Conference 2017: New User Experience Features

Ian Grieve3 minute read3 Comments

Microsoft Dynamics GPThis post is part of the series I am doing on the Dynamics GP Tech Conference 2017.

One of the areas being focused on in the 2018 version of Microsoft Dynamics GP, is the user experience. The announced enhancements are:

  • Add sort to Receivables Transaction Inquiry – by Customer
  • Add sort to Receivables Transaction Inquiry – by Document
  • Add sort to Payables Transaction Inquiry – by Vendor
  • Add sort to Payables Transaction Inquiry – by Document
  • System password is remembered for the duration of the user session – the only downside is if you need to grant access to a window such as the VAT Return to a user, but then need to monitor them to make sure they then close Dynamics GP before continuing with other work.
  • Rename of Payables windows to say Payments instead of Checks – I can’t find the post, but this was a request from Mariano Gomez and is well overdue. Pretty much all payments in the UK are by EFT, so it was getting seriously embarrassing having all the windows with Cheque in the name (UK language pack changes it from Check).
  • Autocomplete added to the web client
  • SmartList Favourites – unique password protection – this is by popular request after the password for SmartList Favourites globally was added in GP 2016 R2.
  • SmartList shortcut on toolbar in web client
  • Bank Reconciliation maximise window in web client
  • Adding additional sorting in Bank Reconciliation window
  • Update help icons to link to tagged online support documents

The enhancements to the user experience listed above should be of benefit to users of Dynamics GP, and follows Microsoft’s ethos of evolution, not revolution in the improvements they are making.

Dynamics GP Tech Conference 2017: New Features In Workflow

Ian Grieve4 minute read3 Comments

Microsoft Dynamics GPThis post is part of the series I am doing on the Dynamics GP Tech Conference 2017.

There have been new features in other areas, which I will come back to, but Workflow is sort of “my thing”, so I’m going to mention the new features which are being added to Workflow 4.0 (I apparently missed the boat somewhere on the name change to Workflow 3.0?).

The coming new features are:

  • Reminder emails for Workflow – this is a feature which has been requested of ym by a couple of clients recently, so nice that I’ll be able to go back and tell them it will be in the next version.
  • Copy Workflow Step – nice to see this one added as it was one of my suggestions on MS Connect.
  • Reporting for Workflow – I’m not sure what is coming in this, but it would be nice to have some default reports showing the history of documents as they passed through the Workflow.
  • Payables Transaction Workflow – additional fields
  • PO Workflow Messages – additional fields – there are never enough fields available for the Workflow, so nice to know more will be available by default.
  • Add EFT/Bank Details to Vendor Approval Workflow – if this one is as it sounds, that will be fantastic as so many clients want this functionality. I think it only took so long as EFT isn’t anywhere near as big in the US as it is in the UK.
  • GL Account Workflow Approval – I have a couple of clients who can ditch some customisations with this one. And a few more who will be delighted so see this added.
  • Purchase Receiving Workflow – I have a few customers waiting for this one to be introduced.
  • Purchase Enter/Match Invoice Workflow – as above, a few clients are waiting for this one.

I know we can’t have everything we want, and the above are good additions to Workflow, but I still want more!