MDGP 2018 RTM Feature of the Day: New Workflow Functionality – Copy Workflow Steps

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 eighth Feature of the Day is new Workflow functionality; I am going to break this feature down into three posts, as the three features included by Microsoft are, while all Workflow enhancements, unrelated to one another.

The second piece of new functionality is the ability to copy a Workflow step; this is one of the suggestions I made on MS Connect, so it’s nice to see it added in this version.

This piece of new functionlity enabled the Copy button on the Worklflow Maintenance window when a step is selected:

Copy Workflow Step

In the Copy Workflow Step window, you are able to select a new step for the Workflow Step as well as marking a box to copy the Sub Steps.

This will save so much tiem when creating a new Workflow process. With the lack of parenthesis in Workflow Step Conditions (please vote on my MS Connect Suggestion to add them) creating steps with complex, or even just numerous, conditions can be a lengthy process; having to do this for multiple steps can make the creation of workflow a real chore, but this new functionality, especially with the abilty to include sub steps (which goes beyond my suggestion and is a great idea so kudos to Microsoft for that) will save so much time.

You can easily copy steps and then edit the step condition which is different between the steps (often somthing like a cost centre).

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

MDGP 2018 RTM Feature of the Day: New Workflow Functionality – Reminder Emails

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 eighth Feature of the Day is new Workflow functionality; I am going to break this feature down into three posts, as the three features included by Microsoft are, while all Workflow enhancements, unrelated to one another.

The first of the new functionality, are reminder emails. Reminder emails can be configured on a per step basis in a similar way to the Time Limit:

Workflow Maintenance

Continue reading “MDGP 2018 RTM Feature of the Day: New Workflow Functionality – Reminder Emails”

Microsoft Dynamics GP Macros: Series Index

Microsoft Dynamics GPUsually when integrating data into Microsoft Dynamics GP we will use Integration Manager, but sometimes this tool is either not available or suitable. Not all clients have a licence for the Customisation Pack (which includes the licence for Integration Manager) or the data to be imported does not have an available adaptor in Integration Manager; examples of the latter which I’ve done work with recently include Bill of Materials and Extended Pricing.

The alternatives to using Integration Manager include other tools such as SmartConnect or Scribe (which have their own price tags attached), direct SQL inserts (which do not respect business logic in the application) or the use of GP macros to play back actions.

This latter method is what I am going to cover in this series; I will cover the recording and playback of macros and how data can be loaded in bulk by using the recorded macro as a template.

Microsoft Dynamics GP Macros
Limitations of Macros
Recording A Macro
Playing A Macro
Playing A Macro Quickly
Macro By MailMerge
Macro By SQL
Macro By Excel Formula
Conclusion

Missing Security Roles In Microsoft Dynamics GP 2018 RTM

Microsoft Dynamics GPThe Dynamics GP Support and Services Blog has a post on the missing security in Microsoft Dynamics GP 2018 RTM; this is the new security roles and tasks created for the new functionality.

For features which are being enhanced, the new security tasks may be being added to roles which already exist and are assigned to users, the security is not automatically updated with the upgrade, but instead scripts are made available to add the missing security; this places the onus on the client to determine if the roles should be updated or not.

I have updated my original post with the new script, but you can also download it here.

MDGP 2018 RTM Feature of the Day: Workflow Additional Fields

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 seventh Feature of the Day is Workflow Additional Fields. Back in April 2017 I submitted an MS Connect suggestion asking for the ability to add additional fields to Workflow.

I guess this might be a form of responding to this suggestion, but I was looking for a way that the user could determine which fields could be available rather than Microsoft deciding which extra fields are added.

Two of the workflow types have had extra fields added where they available in both the Workflow Condition Editor and Message Setup windows allowing you to build workflow steps and notification emails with them.:

Two other workflow types have had the Account Description made available at the line level. These two workflow types are:

  • Purchase Order
  • Purchase Requisition

While having the extra fields available is good, I am disappointed with this new feature for two reasons.

Firstly, it is a very limited number of fields which have been made available rather than allowing users to select additional fields to add.

Secondly, from everything I have seen on the feature regarding EFT information, it only seems like you can include the fields in the workflow conditions and email messages; Dynamics GP is desperately missing functionality around changes to EFT details needing to go through workflow for approval. I fear that this new feature doesn’t extend this far.

As soon as Dynamics GP 2018 is released, I will be giving this a test to confirm how it works.

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

SQL Function To Return Last Workflow Comment

Microsoft Dynamics GPBack in April 2017 I posted an SQL function which can be used to return the workflow status of a transaction or card. This function is now complimented by another one which can be used to return the last comment recorded. This was created for use on a customisation of the Purchase Requisition Entry window which added a Rejection Reason field so users could see at a glance why a purchase requisition had been rejected.

IF OBJECT_ID (N'uf_AZRCRV_GetWorkflowApprovalComments', N'FN') IS NOT NULL
    DROP FUNCTION uf_AZRCRV_GetWorkflowApprovalComments
GO
CREATE FUNCTION dbo.uf_AZRCRV_GetWorkflowApprovalComments(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(20))
	RETURNS VARCHAR(14)
