MDGP 2016 R1 Feature of the Day: SmartLists from Favorites

● Ian Grieve ●  ● 0 Comments  ● 

As mentioned in the previous post, Microsoft Dynamics GP 2013 SP2 saw the introduction of SmartList Designer and have been gradually improving it since. This new feature allows a user to create a new SmartList from a favourite, instead of just from a SmartList Object:

SmartList

SmartList Designer

The main features of are:

  • A user can create a new SmartList from a favourite using Designer
  • The user doesn’t have to remove all extra columns from the default SmartList

This new feature will ease the creation of a SmartList Object simialr to an existing favourite instead of a copy of the default SmartList Object to which the favourite belongs.

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

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

MDGP 2016 R1 Feature of the Day: Export/Import in SmartList Designer

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GP 2013 SP2 saw the introduction of SmartList Designer. It was reasonably useful, but nowhere near as useful as SmartList Builder. Little by little Microsoft are making SmartList Designer more useful. Microsoft Dynamics 2016 R1 actually makes it quote a bit more useful by adding the ability to export and import SmartLists:

Export/Import in SmartList Designer

Import in SmartList Designer

Export in SmartList Designer

One f the problems with SmartList Designer was that you had to create the SmartList on the server on which you wanted it; you could not develop it on a test system or development system and if, as a partner, you wanted to give it to another client you had to recreate it. This is why I often created SQL Views and plugged them in rather than creating the SmartList in the Designer.

I’ll need to do some testing to see how good the export/import functionality is, but this feature may allow us to put together SmartLists on our development system and then export them for clients.

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

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

MDGP 2016 R1 Feature of the Day: Excel Export as Numbers

● Ian Grieve ●  ● 0 Comments  ● 

This feature of the day puzzles me somewhat as I would swear this was a feature introduced within the last couple of versions. It is the export of numbers as numbers from a SmartList:

Excel Export as Numbers

As I mentioned in the introductory paragraph, this is a feature I believe was already introduced, but upon further reflection this may be a slightly different new feature.

The previous feature I am thinking of (but can’t find a specific announcement) exported the numbers to five decimal places and without a currency symbol. The example given in the screenshot, above, has a currency symbol and is to two decimal places.

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

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

SQL View To Allow Customer/Item Link In SmartList Builder

● Ian Grieve ●  ● 4 Comments  ● 

Microsoft Dynamics GPOn this one I am open to someone telling me that there is actually a way to do this out of the box, but I haven’t been able to find it. On a SmartList I was creating, I needed to pull the customer item number and description through to a SOP transaction SmartList, but couldn’t find any table to link with.

As a workaround, I created this view which contains the required link using the CUSTNMBR from SOP10100 (Sales Transaction Work) and the ITEMNMBR from SOP10200 (Sales Transaction Amounts Work):


CREATE VIEW uv_AZRCRV_CustomerItemLink 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
		SOP102.SOPNUMBE
		,SOP102.SOPTYPE
		,SOP102.LNITMSEQ
		,SOP101.CUSTNMBR
		,SOP102.ITEMNMBR
	FROM
		SOP10200 AS SOP102 WITH (NOLOCK)
	INNER JOIN
		SOP10100 AS SOP101 WITH (NOLOCK)
			ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE
	INNER JOIN
		SOP60300 AS SOP603 WITH (NOLOCK)
			ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GO

GRANT SELECT ON uv_AZRCRV_CustomerItemLink TO DYNGRP
GO
● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL View To Return Purchasing Transactions With Multicurrency

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPA client I was working with recently was using two SmartLists, one for Payables Transactions and the other from Purchase Order Transactions, to generate a listing of purchasing transactions. However, they had to do manual fiddling around in Excel to get some of the formatting correct and didn;t have all of the information they wanted. Always up for a challenge, I had a go at producing a view we could plug in using SmartList Designer which would give them the information they wanted in the format the needed.

The below is the SQL view which was produced. It includes the PO and Receipt Numbers as well as the Originating Currency (with symbol) and exchange rate; the Functional Currency was left without a symbol so it can be easily totalled in Excel. Where the transaction does not have an Originating Currency I am outputting the Functional Currency.

