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

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

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

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

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

● Ian Grieve ●  ● 4 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 ●

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 ●

Copy Fiscal Calendar To All Companies

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPIn the last post I mentioned that I create scripts when implementing Microsoft Dynamics GP and then make them available to clients when they would be of benefit. The last script I posted, allowed calendars to ve copied from one database to another. Afetr writing that scirpt I then adapted it by wrapping a cursor around it which allowed a source database to be defined and the calendar copied to all other databases.

The original script is still useful as it allows a targeted copying of calendars from a source to a destination database, but the new allows calendars to be quickly replicated across all companies if they share the same financial year.

The script has two parameters (highlighted) at the top which need to be set:

  • Year
  • SourceDatabase
  • Continue reading → Copy Fiscal Calendar To All Companies

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

    Copy Fiscal Calendar To A New Company

    ● Ian Grieve ●  ● 5 Comments   ● 

    Microsoft Dynamics GPI have a bit of catching up to do on writing posts, but to start the year off I am back to some SQL scripts. As I have mentioned before I have a number of clients with multiple, and in some cases well over 100, companies in Microsoft Dynamics GP.

    I do try to make life easier for them when possible so have been sharing a number of scripts I have for implementing systems. One such script is one which will copy financial (fiscal) calendars from one database to another.

    There are three parameters at the top which need to be set before running the script:

    1. Year
    2. Source Company
    3. Destination Company

    These parameters are highlighted below:

    /*
    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 @Year VARCHAR(4) = '2014'
    DECLARE @SourceDatabase VARCHAR(5) = 'TWO'
    DECLARE @DestinationDatabase VARCHAR(5) = 'THREE'
    DECLARE @SQLStatement VARCHAR(1000)
    
    SET @SQLStatement =
    	'INSERT INTO ' + @DestinationDatabase + '..SY40101
    		(YEAR1,FSTFSCDY,LSTFSCDY,NUMOFPER,HISTORYR)
    	--VALUES
    		(SELECT
    			YEAR1,FSTFSCDY,LSTFSCDY,NUMOFPER,HISTORYR
    		FROM
    			' + @SourceDatabase + '..SY40101
    		WHERE
    			(SELECT
    				COUNT(YEAR1)
    			FROM
    				' + @DestinationDatabase + '..SY40101
    			WHERE
    				YEAR1 = ' + @Year + ') = 0
    		AND
    			YEAR1 = ' + @Year + ')'
    EXEC (@SQLStatement)
    
    SET @SQLStatement =
    	'INSERT INTO ' + @DestinationDatabase + '..SY40100
    		(CLOSED,SERIES,ODESCTN,FORIGIN,PERIODID,PERIODDT,PERNAME
    		,PSERIES_1,PSERIES_2,PSERIES_3,PSERIES_4,PSERIES_5,PSERIES_6,
    		YEAR1,PERDENDT)
    	--VALUES
    		(SELECT
    			CLOSED,SERIES,ODESCTN,FORIGIN,PERIODID,PERIODDT,PERNAME
    			,PSERIES_1,PSERIES_2,PSERIES_3,PSERIES_4,PSERIES_5,PSERIES_6,
    			YEAR1,PERDENDT
    		FROM
    			' + @SourceDatabase + '..SY40100
    		WHERE
    			(SELECT
    				COUNT(YEAR1)
    			FROM
    				' + @DestinationDatabase + '..SY40100
    			WHERE
    				YEAR1 = ' + @Year + ') = 0
    			AND
    				YEAR1 = ' + @Year + ')'
    EXEC (@SQLStatement)
    GO

    The calendar will only be copied if the destination database does not contain a calendar with the same name.

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

    Security Views For Use In SmartList Designer: Populating Security Resource Descriptions

    ● Ian Grieve ●  ● 8 Comments   ● 

    Microsoft Dynamics GPIn this post I’m taking a slight diversion and not posting a SQL view. The reason is that the next view I will be posting requires that the security resource descriptions be available which, by default, they’re not. However, they can be made available, which is what I will be covering in this post.

    SY09400 is the Security Resource Descriptions table which is, by default, empty as the names are stored within the Microsoft Dynamics GP client itself. However, this table can be populated, perhaps somewhat counter-intuitively, by using the Clear Data window (Microsoft Dynamics GP menu ¯ Maintenance ¯ Clear Data).

    To populate the table, open Clear Data, click on Display and then select Physical:

    Clear Data

    Continue reading → Security Views For Use In SmartList Designer: Populating Security Resource Descriptions

    ● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

    MDGP 2015 R2 Feature of the Day: Self Service User Tasks and Roles

    ● Ian Grieve ●  ● 0 Comments   ● 

    Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June 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 twelfth Feature of the Day is Self Service User Tasks and Roles.

    In the Microsoft Dynamics GP 2015 R2 release a Self Service user type was added; this user type will enable users that only require very limited access to the system a less expensive option to perform tasks such as entering payroll time, entering project time and expenses, or creating a requisition.

    This feature also includes Employee Self Service Security Roles and the ability to filter on security tasks available for each user type, which will make the task and role assignment more efficient.

    Security Role Setup & Security Role Setup

    Nice to see that the new user type is also getting some easing of the setup of the roles and tasks. This is one area that, despite the default roles and tasks available, seems to daunt a lot of new users to the system.

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

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

    MDGP 2015 R2 Feature of the Day: Self Service User Type

    ● Ian Grieve ●  ● 1 Comment   ● 

    Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June 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 eleventh Feature of the Day is Self Service User Type.

    Microsoft Dynamics GP 2015 R2 sees a Self Service user type added.

    This user type will enable users that only require very limited access to the system a less expensive option to perform tasks such as entering payroll time, entering project time and expenses, or creating a requisition.

    User Setup

    This new user type is going to be a really useful addition. I have one client who has been looking for a POP add-on to allow online creation of orders for more than 400 users and all the old options were looking quite pricey.

    This Self Service user type used in conjunction with the web client should give them the functionality they want, at a price point they can accept.

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

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

    MDGP 2015 Feature of the Day: Vendor Approval

    ● Ian Grieve ●  ● 0 Comments   ● 

    Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series of posts on the new features of, the soon to be released, Microsoft Dynamics GP 2015. As I have done for the last two releases, I intend to shadow these posts and add my own opinions on these new feature; the series index is here.

    The fourth Feature of the Day is Vendor Approval. This feature is an enhancement to the new Workflow 2.0 module introduced in Microsoft Dynamics GP 2013 R2 which allows users to control the setup and maintenance of vendors.

    Vendor Approval allows users to create mandatory workflow processes around the setup and maintenance of vendors. These mandatory processes need to be completed before transactions can be posted. The workflow processes are defined in Workflow Maintenance (Administration area page ¯ Setup ¯ Company ¯ Workflow ¯ Workflow Maintenance):

    Workflow Maintenance

    The workflow status appears on the window and actions can be taken directly from those windows, from an email or from a Navigation List.

    I want to see more details about this feature as I am very much hoping that the workflow has been extended to include the EFT Bank details.

    The new Workflow 2.0 is far, far simpler and easier to implement and use.

    The roles targeted by this feature are:

    • All

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

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

    MDGP 2015 Feature of the Day: Receivables Management Batch Approval

    ● Ian Grieve ●  ● 0 Comments   ● 

    Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series of posts on the new features of, the soon to be released, Microsoft Dynamics GP 2015. As I have done for the last two releases, I intend to shadow these posts and add my own opinions on these new feature; the series index is here.

    The third Feature of the Day is Receivables Management Batch Approval. This feature is an enhancement to the new Workflow 2.0 module introduced in Microsoft Dynamics GP 2013 R2 which allows users to control the processing of Receivables Management batches in a more streamlined fashion.

    Receivables Management Batch Approval allows users to create mandatory workflow processes around Receivables transaction batches. These mandatory processes need to be completed before transactions can be posted. The workflow processes are defined in Workflow Maintenance (Administration area page ¯ Setup ¯ Company ¯ Workflow ¯ Workflow Maintenance):

    Workflow Maintenance

    The workflow status appears on the window and actions can be taken directly from those windows, from an email or from a Navigation List.

    The new Workflow 2.0 is far, far simpler and easier to implement and use.

    The roles targeted by this feature are:

    • All

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

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