Update Year on Budgets in Fabrikam Sample Company

Microsoft Dynamics GPI’ve written a few scripts to update data in the Fabrikam sample company (I will be posting a series index to link them all together in the near future).

The sample company ships with a few budgets preloaded, but they are not named very well. For example, the budget for 2027 (the year in which Fabrikam is operating) is named BUDGET 4 and the budget for 2025 is named BUDGET 2005.

Continue reading “Update Year on Budgets in Fabrikam Sample Company”

Hands On With MDGP 2018 RTM New Features: New Workflows

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

One of my favourite new features in Microsoft Dynamics GP 2018 RTM, are the new workflows which were introduced:

  • General Ledger Account Approval
  • Receivings Transaction Approval
  • Purchasing Invoice Approval

I’ve had a number of clients and prospects ask if it is possible to have approvals of goods receipt notes and invoices in POP, so it’s good that I can now say that there are. These workflows are likely to be fairly complicated, so, in this post, I am going to be giving a test to the General Ledger Account Approval workflow.

The workflow process is created and maintained just like any other via Workflow Maintenance (Administration area page >> Setup >> Company >> Workflow >> Workflow Maintenance):

Workflow Maintenance

Continue reading “Hands On With MDGP 2018 RTM New Features: New Workflows”

Hands On With MDGP 2018 RTM New Features: DocAttach Available on More Windows

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

The first of the features announced by Microsoft was the introduction of DocAttach on more windows: the DocAttach button on the action pane makes the function more accessible to users. The windows updated for this are:

  • General Ledger Transaction Entry
  • Receivables Transaction Entry
  • Asset General Information
  • Employee Entry
  • I-9 Form (Payroll)
  • Journal Entry Enquiry
  • Payables Transaction Entry Zoom
  • Receivables Transaction Entry Zoom

I quite like the DocAttach function in Microsoft Dynamics GP, although I am careful to be clear with clients and prospects that is is document attachment and not document management.

While it’s good to see DocAttach getting wider exposure across Microsoft Dynamics GP on the action panes, it’s especially nice to see Fixed Asset Management getting some DocAttach improvements as many of my clients have images of their assets to store:

Fixed Asset General

Click to show/hide the Hands On With MDGP 2018 RTM New Features Series Index

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

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:

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”

Dynamics GP Tech Conference 2017: Comprehensive Doc Attach

Microsoft Dynamics GPMicrosoft 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).

SQL View to Return Budgets By Month

Microsoft Dynamics GPThe budget functionality in Microsoft Dynamics GP isn’t the strongest with reporting being particularly weak. The ability to report on budgets in Management Reporter does somewhat redeem this area of functionality.

However, the absence of a SmartList Object for budgets is quite a big issue, as SmartList is a very nice flexible reporting tool which the majority of my clients know well. For those with SmartList Builder, it was easy enough to create a SmartList Object for them.

With the introduction of SmartList Designer, we were able to roll out the SmartList budget report to all of the clients who wanted it.

The script is below and returns the budget information with the beginning balance, 12 hard-coded periods and total horizontally across the page.

Continue reading “SQL View to Return Budgets By Month”

Restore The Account Segment Warning

Microsoft Dynamics GPWhen new accounts are being created in Microsoft Dynamics GP a warning message is displayed if an entered segment does not exist in the Segment Master (GL40200) table:

Restore The Account Segment Warning

If the checkbox is marked then the warning message is not displayed for that user anymore and there is no way to restore the message through the front end of the system.

The below script, allows the message to be restored for a named user (change the highlighted text to the required user):

/*
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).
*/
DECLARE @USERID VARCHAR(20)
SET @USERID = 'userid'

DELETE FROM
	SY01401
WHERE
	coDefaultType = 13
AND
	USERID = @USERID
GO

SQL Script To Activate Horizontal Scroll Arrows

Microsoft Dynamics GPThe Account field in Microsoft Dynamics GP is of a fixed width, but the maximum width of the account string themselves can be much longer than the field.

The solution Microsoft have supplied is an option which activates horizontal scroll arrows in the Account field:

Acount Maintenance

This is activated, on a per user basis, via the User Preferences window (Microsoft Dynamics GP menu >> User Preferences) by marking the Horizontal Scroll Arrows:

User Preferences

However, if you have a lot of users, this can sometimes be a difficult message to disseminate.

An alternative I came up with for a client a wile ago was to create a trigger on the Users Master (SY01400) table which updates the field after a new user is created:

/*
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 TRIGGER
	utr_AZRCRV_UpdateSY01400ActivateHorizontalScrollArrows
ON
	SY01400
AFTER INSERT AS
	UPDATE
		['Users Master']
	SET
		HSCRLARW = 1
	FROM
		SY01400 AS ['Users Master']
	INNER JOIN
		inserted AS INS
			ON
				INS.USERID = ['Users Master'].USERID
GO

If users have already been created, then the following script can be used to activate the horizontal scroll arrows for them:

/*
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).
*/
UPDATE
	SY01400
SET
	HSCRLARW = 1
WHERE
	HSCRLARW = 0
GO

We actually decided to remove the trigger and apply the update script via a SQL Server Agent scheduled job which runs on a period basis (if I recall correctly, it was configured to run each evening at 2100 (avoiding backup jobs).

SQL Script To Update EFT Payment Register Report

Microsoft Dynamics GPPretty much every client we have of Microsoft Dynamics GP uses the EFT Payment Register Report; a number of the save the file instead of printing it as the report has been customised to generate the EFT payment file for upload to the bank.

The clients who do this are either those whose installations of Microsoft Dynamics GP predate version 10 when the EFT for Payables Management module was made available to the UK market or they implemented before the EFT File Format was enhanced to allow a CSV output.

While some clients have created or amended a file format since the above, not all of them have been willing to spend the time (or money) to make the change.

As such, when they copy live to test they need to amend the path to which the report is being output, to ensure that a live file is not accidentally overwritten.

To faciliate this for users who have automated the live to test restore I created a script they could build into the process:

/*
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).
*/
UPDATE
	SY02200
SET
	FILEXPNM = 'C:\BACS OUTPUT\BACS.TXT'
WHERE
	PTGRPTNM = 'EFT Payment Register'

The highlighted section is the path name which needs to be changed depending on where the test file is to be output.

Hands On With Microsoft Dynamics GP 2016 R2: Link Credit Card Invoices To Original Invoice

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components and also look at the new functionality introduced; the index for this series can be found here.

The eighth Feature of the Day, which I am “hands on” with in this post, was Link Credit Card Invoices To Original Invoice.

To test this feature, I entered, posted and paid an invoice for Attractive Telephone Co. for $1,000:

Payables Transaction Inquiry - Vendor

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: Link Credit Card Invoices To Original Invoice”