AS
/*
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).

Returns Workflow Approval status of a specified workflow item.

Requires input parameters of WorkflowTypeName and WfBusObjKey

Valid Workflow Type Names are (as of Microsoft Dynamics GP 2016 R2):
	General Ledger Batch Approval
	Receivables Batch Approval
	Payables Batch Approval
	Payables Transaction Approval
	Purchase Order Approval
	Purchase Requisition Approval
	Vendor Approval
	Employee Profile Approval
	Employee Skills Approval
	Payroll Direct Deposit Approval
	Payroll Timecard Approval
	Payroll W4 Approval
	Expense Report Approval
	Timesheet Approval
	Smartlist Designer View Approval
*/
BEGIN
	RETURN ISNULL((
		SELECT TOP 1 
			WF30100 AS ['Workflow History']
		FROM 
			WF30100 AS ['Workflow History']
		INNER JOIN
			WFI10002 AS ['Workflow Master']
				ON
					['Workflow Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
		WHERE
			['Workflow Master'].Workflow_Type_Name = @WorkflowTypeName
		AND
			['Workflow Master'].WfBusObjKey = @WfBusObjKey
		ORDER BY
			['Workflow History'].DEX_ROW_ID DESC)
	,'Not Submitted')
END
GO
GRANT EXECUTE ON uf_AZRCRV_GetWorkflowApprovalComments TO DYNGRP
GO

This function can easily be included in a view or other query used in a variety of reporting tools.

MDGP 2018 RTM Feature of the Day: New Workflows

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 sixth Feature of the Day is the introduction of three new approval workflows and related emails messages.

The first of the new workflows is General Ledger Account Approval:

Workflow Maintenance

Continue reading “MDGP 2018 RTM Feature of the Day: New Workflows”

SQL View to Return Sales By Customer By Fiscal Year

Microsoft Dynamics GPThe last post was a SQL view to return purchases by vendor by fiscal year in an update of a prior script which only returned the information by the calendar year; I did a similar view at the time for the Sales series which also returned information for the calendar year. This post is an update of that one to return the information linking into the Financial Calendar setup to return the sales by customer by financial year.

IF OBJECT_ID (N'uv_AZRCRV_SalesByCustomerByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesByCustomerByYear
GO
CREATE VIEW uv_AZRCRV_SalesByCustomerByYear AS
/*
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).
*/
SELECT
	['Receivables Transactions'].CUSTNMBR AS 'Customer ID'
	,['RM Debtor MSTR'].CUSTNAME AS 'Customer Name'
	,['RM Debtor MSTR'].CUSTCLAS AS 'Class ID'
	,['Receivables Transactions'].DOCDATE AS 'Year'
	,CASE WHEN ['RM Debtor MSTR'].INACTIVE = 1 THEN
		'Inactive'
	ELSE
		'Active'
	END AS 'Status'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].SLSAMNT ELSE ['Receivables Transactions'].SLSAMNT * -1 END) AS 'Sales Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TRDISAMT * -1 ELSE ['Receivables Transactions'].TRDISAMT END) AS 'Trade Discount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].FRTAMNT ELSE ['Receivables Transactions'].FRTAMNT * -1 END) AS 'Freight'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].MISCAMNT ELSE ['Receivables Transactions'].MISCAMNT * -1 END) AS 'Miscalleneous'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TAXAMNT ELSE ['Receivables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].DOCAMNT ELSE ['Receivables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
	(SELECT
		CUSTNMBR
		,YEAR1 AS DOCDATE
		,GLPOSTDT
		,DOCNUMBR
		,RMDTYPAL
		,SLSAMNT
		,TRDISAMT
		,FRTAMNT
		,MISCAMNT
		,TAXAMNT
		,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
	FROM
		RM20101
	INNER JOIN
		SY40101
			ON
				DOCDATE BETWEEN FSTFSCDY AND LSTFSCDY
	WHERE
		VOIDSTTS >= 0
	AND
		RMDTYPAL IN (1,3,4,7,8)
	UNION ALL
		SELECT
			CUSTNMBR
			,YEAR1 AS DOCDATE
			,GLPOSTDT
			,DOCNUMBR
			,RMDTYPAL
			,SLSAMNT
			,TRDISAMT
			,FRTAMNT
			,MISCAMNT
			,TAXAMNT
			,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
		FROM
			RM30101
		INNER JOIN
			SY40101
				ON
					DOCDATE BETWEEN FSTFSCDY AND LSTFSCDY
		WHERE
			VOIDSTTS >= 0
		AND
			RMDTYPAL IN (1,3,4,7,8)
	) AS ['Receivables Transactions']
LEFT JOIN
	RM00101 AS ['RM Debtor MSTR']
		ON
			['Receivables Transactions'].CUSTNMBR = ['RM Debtor MSTR'].CUSTNMBR
GROUP BY
	['Receivables Transactions'].CUSTNMBR
	,['RM Debtor MSTR'].CUSTNAME
	,['RM Debtor MSTR'].CUSTCLAS
	,['RM Debtor MSTR'].INACTIVE
	,['Receivables Transactions'].DOCDATE
GO
GRANT SELECT ON uv_AZRCRV_SalesByCustomerByYear 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.

MS Connect Suggestion: Allow File and Folder Shortcuts to be Attached in DocAttach

Microsoft Dynamics GPDocAttach was introduced in Microsoft Dynamics GP 2013 and replaced the ole OLE Notes functionality in all areas of Dynamics GP 2013 R2.

You can attach all types of files to DocAttach including web links, except you cannot include shortcuts to files or folders on your network. While the intention of DocAttach is to be able to attach files and have them stored in the database.

However, if you deal with a substantial number of fixed assets and need images of the condition, or deal with technical diagrams, these files are often between quite and very large. As files are stored in the database as BLOBs (Binary Large OBjects) they are the same size in the database as they were originally; this can mean the database size will grow very quickly as files are added.

I have submitted a suggestion on Microsoft Connect asking that file and folder shortcuts be an allowed file type for Doc Attach.

You can vote for this suggestion here.

MDGP 2018 RTM Feature of the Day: Document Attachment Unplugged

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 won commentary.

The series index for this series of posts is here.

This Feature of the Day post from Pam is not itself a new feature, but is a round up of all of the functionality offered by DocAttach since its introduction in Microsoft Dynamics GP 2013 and the enhancements in the versions since.

The whole post can be read here.

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