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.
Over 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:
Select the server you want to connect to and click Connect
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:
on the Welcome step, click Next:
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:
This is a new installation of hMailServer so leave the Create a new hMailServer database and click Next:
Leave the Microsoft SQL Server option selected and click Next:
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:
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:
On the Finish stage, click Next to start the installation:
Once the installation is complete, click Close:
A final step allowing the hMailServer to be run will be displayed:
I’ll be covering the hMailServer Administrator in the next post in this series.
hMailServer 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:
When 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 Administrator Configuration|
Microsoft 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: