SQL View to Return Budgets By Month

● Ian Grieve ●  ● 0 Comments   ● 

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

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

Restore The Account Segment Warning

● Ian Grieve ●  ● 4 Comments   ● 

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
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL Script To Activate Horizontal Scroll Arrows

● Ian Grieve ●  ● 4 Comments   ● 

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).

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

SQL Script To Update EFT Payment Register Report

● Ian Grieve ●  ● 2 Comments   ● 

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.

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

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

● Ian Grieve ●  ● 3 Comments   ● 

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

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

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

● Ian Grieve ●  ● 0 Comments   ● 

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

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

Microsoft Dynamics GP January Hotfix Released

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPI don’t usually post about releases, except for major version releases. However, the January Hotfix release caught my eye with a few of the included features in the 2016 R2 one.

As well as having the US Payroll updates there are updates to the following which would be of benefit to clients in the UK:

  • VAT Daybook

    • VAT Daybook Summary reports may not show correct data in boxes 1, 6 and 7 if you voided a transaction.
    • VAT detail report may show incorrect figures if you have 2 or more tax codes on a transaction.
  • General Ledger

    • The Excel copy and paste function may not calculate the credit/debit column correctly when using an allocation account.
  • Fixed Asset Management

    • Tax values may not update properly if a user marks to create multiple fixed assets in the Fixed Asset Purchase Order additional information window.
  • Bank Reconciliation

    • You may experience performance issues with the Checkbook Register Inquiry window after you install GP 2016 R2.
  • Project Accounting

    • Ability to change line distributions for time and material projects in revenue recognition.
    • Fee Accounts not available in line distributions button.
    • Purchase Receiving’s not reflecting WIP account edits from Purchase Order for non-inventory items.
  • System Manager

    • Fixed issues with Uncollated printing.

The hotfixes for Dynamics GP 2013 R2 and 2015 R2 only contain US Payroll fixes.

The post from the Dynamics GP Support and Services blog contains the download links.

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

MDGP 2016 R2 Feature of the Day: Link Credit Card Invoices to Original Invoice

● 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 2016 R2; 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 eighth Feature of the Day is Link Credit Card Invoices to Original Invoice.

This feature of the day, updates the transaction description on the credit card vendor invoice to easily track back to the originating voucher:

Payables Transaction Inquiry

Continue reading → MDGP 2016 R2 Feature of the Day: Link Credit Card Invoices to Original Invoice

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

MDGP 2016 R2 Feature of the Day: GL Distribution Line Display UI Change

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2016 R2; 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 seventh Feature of the Day is GL Distribution Line Display UI Change.

Transaction Entry

With this new feature, General Ledger Transaction Entry and Journal Entry Inquiry windows will default the scrolling window expanded or collapsed based on the previous display state.

This is promised to be a user based default, so it will be interesting to see if it is actually user based or based upon the Dex.ini file which would make it a machine setting rather than a user one.

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

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

Microsoft Dynamics GP Budget Import Issue

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPBudgets in Microsoft Dynamics GP are easy to maintain using the Budget Wizard. However, we do regularly field calls from clients reporting that the budget import is not working.

The problem reported, was the same as the problem always is; the budget template was not in the correct format.

The first image is the header of the one they were trying to import and the second is the Master budget I exported from GP to show them:

Excel Budget

Excel Budget

As you can see the former budget template has an extra row in the header; this is sufficient to stop the import working.

Everytime we have had this error reported we have found that something was changed in the budget template: a new header row, a header row being deleted, a column being added (the latter is the most common.

The client removed the extra row and was then able to import the budget.

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