MDGP 2018 RTM Feature of the Day: Save Select Payment Settings

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 RTM; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily as well as adding my own commentary.

The series index for this series of posts is here.

The eleventh Feature of the Day is Save Select Payment Settings.

A new Payment Batch ID field has been added to the Build Payment Batch (formerly Select Cheques (or Checks)) window (Purchasing area page >> Transactions >> Build Payment Batch):

Build Payment Batch

This option allows you to save the entered restrictions for easy recall at a later date. I have mixed feelings on this one; my initial impressions was that this feature would be really useful, but when I thought more I can;t see many of my users making much use of this functionality.

Most of them only build a batch based on due date and there is no benefit saving the due date as the selection is for a specific date only.

If you do more complex batch building, such as using Vendor Class, Payment Priority, or a combination of restrictions, then this new option could save you a little time on every payment batch created.

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

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2018 RTM Feature of the Day: Sorting

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 RTM; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily as well as adding my own commentary.

The series index for this series of posts is here.

The tenth Feature of the Day is Sorting. This feature adds sorting options to five windows in Purchasing, Sales and Bank Reconciliation:

Payables Transaction Inquiry - Vendor

Payables Transaction Inquiry - Vendor

Payables Transaction Inquiry - Document

Payables Transaction Inquiry - Document

Receivables Transaction Inquiry - Customer

Receivables Transaction Inquiry - Customer

Receivables Transaction Inquiry - Document

Receivables Transaction Inquiry - Document

Select Bank Transactions

Select Bank Transactions

I like this feature a lot. Clients have been asking about being able to reorder the enquiry windows for a long time; it’s nice that I’ll soon be able to tell them that they can now sort the data.

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

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2018 RTM Feature of the Day: Rename Payment Run Windows

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 RTM; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily as well as adding my own commentary.

The series index for this series of posts is here.

The ninth Feature of the Day is a feature requested by Mariano Gomez and that is to rename the payment run windows.

So instead of them being named Check or Cheque, depending on the installed language pack, they will now be named Payment:

Purchasing menu

The windows which have been renamed are:

  • Select Payables Checks -> Build Payments Batch
  • Edit Payables Check Batch -> Edit Payment Batch
  • Edit Payables Check -> Edit Vendor Payment
  • Print Payables Checks -> Print Payment Documents
  • Post Payables Checks -> Post Payment Documents

In the UK, virtually no-one uses cheques anymore; payments are made by BACS which is a form of EFT. Renaming these windows

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

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL View to Return Purchases By Vendor By Fiscal Year

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPI did a post a few weeks ago which included a SQL view which could be used to return a list of purchases by creditors/vendors by year. I wrote the view for a client who operates a financial year which is the same as the calendar year.

However, most of my clients use different financial years, so I have also created a script which returns the same information, but links to the Financial Calendar to determine which financial, rather than calendar, year a transaction is within:

