SQL View For Vendor Email Addresses

Microsoft Dynamics GPI produced this view to return vendor email address for a client a while ago; I don’t typically use this view by itself, but instead combine it with GP tables to produce a larger SmartList report using either SmartList Designer or SmartList Builder.

CREATE VIEW uv_AZRCRV_VendorInternetAddresses
	['Internet Addresses'].Master_ID AS 'Vendor ID'
	,['Internet Addresses'].ADRSCODE AS 'Address Code'
	,['Internet Addresses'].EmailToAddress AS 'Email To Address'
	,['Internet Addresses'].EmailCcAddress AS 'Email Cc Address'
	,['Internet Addresses'].EmailBccAddress AS 'Email Bcc Address'
	,['Internet Addresses'].INET1 AS 'Email'
	SY01200 AS ['Internet Addresses']
	['Internet Addresses'].Master_Type = 'VEN'


SQL View For Customer Item Link From SOP Transaction Line

Microsoft Dynamics GPThis one came up from a query a client had about linking a sales order transaction line to the customer item in SmartList Builder. The problem is that to join two tables together, you need all of the key fields to be on the same table; unfortunately, with the SOP Transaction table, this isn’t the case when you want to link to the customer item.

SOP10100 (Sales Transaction Work) holds the CUSTNMBR (Customer Number), but SOP10200 (Sales Transaction Amounts Work) holds the ITEMNMBR (Item Number) which are both needed to link to SOP60300 (Sales Customer Item Cross Reference) which holds the customer item number and description.

While there may be a way to do this in SmartList Builder I’ve not been able to work it out (other than using two calculated fields), it is easier, quicker and more reusable, to create a simple SQL View which returns the relevant information.

In this case the view I created works only for transactions which are at a status of work:

CREATE VIEW uv_PI_SOPCustomerItemLink
		SOP10200 AS SOP102
		SOP10100 AS SOP101
				AND SOP101.SOPOwner = SOP102.SOPOwner
		SOP60300 AS SOP603


The SQL above includes the Grant statement used to add select permissions for the DYNGRP.

My MVP Renewed for 2015 and David Musgrave Awarded

Microsoft MVPWell, I received the notification email yesterday afternoon that I had been awarded the Microsoft Most Valuable Professional award for the third year running. It’s really niec to receive the award as it shows I am still contributing in a meaningful way to the Dynamics GP community.

There was another new MVP for Dynamics GP added to the roster this month as well; David Musgrave. Previously David was ineligible for the award as he worked directly for Microsoft, but as of October last year he has been independent again and running Winthrop Development Consultants (the company responsible for GP Power Tools, formerly known as the Support Debugging Tool).

David has always been a great contributor to the Dynamics GP community and I’m delighted to see that this has been officially recognised by Microsoft with his MVP award.

Insert Inventory User Categories From CSV

Microsoft Dynamics GPI have had this script for quite a while and have used it a number of times for different clients when implementing the Inventory Control module in Microsoft Dynamics GP.

One client who was using Inventory was entering a lot of user categories, mistakenly entered the description into the Image field. In that case I did not know that they were populating the User Categories or I would have offered this script to them to save time.

To use the script you need a CSV file with four columns: User Category Value (the ID of the item you want to load), User Category Number (which of the user categories into which the row is to be loaded), Image URL and Description:

	,Image_URL VARCHAR(300)
	,UserCatLongDescr VARCHAR(300))


		,LEFT(UD.Image_URL, 254)
		,LEFT(UD.UserCatLongDescr, 254)
		#UploadData AS UD
			IV40600 AS IV

DROP TABLE #UploadData

You will need to change the highlighted line to the location of your CSV file. As always before running a script on live, test it in a test company first and have a good backup of your database.

hMailServer: Connecting Outlook

Microsoft Dynamics GPOver the last three posts, I have shown how to install and configure hMailServer for use so it can be used to send emails for Microsoft Dynamics GP’s Workflow 2.0 module. in this, the final post in the series I’m going to show how to configure one of the created email accounts in Microsoft Outlook.

To create the new account, click the File tab and then on the + Add Account button:

Account Information

Continue reading

hMailServer: Database Configuration

Microsoft Dynamics GPIn the last post I showed the installation of hMailServer; in this one I’m going to cover the database setup.

