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 (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).
*/
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 (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).
*/
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).

Nothing Happens When Submitting A Purchase Requisition For Approval

Microsoft Dynamics GPI was onsite with a client to start a project implementing Workflow 2.0 for Purchase Requisition Approval and ran into a problem while doing training on how to interact with the workflow.

Everything had been fine until this point, but after creating a simple example approval workflow, I clicked the Submit button on the Purchase Requisition Entry window and nothing happened; I tried again through the navigation list, again without success.

I promised to figure out the issue in the next break and moved onto discussing other ways of interacting and then called the break.

It occurred to me that this might be another manifestation of an issue I have encountered previously, but without the visible error message.

I ran the script against the system database which redeploys the .NET Assemblies used by Workflow:

exec wfDeployClrAssemblies

It took a few minutes to run through, but I was then able to approve the requisition without further problem.

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 (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).
*/
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: Save Fixed Assets With Suffix

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.

in this post, I am “hands on” with the Save Fixed Assets With Suffix Feature of the Day, which was the fifteenth feature.

When this feature was announced, I said I was puzzled by it as I thought you already could, but when testing found that in GP 2016 R1, you could change the Suffix, but GP wasn’t happy and produced a number of errors.

Mark Polino also thought this was not a new feature, but the return of a , possibly inadvertently, removed feature.

The Asset General Information window (Financial >> Cards >> Fixed Assets >> General) now allows you to overtype the Suffix field (to the right of the Asset ID field:

Asset General Information

Click to show/hide the Hands On With Microsoft Dynamics GP 2016 R2 Series Index

Hands On With Microsoft Dynamics GP 2016 R2: POP To FA Link Includes Taxes

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 thirteenth Feature of the Day I am going “hands on” with is POP to FA Link to Include Taxes.

This feature will be popular with some of our clients who deal with non-recoeverable VAT (VAT is a UK sales tax charged at a standard rate of 20%) as non-rec VAT means they would need to depreciate the gross value of the asset including VAT where in previous versions the asset would be created net and need to be amended.

To use this feature, the Include Tax in Acquisition Cost checkbox on the Fixed Assets Company Setup window (Financial >> Setup >> Fixed Assets >> Company) under the Purchasing Options:

Fixed Assets Company 	Setup

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: POP To FA Link Includes Taxes”

Hands On With Microsoft Dynamics GP 2016 R2: Bank Reconciliation Tracks History

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 tenth Feature of the Day I’m going “hands on” with is the Bank Reconciliation Tracks History one which introduced a new window on the Financial Routines menu:

Reconciled Transaction Maintenance

To use this feature, decide if all, a range or one Checkbook should have its reconciliation history moved to history.

Mark the checkbox next to the type of transactions which should be moved and enter a Cut Off date; although the date field is aligned with the Reconciled Deposit Transactions, it apples to all of the transaction types.

Click Process to perform the move of reconciled transactions to history.

Click to show/hide the Hands On With Microsoft Dynamics GP 2016 R2 Series Index

Hands On With Microsoft Dynamics GP 2016 R2: Receivables & Payables Distribution Line Display UI Change

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 feature I am going “hands on” with is the ninnth Feature of the Day, GL Distribution Line Display UI change.

The status of the scrolling window on the Payables or Distribution Zoom window are remembered:

As with the GL Distribution Line Display UI Change feature, the settings for this feature are stored in the SY01402 table (System User Defaults) in the system database, which is typically called DYNAMICS.

Again is closed and 1 is expanded; the Payables Management setting is where the syDefaultType column is equal to 107 and Receivables Management is 111.

Click to show/hide the Hands On With Microsoft Dynamics GP 2016 R2 Series Index

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”

Hands On With Microsoft Dynamics GP 2016 R2: GL Distribution Line Display UI change

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 feature I am going “hands on” with is the seventh Feature of the Day, GL Distribution Line Display UI change.

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: GL Distribution Line Display UI change”

Hands On With Microsoft Dynamics GP 2016 R2: Display Tax Percent for Historical Sales Transactions

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.

In this post, I’m going “hands on” with the sixth of the Microsoft Dynamics GP 2016 R2 Feature of the Day posts, Display Tax Percent for Historical Sales Transactions.

To test this feature, I created a new Tax Detail in Tax Detail Maintenance (Administration >> Setup >> Company >> Tax Details) for a Sales series Standard Rated UK VAT at 20% and created a related Tax Schedule:

Tax Detail Maintenance

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: Display Tax Percent for Historical Sales Transactions”