SQL script to insert test email address to all customers

Microsoft Dynamics GPBack 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 an IT Professional (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' 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 an IT Professional (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.

SQL script to populate the Payables Distribution Type SETP (PM40103) table

Microsoft Dynamics GPI recently wrote a report for a client which included payables distributions; in order to output the distribution type, I joined to the Payables Distribution Type SETP (PM40103) table. Unfortunately, when the report was deployed, it didn’t work: all of the rows had a blank distribution type.

When I investigated I found that the Payables Distribution Type SETP (PM40103) table was empty. My demo environment is running Fabrikam and had fifteen rows of data, so I decided to migrate my table content to the clients system using a simple SQL script to insert the extracted rows.

The script is a simple one which doesn’t do any checking so confirm the table is empty before running.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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). */
INSERT INTO PM40103 (DISTTYPE, DSNMSHRT, DSNMLONG) VALUES (1, 'CASH', 'CASH') ,(2, 'PAY', 'Accounts Payable') ,(3, 'AVAIL', 'Discounts Available') ,(4, 'TAKEN', 'Discounts Taken') ,(5, 'FNCHG', 'Finance Charge') ,(6, 'PURCH', 'Purchases') ,(7, 'TRADE', 'Trade Discounts') ,(8, 'MISC', 'Miscellaneous') ,(9, 'FREIGHT', 'FREIGHT') ,(10, 'TAXES', 'TAXES') ,(11, 'WRITE', 'Write Offs') ,(12, 'OTHER', 'OTHER') ,(13, 'GST', 'GST') ,(14, 'PPS', 'PPS') ,(15, 'UNIT', 'UNIT')

SmartList searching with wildcards

Microsoft Dynamics GPI 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.

Windows rotation lock missing

WindowsI use a small convertible laptop (the screen rotates through 360o to turn into a tablet) for a lot of web browsing; occasionally I convert it into a tablet and switch allow the screen to rotate from landscape to portrait mode. However, when I tried to do this by switching off the Rotation Lock option the other day, the option in the action centre was missing; it is usually between Tablet Mode and Network:

Rotation Lock missing from the action centre

Continue reading “Windows rotation lock missing”

Bad Parameter in refreshable Excel report query

Microsoft Dynamics GPOver 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:

Excel error message

Microsoft Excel

Bad parameter type. Microsoft Excel is expecting a different ind of value than what was provided.

Continue reading “Bad Parameter in refreshable Excel report query”

Workflows available in Microsoft Dynamics GP

Microsoft Dynamics GPSince 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:

Available workflow approval types

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.

Fortunately, the company for which I work, ISC Software, are both a VAR and an ISV with a number of products and services available.

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.

Record short training or demo videos

Useful ApplicationsAfter posting about a couple of useful websites, I thought I’d do a useful application. Flashback Express can be sued to record short training or demo videos. While it can be used to capture the webcam, I have been using it to record the screen and sounds; for one video I needed to record both myself and someone else speaking and Flashback Express easily allowed me to capture sound from both the microphone and speakers.

Videos are recorded in a native format, but can be both exported to WMV or MP4 as well as exported directly to a YouTube channel.

Flachback Express

Continue reading “Record short training or demo videos”

Update Posting Setup in all companies to use Posting Date from Transaction

Microsoft Dynamics GPI 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 (Administration area page » Setup » Posting » Posting) 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 an IT Professional (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.

Use SQL to generate a macro to create Vendor Items

Microsoft Dynamics GPOn 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 an IT Professional (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

Features of SalesHQ from ISC Software: Free Trial [Video]

SalesHQ PortalThis post is part of the Features of SalesHQ from ISC Software, as well as part of the over-arching GP Elementz from ISC Software series.

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:

GP Elementz from ISC Software

GP Elementz from ISC Software
What add-ons are available?
GP Elementz Portals
Features of PurchaseHQ from ISC Software: Introduction to PurchaseHQ
Features of PurchaseHQ from ISC Software: Introduction to PurchaseHQ [Video]
Features of PurchaseHQ from ISC Software: Secure access
Features of PurchaseHQ from ISC Software: Secure access [Video]
Features of PurchaseHQ from ISC Software: Configuration
Features of PurchaseHQ from ISC Software: Configuration [Video]
Features of PurchaseHQ from ISC Software: Setup images
Features of PurchaseHQ from ISC Software: Setup images [Video]
Features of PurchaseHQ from ISC Software: Granting users access
Features of PurchaseHQ from ISC Software: Granting users access [Video]
Features of PurchaseHQ from ISC Software: Enter a Purchase Requisition
Features of PurchaseHQ from ISC Software: Enter a Purchase Requisition [Video]
Features of PurchaseHQ from ISC Software: Enter a Purchase Order
Features of PurchaseHQ from ISC Software: Enter a Purchase Order [Video]
Features of PurchaseHQ from ISC Software: View my orders
Features of PurchaseHQ from ISC Software: View my orders [Video]
Features of PurchaseHQ from ISC Software: Workflow Integration
Features of PurchaseHQ from ISC Software: Workflow Integration [Video]
Features of PurchaseHQ from ISC Software: Document Approval
Features of PurchaseHQ from ISC Software: Document Approval [Video]
Features of PurchaseHQ from ISC Software: Receive Goods
Features of PurchaseHQ from ISC Software: Receive Goods [Video]
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) Overview
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) Overview [Video]
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) configuration
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) configuration
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) configuration [Video]
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) Create Approvers
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) Create Approvers [Video]
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) Configure Delegation
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) Configure Delegation [Video]
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) submit transaction for approval
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) submit transaction for approval [Video]
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) in portal
Features of PurchaseHQ from ISC Software: Supplier Invoice Approval (Non-Workflow) in portal [Video]
Features of PurchaseHQ from ISC Software: Free Trial Available
Features of PurchaseHQ from ISC Software: Free Trial Available [Video]
Features of CustomerHQ from ISC Software: Introduction to CustomerHQ
Features of CustomerHQ from ISC Software: Introduction to CustomerHQ [Video]
Features of CustomerHQ from ISC Software: Secure access
Features of CustomerHQ from ISC Software: Secure access [Video]
Features of CustomerHQ from ISC Software: Configuration
Features of CustomerHQ from ISC Software: Configuration [Video]
Features of CustomerHQ from ISC Software: Change images
Features of CustomerHQ from ISC Software: Change images [Video]
Features of CustomerHQ from ISC Software: Manage customer accounts
Features of CustomerHQ from ISC Software: Manage customer accounts [Video]
Features of CustomerHQ from ISC Software: Manage salesperson accounts
Features of CustomerHQ from ISC Software: Manage salesperson accounts [Video]
Features of CustomerHQ from ISC Software: Account Summary
Features of CustomerHQ from ISC Software: Account Summary [Video]
Features of CustomerHQ from ISC Software: View transactions
Features of CustomerHQ from ISC Software: View transactions [Video]
Features of CustomerHQ from ISC Software: View Statements
Features of CustomerHQ from ISC Software: View Statements [Video]
Features of CustomerHQ from ISC Software: Free Trial available
Features of CustomerHQ from ISC Software: Free Trial available [Video]
Features of SalesHQ from ISC Software: Introduction to SalesHQ
Features of SalesHQ from ISC Software: Introduction to SalesHQ [Video]
Features of SalesHQ from ISC Software: Secure access
Features of SalesHQ from ISC Software: Secure access [Video]
Features of SalesHQ from ISC Software: Configuration
Features of SalesHQ from ISC Software: Configuration [Video]
Features of SalesHQ from ISC Software: Change images
Features of SalesHQ from ISC Software: Change images [Video]
Features of SalesHQ from ISC Software: Manage customer accounts
Features of SalesHQ from ISC Software: Manage customer accounts [Video]
Features of SalesHQ from ISC Software: Manage salesperson accounts
Features of SalesHQ from ISC Software: Manage salesperson accounts [Video]
Features of SalesHQ from ISC Software: Account Summary
Features of SalesHQ from ISC Software: Account Summary [Video]
Features of SalesHQ from ISC Software: View transactions
Features of SalesHQ from ISC Software: View transactions [Video]
Features of SalesHQ from ISC Software: View statement
Features of SalesHQ from ISC Software: View statement [Video]
Features of SalesHQ from ISC Software: Inventory Lookup
Features of SalesHQ from ISC Software: Inventory Lookup [Video]
Features of SalesHQ from ISC Software: Price Lookup
Features of SalesHQ from ISC Software: Price Lookup [Video]
Features of SalesHQ from ISC Software: Add Prospect
Features of SalesHQ from ISC Software: Add Prospect [Video]
Features of SalesHQ from ISC Software: Create quote
Features of SalesHQ from ISC Software: Create quote [Video]
Features of SalesHQ from ISC Software: Create order
Features of SalesHQ from ISC Software: Create order [Video]
Features of SalesHQ from ISC Software: Create invoice
Features of SalesHQ from ISC Software: Create invoice [Video]
Features of SalesHQ from ISC Software: Free Trial
Features of SalesHQ from ISC Software: Free Trial [Video]
GP Elementz Add-ins
Features of Enhanced Notes from ISC Software: Introduction to Enhanced Notes
Features of Enhanced Notes from ISC Software: Introduction to Enhanced Notes [Video]
Features of Enhanced Notes from ISC Software: Individual notes
Features of Enhanced Notes from ISC Software: Individual notes [Video]
Features of Enhanced Notes from ISC Software: Categories/Subcategories
Features of Enhanced Notes from ISC Software: Categories/Subcategories [Video]
Features of Enhanced Notes from ISC Software: Assign tasks
Features of Enhanced Notes from ISC Software: Assign tasks [Video]
Features of Enhanced Notes from ISC Software: Available for reporting
Features of Enhanced Notes from ISC Software: Available for reporting [Video]
Features of Enhanced Notes from ISC Software: Free Trial
Features of Enhanced Notes from ISC Software: Free Trial [Video]
Features of Financial Elementz from ISC Software: Introduction to Financial Elementz
Features of Financial Elementz from ISC Software: Introduction to Financial Elementz [Video]
Features of Financial Elementz from ISC Software: Available imports
Features of Financial Elementz from ISC Software: Available imports [Video]
Features of Financial Elementz from ISC Software: General Ledger Code Import
Features of Financial Elementz from ISC Software: General Ledger Code Import [Video]
Features of Financial Elementz from ISC Software: Create General Ledger import
Features of Financial Elementz from ISC Software: Create General Ledger import [Video]
Features of Financial Elementz from ISC Software: Run General Ledger import
Features of Financial Elementz from ISC Software: Run General Ledger import [Video]
Features of Financial Elementz from ISC Software: Create Payables Transaction import
Features of Financial Elementz from ISC Software: Create Payables Transaction import [Video]
Features of Financial Elementz from ISC Software: Run Payables Transaction import
Features of Financial Elementz from ISC Software: Run Payables Transaction import [Video]
Features of Financial Elementz from ISC Software: Create Manual Payments import
Features of Financial Elementz from ISC Software: Create Manual Payments import [Video]
Features of Financial Elementz from ISC Software: Run Manual Payments import
Features of Financial Elementz from ISC Software: Run Manual Payments import [Video]
Features of Financial Elementz from ISC Software: Create Receivables Transaction import
Features of Financial Elementz from ISC Software: Create Receivables Transaction Import [Video]
Features of Financial Elementz from ISC Software: Run Receivables Transaction import
Features of Financial Elementz from ISC Software: Run Receivables Transaction Import [Video]
Import Custom Company Colours into Security Informer Module for Microsoft Dynamics GP