Hands On With Microsoft Dynamics GP 2016 R2: Batch Edited Message

● 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 of the day I am covering in this post, is the Batch Edited Message. If a user tries to open a batch being edited by another user they will receive a warning message containing the users name:

Microsoft Dynamics GP: This batch is being edited by laurab. Please try later.

Microsoft Dynamics GP

This batch is being edited by laurab. Please try later.

Continue reading → Hands On With Microsoft Dynamics GP 2016 R2: Batch Edited Message

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

Hands On With Microsoft Dynamics GP 2016 R2: SmartList Favourite Protection

● 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 second of the new features I’m going to cover is SmartList Favorite Protection.

This feature allows the administrators to define a password which must be entered before a SmartList favorite can be modified. The password is entered in the SmartList Options window (Administration >> Setup >> System >> SmartList Options):

SmartList Options

The password impacts all SmartList favorites. Please do not use ACCESS as the password; I see far too many clients who have this as a password throughout their system. It is in all of the manuals and lots of blog posts so it is pretty much the least secure password you could use.

Continue reading → Hands On With Microsoft Dynamics GP 2016 R2: SmartList Favourite Protection

● Categories: Dynamics, GP, Microsoft, SmartList ● 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: Batch Edited Message

● 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 fourth Feature of the Day is Batch Edited Message:

This batch is being edited by JodiCh. Please try again later.

This is a simple, but oh so welcome, enhancement to Microsoft Dynamics GP 2016 R2; when a user tries to interact with a batch which is being edited by another user, the message will no longer simply say it is being edited by another user, but will now name the other user who is editing the batch.

No longer will I need to write, or deploy, SmartLists to users allowing them to see who is locking batches (I still might so the administrator can take a global view of their system) to simply see which user is preventing a batch from being posted.

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

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

Fixing Typo in Fabrikam’s Tax Schedule Description

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPThe script in this post fixes a typo, in the Fabrikam sample database, in the Tax Schedule Description whereby the h in Purchases was missing. Not a big thing, but I was working on a report which was configured to return the Description as well as the Tax Schedule ID and it was bugging me.

Hence a script to fix it, so I can easily fix it again in future.

/*
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).
*/UPDATE
	['Tax Schedule Header Master']
SET
	TXSCHDSC = REPLACE(TXSCHDSC, 'Purcase', 'Purchase')
FROM
	TX00101 AS ['Tax Schedule Header Master']
WHERE
	TXSCHDSC LIKE '%Purcase%'
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL Script To Bulk Alter Users With Logins

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPBack in July 2013 I did a post where I looked at a problem copying live to test. The basic issue was that the Microsoft Dynamics GP user is also a login (at the SQL Server level) and a user (at the SQL Server database level) and when a database is copied from the live server to the test server (or from the current live top the new live) you can run a script to transfer across the logins, but the users come across with the database and will have different SIDs (Security IDs).

You can use the ALTER USER command in SQL to re-link the login with the user, but this is one statement per user per database. The old post showed how to do this, but this quickly becomes a pain when there are more than a handful of users.

As Perfect Image has grown we have clients with more and more users and/or company databases. Our largest client has over 250 users in their Dynamics GP installation while another has fewer users, but well over 100 companies. Both of these can make copying live to test problematic, especially when only a company database might be copied over rather than the whole system.

I needed to automate the process of altering the login to match the user; the below script is the result of this need. Continue reading → SQL Script To Bulk Alter Users With Logins

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

SQL Script To Copy Tax Setup To All Companies Using Cursor

● Ian Grieve ●  ● 2 Comments   ● 

Microsoft Dynamics GPFollowing on from the creation of the script to copy tax schedules between companies, I was on a fairly long train journey and spent a small part of the time wrapping a cursor round the original script to allow tax schedules to be configured in one company and rolled through the others.

There is one setting at the top of the script to set the SourceCompany.

One item to note, is that the Account Index is set to 0 so all companies do need the tax nominal account entering once they have been copied.

/*
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).
*/
DECLARE @SQLStatement AS VARCHAR(2000)
DECLARE @SourceCompany VARCHAR(5)
DECLARE @DestinationCompany VARCHAR(5)

SET @SourceCompany = 'TWO'

DECLARE
	cursor_InterID CURSOR 
