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:

CREATE TABLE #UploadData
	(USCATVAL VARCHAR(100)
	,USCATNUM VARCHAR(1)
	,Image_URL VARCHAR(300)
	,UserCatLongDescr VARCHAR(300))
GO

BULK INSERT
	#UploadData
FROM
	'c:\temp\UserCategories.csv'
WITH
	(FIELDTERMINATOR = ','
	,ROWTERMINATOR = '\n')
GO

INSERT INTO IV40600
	(USCATVAL
	,USCATNUM
	,Image_URL
	,UserCatLongDescr)
	
	(SELECT
		LEFT(UD.USCATVAL, 10)
		,LEFT(UD.USCATNUM, 1)
		,LEFT(UD.Image_URL, 254)
		,LEFT(UD.UserCatLongDescr, 254)
	FROM
		#UploadData AS UD
	WHERE (SELECT
			COUNT(IV.USCATVAL)
		FROM
			IV40600 AS IV
		WHERE
			IV.USCATVAL = UD.USCATVAL
		AND
			IV.USCATNUM = UD.USCATNUM) = 0)
GO

DROP TABLE #UploadData
GO

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: Connecting Outlook”

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.

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: Installation”

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.

hMailServer
Installation
Database Configuration
hMailServer Administrator Configuration
Connecting Outlook
Connect Outlook 2019 Professional

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 “Hands On With Microsoft Dynamics GP 2015 R2: Install GP Add-in For Microsoft Word”