IF OBJECT_ID (N'uv_AZRCRV_PurchasesByVendorByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_PurchasesByVendorByYear
GO
CREATE VIEW uv_AZRCRV_PurchasesByVendorByYear AS
/*
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
	['Payables Transactions'].VENDORID AS 'Vendor ID'
	,['PM Vendor Master'].VENDNAME AS 'Vendor Name'
	,['PM Vendor Master'].VNDCLSID AS 'Class ID'
	,['Payables Transactions'].DOCDATE 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
		['PM Transaction OPEN File'].VENDORID
		,['Period Header'].YEAR1 AS DOCDATE
		,['PM Transaction OPEN File'].PSTGDATE
		,['PM Transaction OPEN File'].DOCNUMBR
		,['PM Transaction OPEN File'].DOCTYPE
		,['PM Transaction OPEN File'].PRCHAMNT
		,['PM Transaction OPEN File'].TRDISAMT
		,['PM Transaction OPEN File'].FRTAMNT
		,['PM Transaction OPEN File'].MSCCHAMT
		,['PM Transaction OPEN File'].TAXAMNT
		,['PM Transaction OPEN File'].DOCAMNT
	FROM
		PM20000 AS ['PM Transaction OPEN File']
	INNER JOIN
		SY40101 AS ['Period Header']
			ON
				['PM Transaction OPEN File'].DOCDATE BETWEEN ['Period Header'].FSTFSCDY AND ['Period Header'].LSTFSCDY
	WHERE
		VOIDED >= 0
	AND
		DOCTYPE <= 5
	UNION ALL
		SELECT
			['PM Paid Transaction History File'].VENDORID
			,['Period Header'].YEAR1 AS DOCDATE
			,['PM Paid Transaction History File'].PSTGDATE
			,['PM Paid Transaction History File'].DOCNUMBR
			,['PM Paid Transaction History File'].DOCTYPE
			,['PM Paid Transaction History File'].PRCHAMNT
			,['PM Paid Transaction History File'].TRDISAMT
			,['PM Paid Transaction History File'].FRTAMNT
			,['PM Paid Transaction History File'].MSCCHAMT
			,['PM Paid Transaction History File'].TAXAMNT
			,['PM Paid Transaction History File'].DOCAMNT
		FROM
			PM30200 AS ['PM Paid Transaction History File']
	INNER JOIN
		SY40101 AS ['Period Header']
			ON
				['PM Paid Transaction History File'].DOCDATE BETWEEN ['Period Header'].FSTFSCDY AND ['Period Header'].LSTFSCDY
		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
	,['Payables Transactions'].DOCDATE
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.

UPDATE: Removed Format on DOCDATE. Thanks to Tim Wappat for pointing out the error.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2018 RTM Feature of the Day: Doc Attach Notes on Inquiry Windows

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 RTM; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily as well as adding my own commentary.

The series index for this series of posts is here.

The second Feature of the Day is Notes on Inquiry windows. The maintenance windows have long allowed notes to be recorded against the window key field (e.g. Vendor ID on the Vendor Maintenance window):

Vendor Maintenance

Continue reading → MDGP 2018 RTM Feature of the Day: Doc Attach Notes on Inquiry Windows

● Categories: Dynamics, GP, Microsoft, Project Accounting ● Tags: , , , , , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2018 RTM Feature of the Day: DocAttach Available on More Windows

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 RTM; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily.

The series index for this series of posts is here.

The first Feature of the Day is Document Attachment available on more windows.

Five additional windows now have Doc Attach available via a button on the action pane:

General Ledger Transaction Entry

General Ledger Transaction Entry

Receivables Transaction Entry

Receivables Transaction Entry

Asset General Information

Asset General Information

Employee Entry

Employee Entry

I-9 form (Payroll)

I-9 form (Payroll)

It is nice to see Fixed Assets getting some Doc Attach functionality; quite a few clients have documentation or images relating to their assets, so this offers a good way to keep these with the asset.
Continue reading → MDGP 2018 RTM Feature of the Day: DocAttach Available on More Windows

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL View to Return Purchases By Vendor By Year

● Ian Grieve ●  ● 5 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 (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).
*/
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.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Dynamics GP Tech Conference 2017: Optimised Financials

● Ian Grieve ●  ● 0 Comments   ● 

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.

Click to show/hide the Dynamics GP Tech Conference 2017 Series Index

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

Dynamics GP Tech Conference 2017: New User Experience Features

● Ian Grieve ●  ● 3 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.

Click to show/hide the Dynamics GP Tech Conference 2017 Series Index

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

Report Used for the Reprint Check Remittance

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPMicrosoft Dynamics GP 2013 saw the introduction of the ability to reprint the check remittance report. This feature was a very nice one to see, as numerous clients, if not all of them, had been asking for.

At some point after this, and I am not sure in which version, the ability to email the reprinted remittance was introduced:

Payments Payable Zoom

Continue reading → Report Used for the Reprint Check Remittance

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●