/*
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).
*/
CREATE VIEW uv_AZRCRV_PurchasingTrxList AS
	SELECT
		PM.VCHRNMBR AS 'Voucher Number'
		,CASE WHEN PM.DOCTYPE = 1 THEN
			PM401.PMTRXDSC_1
		WHEN PM.DOCTYPE = 2 THEN
			PM401.PMTRXDSC_2
		WHEN PM.DOCTYPE = 3 THEN
			PM401.PMTRXDSC_3
		WHEN PM.DOCTYPE = 4 THEN
			PM401.PMTRXDSC_4
		WHEN PM.DOCTYPE = 5 THEN
			PM401.PMTRXDSC_5
		WHEN PM.DOCTYPE = 6 THEN
			PM401.PMTRXDSC_6
		WHEN PM.DOCTYPE = 7 THEN
			PM401.PMTRXDSC_7
		WHEN PM.DOCTYPE = 8 THEN
			PM401.PMTRXDSC_8
		ELSE
			'Unknown'
		END AS 'Document Type'
		,PM004.DOCTYPE
		,CASE WHEN PM004.DCSTATUS = 1 THEN
			'Work'
		WHEN PM004.DCSTATUS = 2 THEN
			'Open'
		WHEN PM004.DCSTATUS = 3 THEN
			'History'
		ELSE
			'Unknown'
		END AS 'Document Status'
		,PM004.DCSTATUS
		,PM.PORDNMBR AS 'PO Number'
		,ISNULL(POP303.POPRCTNM,'') AS 'Receipt Number'
		,PM.VENDORID AS 'Creditor Name'
		,PM002.VENDNAME AS 'Creditor ID'
		,CONVERT(VARCHAR,PM.DOCDATE,103) AS 'Document Date'
		,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END
			+ ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) 
			+ CAST(CAST(ISNULL(MC.OPURAMT,PM.PRCHAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Purchasing Amount'
		,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END 
			+ ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) 
			+ CAST(CAST(ISNULL(MC.ORTAXAMT,PM.TAXAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Tax Amount'
		,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END 
			+ ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) 
			+ CAST(CAST(ISNULL(MC.ORDOCAMT,PM.DOCAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Document Amount'
		,CASE WHEN MC.XCHGRATE IS NULL THEN 0 ELSE MC.XCHGRATE END AS 'Exchange Rate'
		,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.PRCHAMNT ELSE PM.PRCHAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Purchasing Amount'
		,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.TAXAMNT ELSE PM.TAXAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Tax Amount'
		,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.DOCAMNT ELSE PM.DOCAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Document Amount'
	FROM
		(
		-- Transaction History
		SELECT
			VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT
		FROM
			PM30200 WITH (NOLOCK)
		UNION ALL
			-- Transaction Open
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT
			FROM
				PM20000 WITH (NOLOCK)
		UNION ALL
			-- Transaction Work
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT
			FROM
				PM10000 WITH (NOLOCK)
		UNION ALL
			-- Payment Work
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,'',VENDORID,0,0,CHEKTOTL
			FROM
				PM10300 WITH (NOLOCK)
		UNION ALL
			-- Manual Payment Work
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,'',VENDORID,0,0,DOCAMNT
			FROM
				PM10400 WITH (NOLOCK)
		) AS PM
	 INNER JOIN
		PM00400 PM004 WITH (NOLOCK)
			 ON PM004.CNTRLNUM = PM.VCHRNMBR AND PM004.DOCTYPE = PM.DOCTYPE
	 INNER JOIN
		PM00200 AS PM002 WITH (NOLOCK)
			 ON PM002.VENDORID = PM.VENDORID
	 LEFT JOIN
		MC020103 AS MC WITH (NOLOCK)
			 ON MC.VCHRNMBR = PM.VCHRNMBR AND MC.DOCTYPE = PM.DOCTYPE
	 LEFT JOIN
		POP30300 AS POP303 WITH (NOLOCK)
			 ON POP303.VCHRNMBR = PM.VCHRNMBR
	 LEFT JOIN
		DYNAMICS..MC40200 AS MC402 WITH (NOLOCK)
			 ON MC402.CURRNIDX = MC.CURRNIDX
	 INNER JOIN
		MC40000 AS MC400 WITH (NOLOCK)
			 ON MC400.FUNLCURR = MC400.FUNLCURR
	 INNER JOIN
		DYNAMICS..MC40200 AS MC402F WITH (NOLOCK)
			 ON MC402F.CURRNIDX = MC400.FUNCRIDX
	 INNER JOIN
		PM40100 AS PM401 WITH (NOLOCK)
			 ON PM401.UNIQKEY = PM401.UNIQKEY
	ORDER BY PM.DOCDATE
 GO 

GRANT SELECT ON uv_AZRCRV_PurchasingTrxList TO DYNGRP
GO
● Categories: Dynamics, GP, Microsoft, Multicurrency, SmartList, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL View For Payables Transaction Distribution Accounts

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPOne of the odd limitations in SmartList is the inability to run a report and get the distribution accounts for payables transactions; there is a field available for the different distributions, but these fields are from the Vendor Card, not the transaction. This view was the result of a query from someone; it returns the account number and description for the payables distributions.

CREATE VIEW uv_AZRCRV_PayablesTransactionDistributionAccounts AS

SELECT
	['PM Distribution WORK OPEN HIST'].VCHRNMBR AS 'Voucher Number'
	,CASE WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 1 THEN
		'Cash'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 2 THEN
		'Payable'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 3 THEN
		'Discount Available'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 4 THEN
		'Discount Taken'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 5 THEN
		'Finance Charge'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 6 THEN
		'Purchase'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 7 THEN
		'Trade Discount'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 8 THEN
		'Miscellaneous Charge'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 9 THEN
		'Freight'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 10 THEN
		'Taxes'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 11 THEN
		'Writeoffs'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 12 THEN
		'Other'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 13 THEN
		'GST Disc'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 14 THEN
		'PPS Amount'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 16 THEN
		'Round'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 17 THEN
		'Realized Gain'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 18 THEN
		'Realized Loss'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 19 THEN
		'Due To'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 20 THEN
		'Due From'
	END AS 'Distribution Type'
	,['PM Distribution WORK OPEN HIST'].DistRef AS 'Distribution Reference'
	,['Account Index Master'].ACTNUMST AS 'Account Number'
	,['Account Master'].ACTDESCR AS 'Account Description'
FROM
	(SELECT
		VCHRNMBR
		,DISTTYPE
		,DSTINDX
		,DistRef
	FROM
		PM10100 AS ['PM Distribution WORK OPEN HIST']
	UNION ALL
		SELECT
			VCHRNMBR
			,DISTTYPE
			,DSTINDX
			,DistRef
		FROM
			PM30600 AS ['PM Distribution History File']
	) AS ['PM Distribution WORK OPEN HIST']
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON ['Account Index Master'].ACTINDX = ['PM Distribution WORK OPEN HIST'].DSTINDX
INNER JOIN
	GL00100 AS ['Account Master']
		ON ['Account Master'].ACTINDX = ['Account Index Master'].ACTINDX
GO

GRANT SELECT ON uv_AZRCRV_PayablesTransactionDistributionAccounts TO DYNGRP
GO
● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL View For Computer Cheques At Work Status To Check Email Enabled

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPWhile onsite with a client recently, I was asked if it was possible to view the payments in a computer checks batch and whether the creditors (vendors) were enabled for emailed remittances.

Out of the box no, but SmartList Designer can be used to do this. I created a new SQL view to get payments at a status of work and, in SmartList Designer, combined it with the remittance enabled view and vendor email addresses view I have previously posted.

I could have used the table in GP, but I find using views in SmartList Designer easier so created the view.

CREATE VIEW uv_AZRCRV_PaymentRunWORK AS

SELECT
	PM103.BACHNUMB AS 'Batch Number'
	,PM103.VENDORID AS 'Creditor ID'
	,PM2.VENDNAME AS 'Creditor Name'
	,PM2.VADDCDPR AS 'Primary Address'
	,PM2.VADCDTRO AS 'Remit To Address'
	,PM103.DOCDATE AS 'Document Date'
FROM
	PM00200 AS PM2
INNER JOIN
	PM10300 AS PM103 ON PM103.VENDORID = PM2.VENDORID
GO

GRANT SELECT ON uv_AZRCRV_PaymentRunWORK TO DYNGRP
GO

All of these views could have been created natively in SmartList Builder, but by creating them as views I can reuse them in SmartList Designer or any other reporting tool.

● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL View For Remittance Enabled

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPWant an easy way to see which vendors are configured for remittances to be emailed? Plug this view into SmartList Designer, or SmartList Builder, and you’ll have that easy way.

CREATE VIEW uv_AZRCRV_VendorRemittanceEnabled AS
SELECT 
	EmailCardID AS VENDORID
	,CASE WHEN EmailDocumentEnabled = 1 THEN 'Yes' ELSE 'No' END AS 'Remittance Enabled'
 FROM 
	SY04905
WHERE
	EmailSeriesID = 4
AND
	MODULE1 = 19
AND	
	EmailDocumentID = 6
GO

GRANT SELECT ON uv_AZRCRV_VendorRemittanceEnabled TO DYNGRP
GO
● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL View For Vendor Email Addresses

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPI produced this view to return vendor email address for a client a while ago; I don’t typically use this view by itself, but instead combine it with GP tables to produce a larger SmartList report using either SmartList Designer or SmartList Builder.


CREATE VIEW uv_AZRCRV_VendorInternetAddresses
AS
SELECT
	['Internet Addresses'].Master_ID AS 'Vendor ID'
	,['Internet Addresses'].ADRSCODE AS 'Address Code'
	,['Internet Addresses'].EmailToAddress AS 'Email To Address'
	,['Internet Addresses'].EmailCcAddress AS 'Email Cc Address'
	,['Internet Addresses'].EmailBccAddress AS 'Email Bcc Address'
	,['Internet Addresses'].INET1 AS 'Email'
FROM
	SY01200 AS ['Internet Addresses']
WHERE
	['Internet Addresses'].Master_Type = 'VEN'
GO

GRANT SELECT ON uv_AZRCRV_VendorInternetAddresses TO DYNGRP
GO
● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Designer ● Tags: , , , , , , , , , , ,  ● Permalink ● Shortlink ●

Security Views For Use In SmartList Designer: User Access & Granted Security Roles With Tasks & Operations

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPThe eighth and final view in the series, shows users with their access to companies, roles, tasks and security operations. I’ll state up front that this is probably the least useful fo the views due to the sheer number of rows that it returns.

CREATE VIEW [dbo].[uv_AZRCRV_UserAccessAndGrantedSecurityRolesWithTasksAndOperations] 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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
	['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'
	,ISNULL(['Security Role Task Assignment'].SECURITYTASKID, '') AS 'Security Task ID'
	,ISNULL(['Security Task Master'].SECURITYTASKNAME, '') AS 'Security Task Name'
	,ISNULL(['Security Task Master'].SECURITYTASKDESC, '') AS 'Security Task Description'
	,ISNULL(['Security Resource Descriptions'].PRODNAME, '') AS 'Product Name'
	,ISNULL(['Security Resource Descriptions'].Series_Name, '') AS 'Series Name'
	,ISNULL(['Security Resource Descriptions'].DSPLNAME, '') AS 'Security Operation Name'
	,ISNULL(['Security Resource Descriptions'].TYPESTR, '') AS 'Security Operation Type'
FROM
	SY01400 AS ['User Master']
LEFT JOIN
	SY40400 AS ['Class Master']
		ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
LEFT JOIN
	SY60100 AS ['User-Company Access']
		ON ['User-Company Access'].USERID = ['User Master'].USERID
LEFT JOIN
	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
LEFT JOIN
	SY09100 AS ['Security Roles Master']
		ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID
LEFT JOIN
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
LEFT JOIN
	SY01500 AS ['Company Master']
		ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
LEFT JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
LEFT JOIN
	SY10700 AS ['Security Assignment Task Operations']
		ON ['Security Assignment Task Operations'].SECURITYTASKID = ['Security Task Master'].SECURITYTASKID
LEFT JOIN
	SY09400 AS ['Security Resource Descriptions']
		ON ['Security Resource Descriptions'].SECURITYID = ['Security Assignment Task Operations'].SECURITYID
GO

GRANT SELECT ON uv_AZRCRV_UserAccessAndGrantedSecurityRolesWithTasks TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●