MS Connect Suggestion: Add Workflow To Receivings Transaction Entry and Enter/Match Invoice

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPThis has come up from a couple of different clients in the last few weeks. It would be good if approval workflows could be added to Receivings Transaction Entry and also the Enter/Match Invoices windows.

While there is workflow on the PO, a PO might not be fully received/invoiced so it would be good to have approval of the receivings document or the invoice.

The MS Connect suggestion can be found here.

Please take a look and cast your vote.

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

MS Connect Suggestion: Add Bar Code Field to Item Maintenance

● Ian Grieve ●  ● 3 Comments  ● 

Microsoft Dynamics GPMVP Belinda Allen has posted a suggestion to MS Connect asking Microsoft to add a bar code field to Item Maintenance, Item Transaction Inquiry and Stock Count Entry.

I think this would be a good additin, so please take a look and cast your vote.

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

    New Year eBook Sale at azurecurve Publishing

    ● Ian Grieve ●  ● 4 Comments  ● 

    Microsoft Dynamics GPFrom now until the 5th January 2016 the ebook versions of Implementing the Microsoft Dynamics GP Web Client and Microsoft Dynamics GP Workflow 2.0 can each be bought for the discounted price of $9.99 using coupon code NEWYEAR2016.

    Implementing the Microsoft Dynamics GP Web Client Microsoft Dynamics GP Workflow 2.0

    Make sure you enter the coupon code NEWYEAR2016 at the checkout to get the discount.

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

    Customization Maintenance: “Unable to open customizations dictionary”

    ● Ian Grieve ●  ● 4 Comments  ● 

    Microsoft Dynamics GPI ‘ve been involved in quite a few upgrades recently and have had a few issues arise. One of them was on one site after I had installed Microsoft Dynamics GP, the web client and web services and also Management Reporter.

    I did some work to upgrade the customised forms and reports and then imported them through Customisation Maintenance (Microsoft Dynamics GP menu ¯ Tools ¯ Customisation ¯ Customisation Maintenance). Or at least I tried to as I got an error with the SOP_Entry form:

    Errors

    Continue reading → Customization Maintenance: “Unable to open customizations dictionary”

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

    Changing The Logical File Names Of A SQL Database

    ● Ian Grieve ●  ● 3 Comments  ● 

    Microsoft Dynamics GPWhen a database is created, it has a logical name assigned to it which will match the physical name. However, when working with Microsoft Dynamics GP, we often create both a live and test database and then populate the settings in the live database and replicate over the top of the test one.

    Or on occasion we have created a template database which then gets copied when a new company is created (this method is often used for clients who have a substantial amount of setup in third party modules which the PSTL Company Copy doesn’t cater for.

    The problem with both of these is that when a database is restored elsewhere it brings it’s logical name with it; meaning a mismatch between the logical and physical names which causes problems when backing up and restoring databases.

    However, all is not lost; it is possible to change the logical name of a database using a simple SQL script. The script, below, has two ALTER DATABASE commands, one for the data file and the other for the log file.

    I am changing the logical name from GPST15R2 to GPSP15R2 on both files (see highlighted text):

    /*
    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).
    */
    ALTER DATABASE
    	P15R2
    MODIFY FILE 
    	(
    	NAME = [GPST15R2Dat.mdf]
    	,NEWNAME = [GPSP15R2Dat.mdf]
    	)
     GO
     ALTER DATABASE
    	P15R2
    MODIFY FILE 
    	(
    	NAME = [GPST15R2Log.ldf]
    	,NEWNAME = [GPSP15R2Log.ldf]
    	)
    GO

    As always when running a SQL script against a database, make sure you have a good backup and perform a test afterward to make sure there are no problems.

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

    Integration Manager Error: Error Executing Script ‘Before Integration’ Line 17: – Object required: ‘gpObj’

    ● Ian Grieve ●  ● 4 Comments  ● 

    Microsoft Dynamics GPOne of Perfect Image’s clients is merging with another organisation and I’ve been onsite a few times to help them configure the new companies and import data. One of the items on my list was to use Integration Manager to import the new chart of accounts. While doing this I used VBA to extend the integration to map between the chart of accounts in the new system to the new format and numbering wanted in Microsoft Dynamics GP.

    It was when I came to test the integration with the mapping active that I encountered my problem:

    ERROR: Error Executing Script 'Before Integration' Line 17: - Object required: 'gpObj'

    Show/Hide Error Message

    ERROR: Error Executing Script 'Before Integration' Line 17: - Object required: 'gpObj'
    Error Executing Script 'Before Integration' Line 17: - Object required: 'gpObj'
    Object required: 'gpObj'
    Integration Failed
    Integration Results
        0 documents were read from the source query.
        0 documents were attempted:
            0 integrated without warnings.
            0 integrated with warnings.
            0 failed to integrate.

    Continue reading → Integration Manager Error: Error Executing Script ‘Before Integration’ Line 17: – Object required: ‘gpObj’

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

    Navigation List Error: “Cannot insert the value NULL into column ‘VENDNAME'”

    ● Ian Grieve ●  ● 4 Comments  ● 

    Microsoft Dynamics GPThis came up a few days ago at a client site during an upgrade being done by a colleague and then again when I was running a training session.

    When I was doing the training I was explaining what a Temporary Creditor (or Vendor for the American readers) was and why I hated them so much. As I was talking I was showing that even through the temporary creditor itself had been deleted, the history for it remained.

    It was when I tried to open the Payables Transactions navigation list that I got the below error:

    Cannot insert the value NULL into column 'VENDNAME'

    Microsoft Dynamics GP

    [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into 'VENDNAME', table 'tempdb.dbo.##181163', column does not allow nulls. INSERT fails.[Microsoft][SQL Server Native Client 11][SQL Server]Cannot insert the value

    Continue reading → Navigation List Error: “Cannot insert the value NULL into column ‘VENDNAME’”

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

    eConnect Incoming Queue In Microsoft Dynamics GP 2015 Does Not Work

    ● Ian Grieve ●  ● 3 Comments  ● 

    Microsoft Dynamics GPI did an upgrade of a client from Microsoft Dynamics GP 2010 R2 to Dynamics GP2015 recently and encountered a problem with eConnect.

    This particular client uses the eConnect incoming queue to integrate journals and payables invoices into Dynamics GP from a housing management system.

    After installing eConnect and configuring the incoming queue I set about doing a test to ensure it was working.

    Unfortunately, it didn’t.

    Continue reading → eConnect Incoming Queue In Microsoft Dynamics GP 2015 Does Not Work

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