FOR 
	SELECT
		INTERID
	FROM
		DYNAMICS..SY01500
	INNER JOIN
		master..sysdatabases
	ON
		name = INTERID
	
	OPEN cursor_InterID

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@DestinationCompany
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)

			-- Sales/Purchases Tax Schedule Header Master
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00101
				(TAXSCHID,TXSCHDSC)
			--VALUES
				(SELECT 
					TAXSCHID,TXSCHDSC
				FROM
					' + @SourceCompany + '..TX00101 AS TX
				WHERE
					(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00101 WHERE TAXSCHID = TX.TAXSCHID) = 0)'
			EXEC (@SQLStatement)

			-- Sales/Purchases Tax Schedule Master
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00102
				(TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate)
			--VALUES
				(SELECT
					TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate
				FROM
					' + @SourceCompany + '..TX00102 AS TX
				WHERE
					(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00102 WHERE TAXSCHID = TX.TAXSCHID AND TAXDTLID = TX.TAXDTLID) = 0)'
			EXEC (@SQLStatement)

			-- Sales Purchases Tax Master
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00201
				(TAXDTLID,TXDTLDSC,TXDTLTYP,ACTINDX,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
				TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,NOTEINDX,
				NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
				TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT)
			--VALUES
				(SELECT
					TAXDTLID,TXDTLDSC,TXDTLTYP,0,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
					TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,0,
					NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
					TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT
				FROM
					' + @SourceCompany + '..TX00201 AS TX 
				WHERE
					(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00201 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
			EXEC (@SQLStatement)

			-- Sales/Purchases Tax Summary Master
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00202
				(TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST)
			--VALUES
				(SELECT
					TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST
				FROM
					' + @SourceCompany + '..TX00202 AS TX
				WHERE
					(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00202 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
			EXEC (@SQLStatement)

			FETCH NEXT FROM
				cursor_InterID
			INTO
				@DestinationCompany
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

If you use this script, then please make sure you have a good backup before running it and also test afterwards.

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

MDGP 2015 R2 Feature of the Day: Default SmartList Visibility

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 has been released and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The twenty fifth, and last, Feature of the Day is Default SmartList Visibility. When creating a new SmartList Favorite, the user has the option to select to whom the SmartList Favorite will be visible, including the following options: System, Company, User Class, and User ID. System is the default option selected for the visibility.

Add or Remove Favorites

Continue reading → MDGP 2015 R2 Feature of the Day: Default SmartList Visibility

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

MDGP 2013 R2 Feature of the Day: Take Company Offline

● Ian Grieve ●  ● 1 Comment   ● 

The Inside Microsoft Dynamics GP blog started a series of Microsoft Dynamics GP 2013 R2 Feature of the day posts the other day. As they did with the Microsoft Dynamics GP 2013 Feature of the Day posts they are doing them as short posts containing a PowerPoint slide show. I am translating these from the PowerPoints into posts; you can find my series index here.

The twenty-second Feature of the Day covered is Take Company Offline. This feature provides an easy way to take a company offline in order to perform a maintenance or business task requiring exclusive access. While a company is offline, only the user needing to perform the task and administrators can log into the company.

With this feature, the administrator can also send messages to GP users. Messages can be sent as a task reminder of upcoming events like maintenance happening this weekend or pop-up messages asking the user to exit the system for example.

The Take Company Offline for Maintenance window allows a single, or multiple companies, to be taken offline and a message entered which will be displayed to user’s as they attempt to access an offline company:

Take Company Offline for Maintenance

Continue reading → MDGP 2013 R2 Feature of the Day: Take Company Offline

● Categories: Dynamics, GP, Microsoft, Professional Services Tools Library ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2013 R2 Feature of the Day: Identity Management – Login

● Ian Grieve ●  ● 3 Comments   ● 

The Inside Microsoft Dynamics GP blog started a series of Microsoft Dynamics GP 2013 R2 Feature of the day posts the other day. As they did with the Microsoft Dynamics GP 2013 Feature of the Day posts they are doing them as short posts containing a PowerPoint slide show. I am translating these from the PowerPoints into posts; you can find my series index here.

The third Feature of the Day covered is Identity Management – Login.

This feature allows users to log into the GP web client using their Windows identity (e.g. the Windows Domain login). This is configured in the User Setup window (Administration ¯ Setup ¯ Tools ¯ System ¯ User) when the user is created.

The window has been enhanced to allow a user to be configured with both, or either, an SQL identity and/or a Windows identity; additionally a user can be configured as a Web Client user only and does not then require an SQL identity which is only required if the user will access GP using the desktop client; likewise the Windows identity is only required if the user will be using the web client.

A user would have both types of identity created if they would be accessing GP using either, or both, the desktop or web clients in a mixed mode deployment.

User Security

Continue reading → MDGP 2013 R2 Feature of the Day: Identity Management – Login

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