If you followed the last post you should have the hMailServer password window on screen. If not start the hMailServer Database Setup utility from the start menu.

Enter the admin password configured during the installation and click OK:

hMailServer password

on the Welcome step, click Next:

hMailServer Database Setup - Step 1 of 7: Welcome

Choose the database server Owner; as I am installing this on a Dynamics GP test system which has a full SQL Server installation, I have selected the Use external database engine (MSSQL, MySQL or PostgreSQL) radion button. Click Next:

Setup - hMailServer: Select database server Owner

This is a new installation of hMailServer so leave the Create a new hMailServer database and click Next:

hMailServer Database Setup - Step 2 of 7: Select option

Leave the Microsoft SQL Server option selected and click Next:

hMailServer Database Setup - Step 3 of 7: Select database server Owner

Enter the Database server address (this can be either the Server Name or IP Adress), enter a Database name and choose your Authentication method.

During an installation, I typically select Use server authentication and enter the sa username and password. I do this to ensure the user I am using has all of the necessary permissions to create a database and all objects.

Click Next to proceed:

hMailServer Database Setup - Step 4 of 7: Enter sever connection information

You can then configure the hMailServer service dependency if the mail server is the same machine as the SQL Server (which in this example it is not).

Click NMext to proceed:

hMailServer Database Setup - Step 5 of 7: Set hMailServer service dependency

On the Finish stage, click Next to start the installation:

hMailServer Database Setup - Step 6 of 7: Finish

Once the installation is complete, click Close:

hMailServer Database Setup - Step 7 of 7: Completed

A final step allowing the hMailServer to be run will be displayed:

Setup - hMailServer Database Setup: Completing the hMailServer Setup Wizard

I’ll be covering the hMailServer Administrator in the next post in this series.

Click to show/hide the hMailServer Series Index

hMailServer: Installation

Microsoft Dynamics GPhMailServer is a small, lightweight mail server with POP3 and SMTP. I have been using it for a while on test or demo systems where I either don’t have access to the company Exchange server or don’t want to use it as the demo system may not be connected to the company network when I am out of the office.

To install hMailServer, download and run the installer.

On the Welcome step, click Next:

Setup - hMailServer: Welcome to the hMailServer Setup Wizard

Continue reading

hMailServer: Series Index

Microsoft Dynamics GPWhen demoing Microsoft Dynamics GP it is often necessary to demonstrate a few functions which integrate with email. With the email document functionality introduced in Microsoft Dynamics GP 2010 R2, being able to have the email appear in an Outlook client without a mail server connected was sufficient because you could still open the email and show the attachment.

However, with the introduction of Workflow 2.0 in Microsoft Dynamics GP 2013 R2 it is actually necessary for Workflow 2.0 to be able to connect to an SMTP server to send an email and also for the logged in user to receive the email.

We typically do the demo using a virtual machine with everything required installed; because of Workflow 2.0 this now also includes a mail server. The VM already has SQL Server, Dynamics GP, Web Services, Web Client, Management Reporter and other services and applications installed so I don’t want to use something like Microsoft Exchange which is far larger and resource heavy than is needed.

Instead I have started using hMailServer which is a small and lightweight mail server with both POP3 and SMTP.

This is short series of posts where I’ll cover the installation, configuration and connection of an Outlook client, but please bear in mind this is for a self-contained test system and not a production one.

Click to show/hide the hMailServer Series Index

Hands On With Microsoft Dynamics GP 2015 R2: Install GP Add-in For Microsoft Word

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 was released at the end of May. As I mentioned at the time, I was away from home for work and rather busy otherwise so this series of posts has been somewhat delayed.

However, I am now getting my hands on GP 2015 R2 and thought I would do my usual set of posts on the installation of the various components and also the usage of the new functionality. This is going to be quite a long series of posts, so keep checking back regularly.

When using the Word Templates to replace the standard reports for printing reports such as invoices, remittances or purchase orders you can do changes to the format or layout using the standard functionality of Microsoft Word.

However, if you want to add additional fields then you need to install the Microsoft Dynamics GP Add-in for Microsoft Word. Do this by launching the Microsoft Dynamics GP setup utility from the downloaded installation media and, under Additional Products, click on Microsoft Dynamics GP Add-in for Microsoft Word:

Microsoft Dynamics GP 2015 setup utility

Continue reading