Over the course of this series, the index, below, will automatically update with the new posts as they go live (unless you’re reading a syndicated version of this post in which case check back here).
The PO is shown as not submitted in the workflow status bar, but the button on the tool is a Recall button rather than a Submit one showing that the document has been submitted:
This post is part of the GP Elementz from ISC Software series.
There are a number of add-ins available from ISC Software for Microsoft Dynamics GP which have been written in Dexterity, which have the look and feel of standard windows. These add-ins are sold under the GP Elementz label.
There are five main Elementz products listed on the GP Elementz website:
- Enhanced Notes – Extends and secures standard notes by allowing multiple separate notes to be recorded against records. Includes user alerts, categorization and audit functionality.
- Financial Elementz – A collection of windows and reports that enhance standard financial Dynamics GP function.
- Distribution Elementz – A collection of windows & reports that extend Dynamics GP SOP, POP and inventory functions.
- Security Informer – Provides on-screen messages stating details of which window or report access is denied.
- Invoice Transformer – Consolidate or ‘mirror’ SOP invoices. Transfer item lines between SOP invoices.
I’m also going to take a look at one of the other GP Elementz products which is available and that is PO Management. This add-in provides an alternative to the standard SOP-POP Link which allows for partial invoicing of committed sales orders.
Back when this blog was a few months old, I posted a script on updating emails on vendors to keep emails internal for testing and have recently had the need to provide a customer a similar script for testing the emailing of invoices and statements.
This first script updates all existing records to replace the email address with one supplied by the customer (the highlighted email address should be changed to the required email address):
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 @TestEmail VARCHAR(100) SET @TestEmail = 'firstname.lastname@example.org' UPDATE SY01200 SET INET1 = @TestEmail ,EmailToAddress = @TestEmail ,EmailCcAddress = '' ,EmailBccAddress = '' WHERE Master_Type = 'CUS'
this second script will insert an email address against all customer addresses which don’t currently have an email (the highlighted email address should be changed to the required email address).
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 @TestEmail VARCHAR(100) SET @TestEmail = 'email@example.com' INSERT INTO SY01200 ( Master_Type ,Master_ID ,ADRSCODE ,INETINFO ,EmailToAddress ,EmailCcAddress ,EmailBccAddress ) --VALUES ( SELECT 'CUS' ,CUSTNMBR ,ADRSCODE ,'' ,@TestEmail ,'' ,'' FROM RM00102 AS ['Customer Master Address File'] WHERE ( SELECT COUNT(Master_ID) FROM SY01200 AS ['Internet Addresses'] WHERE Internet Addresses.Master_Type = 'CUS' AND Internet Addresses.Master_ID = ['Customer Master Address File'].CUSTNMBR AND Internet Addresses.ADRSCODE = ['Customer Master Address File'].ADRSCODE ) = 0 )
These scripts are only intended for use on a development or test system so please be careful when running them; as always with update scripts, I’d recommend having a good backup before running them just in case of problems.
I was recently showing some clients how to use wildcard searching in SmartListand looked up my old blog post to refresh mym memory. When I was searching for my page, I found one from Shawn Dorward which he posted a couple of years ago and which covered some additional searching which was available.
Shawn’s blog post contains a some really good example of searching and also covers a couple of items, such as all fields of which I was not aware.
Over the last few months, I’ve been doing work with clients on creating some refreshable Excel reports. Working on one of them, we had parameters for dates and added one for Vendor ID, which is when I encountered an error:
Bad parameter type. Microsoft Excel is expecting a different ind of value than what was provided.
Since Microsoft Dynamics GP 2013 R2 saw the introduction of a new Workflow module for approval of documents, almost every version of Dynamics GP since then has introduced new functionality to workflow; sometimes in the form of new approval types or integration with other modules such as Document Attachment.
This diagram shows the available workflow types, colour-coded by version introduced:
If you are looking to implement workflow, I have a book which can guide you though configuring the Microsoft Dynamics GP Workfow module from the basics of designing your workflow process, through building it and the ways that users can interact with the workflow for approvals.
The Workflow module is quite popular and becoming much more so with so many clients working in a distributed manner. There is one problem on the horizon and that is the retirement of TLS 1.0 and 1.1 which the Web Services for Microsoft Dynamics GP rely on. We are waiting for confirmation as to when a fix for this will be available, but I am expecting the fix to require an upgrade of Microsoft Dynamics GP which isn’t always quickly possible.
One of those products is PurchaseHQ which provides an Azure hosted web portal through which users can log on to do their purchasing series approvals. It fully integrates with the Workflow module in Microsoft Dynamics GP (blog and video) for document approvals (blog and video). The portal is quite a popular approach for organisations with a lot of approvals which need to be done as approvers have a web page they can logon to to see all of their approvals for all companies in one list, rather than receiving one email per approval.
I was recently doing some work for a client and an issue which came up was the posting date for transactions posted to the GL was not always correct. After some investigation we determined that they had their Posting Setup ( ) was set to use the posting date from the batch instead of the transaction.
As they had a substantial number of companies, and a lot of settings to change, they did not want to make this change manually. I’ve previously written a script to update some settings in Posting Setup in all companies so I was quickly able to amend the script for this change:
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 @SQL_Statement VARCHAR(1000) DECLARE cursor_InterID Cursor FOR SELECT INTERID FROM DYNAMICS..SY01500 Open cursor_InterID DECLAR @INTERID VARCHAR(100) FETCH NEXT FROM cursor_InterID INTO @INTERID WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) SET @SQL_Statement + 'UPDATE ' + RTRIM(@INTERID) + '.dbo.SY02300 SET UPSTDTFR = 1 WHERE UPSTDTFR = 0' exec (@SQL_Statement) FETCH NEXT FROM cursor_InterID INTO @INTERID END CLOSE cursor_InterID DEALLOCATE cursor_InterID
This will update all companies so I would recommend testing on a standalone test system before running on live and making sure you have a good backup before using the script.
On a recent project with a client to implement the Purchase Order Processing module, we needed to create a lot of vendor item numbers. I’m a big advocate of doing updates in the front-end where possible or using integration tools where they support standard functionality. This particular client didn’t have a suitable integration tool available for inserting vendor items, so instead I created a SQL script which would generate a Microsoft Dynamics GP macro which can be played back to insert the records.
The script below will link all active vendors with every active non-discontinued item; it can easily be amended to limit the selected vendors or items (which is how we used it by running it several times with different restrictions):
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). */ SELECT '# DEXVERSION=18.00.0021.000 2 2 CheckActiveWin dictionary ''default'' form ''IV_Item_Vendors'' window ''IV_Item_Vendors'' TypeTo field ''Item Number'' , ''' + RTRIM(REPLACE(ITEMNMBR, '''', '~"')) + ''' MoveTo field ''Vendor ID'' TypeTo field ''Vendor ID'' , ''' + RTRIM(REPLACE(VENDORID, '''', '~"')) + ''' MoveTo field ''Vendor Item Number'' CommandExec dictionary ''default'' form ''IV_Item_Vendors'' command ''Save Button_w_IV_Item_Vendors_f_IV_Item_Vendors'' ' FROM PM00200 AS ['PM Vendor Master'] CROSS JOIN IV00101 AS ['Item Master'] WHERE VENDSTTS = 1 AND ITEMTYPE <> 2 -- discontinued AND INACTIVE = 0 AND ( SELECT COUNT(*) FROM IV00103 WHERE VENDORID = ['PM Vendor Master'].VENDORID AND ITEMNMBR = ['Item Master'].ITEMNMBR ) = 0
Earlier today I published a Free Trial post, but I am experimenting with making video alternatives to the standard blog posts.
You can watch the GP Elementz – SalesHQ – Free Trial video: