MS Connect Suggestion: Add Bar Code Field to Item Maintenance

● Ian Grieve ●  ● 3 Comments  ● 

Microsoft Dynamics GPMVP Belinda Allen has posted a suggestion to MS Connect asking Microsoft to add a bar code field to Item Maintenance, Item Transaction Inquiry and Stock Count Entry.

I think this would be a good additin, so please take a look and cast your vote.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Microsoft Dynamics GP Macro Error: “Keyword or punctuation expected, but not found (Line #2)”

● Ian Grieve ●  ● 3 Comments  ● 

Microsoft Dynamics GPWhile implementing Microsoft Dynamics GP for a client earlier this year, we used macros to load Assembly Transactions into Dynamics GP as there is no integration available in Integration Manager. The macros were created on the clients development server, tested and then migrated to the live server.

However, despite the development and live servers having exactly the same Windows and Dynamics GP configurations, the following error was generated on the live server when trying to run the macro:

Microsoft Dynamics GP: Keyword or punctuation expected, but not found. (Line #2)

Microsoft Dynamics GP

Keyword or punctuation expected, but not found. (Line #2)

# DEXVERSION=11.00.0359.000 2 2
CheckActiveWin dictionary 'default'  form bmTrxEntry window bmTrxEntry 
  TypeTo field 'TRX ID' , '262307A'
  MoveTo field 'TRX Date'

The code displayed above is from the macro file; the problem was the key words (such as dictionary) were not capitalised when the macro was recorded and tested on the development server, but needed to be capitalised for the live server. To get the capitalisation correct, I recorded a macro on live and then used the source CSV file to create a new macro by using the newly recorded one as a template for the mail merge.

It did mean that this was not tested the way the previous one was, but we’d proved the method on test and this was deemed acceptable.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , ,  ● Permalink ● Shortlink ●

SQL View To Allow Customer/Item Link In SmartList Builder

● Ian Grieve ●  ● 4 Comments  ● 

Microsoft Dynamics GPOn this one I am open to someone telling me that there is actually a way to do this out of the box, but I haven’t been able to find it. On a SmartList I was creating, I needed to pull the customer item number and description through to a SOP transaction SmartList, but couldn’t find any table to link with.

As a workaround, I created this view which contains the required link using the CUSTNMBR from SOP10100 (Sales Transaction Work) and the ITEMNMBR from SOP10200 (Sales Transaction Amounts Work):


CREATE VIEW uv_AZRCRV_CustomerItemLink AS
/*
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
		SOP102.SOPNUMBE
		,SOP102.SOPTYPE
		,SOP102.LNITMSEQ
		,SOP101.CUSTNMBR
		,SOP102.ITEMNMBR
	FROM
		SOP10200 AS SOP102 WITH (NOLOCK)
	INNER JOIN
		SOP10100 AS SOP101 WITH (NOLOCK)
			ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE
	INNER JOIN
		SOP60300 AS SOP603 WITH (NOLOCK)
			ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GO

GRANT SELECT ON uv_AZRCRV_CustomerItemLink TO DYNGRP
GO
● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL View For Customer Item Link From SOP Transaction Line

● Ian Grieve ●  ● 2 Comments  ● 

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
AS
	SELECT
		SOP101.CUSTNMBR
		,SOP102.ITEMNMBR
		,SOP603.CUSTITEMNMBR
		,SOP603.CUSTITEMDESC
	FROM
		SOP10200 AS SOP102
	INNER JOIN
		SOP10100 AS SOP101
			ON SOP101.SOPNUMBE = SOP102.SOPNUMBE
				AND SOP101.SOPOwner = SOP102.SOPOwner
	INNER JOIN
		SOP60300 AS SOP603
			ON SOP603.CUSTNMBR = SOP101.CUSTNMBR
				AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GO

GRANT SELECT ON uv_PI_SOPCustomerItemLink TO DYNGRP
GO

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

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , ,  ● Permalink ● Shortlink ●

Insert Inventory User Categories From CSV

● Ian Grieve ●  ● 1 Comment  ● 

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.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

Update Setup Mistake On Inventory User Categories

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPWe implemented Microsoft Dynamics GP for a client a while ago; they are quite heavy users of the Inventory Control module.

Unfortunately, when they configured the User Categories in Inventory Control Setup (Inventory ¯ Setup ¯ Inventory Control) the description for the category was entered in the Image field instead of the Description field.

Rather than making them go through and manually change all of the categories that had been defined, I put together a small script to do the job.

The script checks if the length of the entry in the Image field is greater than zero and that the Description has not been set before doing the update.

UPDATE
	IV40600
SET
	UserCatLongDescr = Image_URL
	,Image_URL = ''
WHERE
	LEN(RTRIM(Image_URL)) > 0
 AND 
	LEN(RTRIM(UserCatLongDescr)) = 0
GO

As I was writing this script we encountered the same setup issue on a client installation we took over from another partner so this looks like it may be more common that I thought it would be; caused I think be the fact the window has the Image field visible by default and not the Description which people expect.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2015 R2 Feature of the Day: Historical Received Not Invoiced Report

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The fifteenth Feature of the Day is Historical Received Not Invoiced Report.

A new SQL Reporting Services Report based off the current Received Not Invoiced report but add new functionality by adding a cutoff date based on transaction date or GL Post Date to allow customers to use the report for Historical purposes. This report will allow users to see what was received into inventory but not invoiced yet as of a specific date.

Historical Received Not Invoiced Report SSRS Report

These reports are becoming increasingly popular with clients as they look far better than the “very white” ones GP has traditionally shipped with. The only downside for someone who isn’t in the US is that they ship with a default language of en_US so all values come out as USD. I have some PowerShell scripts I have been working on to download, update the language and upload the reports from SQL Server Reporting Services. I need to revisit and see if I can knock them into shape to post online; I’m not experienced with PowerShell so it is taking me awhile. If someone wants to volunteer to help, I would appreciate it.

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , ,  ● Permalink ● Shortlink ●

Slow Opening Windows In Microsoft Dynamics GP

● Ian Grieve ●  ● 3 Comments  ● 

Microsoft Dynamics GPI recently implemented Microsoft Dynamics GP for a client who is a UK subsidiary of an American company. This client is a heavy user of the Inventory Control module with over 30,000 items (you’ll see the relevance of this soon).

Shortly after the go-live, users started reporting that windows were opening very slowly; for example, it was taking almost 2 minutes to open the Sales Transaction Entry window. I sat with one of the users and looked at the problem with them; it took a few minutes, due to the slowness, of checking various windows before realising that it was the windows with an Item Number field on them which were slow opening.

I did a quick search online and found a post on Dynamics Code Blocks by Tim Wappat where he had encountered a similar issue which was being caused by the AutoComplete function; this would make sense as by default the AutoComplete will remember 10,000 entries per field.

Which with over 30,000 items and a couple hundred sales orders being processed everyday meant the AutoComplete was quickly building up for each user.

Tim’s solution seemed to be a little more convoluted than I woulr have expected and involved deleting files in the Windows profile. I did a test with one of the users, by deleting the AutoComplete entries via User Preferences (Microsoft Dynamics GP menu ¯ User Preferences) and clicking the AutoComplete button (ringed in red):

User Preferences

Continue reading → Slow Opening Windows In Microsoft Dynamics GP

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL Query To Determine Quantity To Order

● Ian Grieve ●  ● 3 Comments  ● 

I recently had cause to knock together a fairly simple SQL script to determine the quantity to order based on Sales transactions in the system taking into account the Order Point Quantity and Quantity To Order for the Item/Site combination in the Inventory module:

SELECT
   SOP10200.LOCNCODE AS 'Site'
   ,SOP10200.ITEMNMBR AS 'Item Number'
   ,SUM(SOP10200.QUANTITY) AS 'Quantity Required'
   ,IV00102.ORDRUPTOLVL - SUM(SOP10200.QTYTORDR) AS 'Quantity To Order'
FROM
   SOP10200 (NOLOCK)
LEFT OUTER JOIN
   IV00102 (NOLOCK)
      ON IV00102.ITEMNMBR = SOP10200.ITEMNMBR AND IV00102.LOCNCODE = SOP10200.LOCNCODE
WHERE
   SOP10200.QTYTORDR <= ORDRPNTQTY AND SOP10200.QTYTORDR <> 0
GROUP BY
   SOP10200.ITEMNMBR, SOP10200.LOCNCODE, IV00102.ORDRUPTOLVL
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2013 R2 Feature of the Day: Assign An Item To Multiple Sites

● Ian Grieve ●  ● 0 Comments  ● 

The Inside Microsoft Dynamics GP blog started a series of Microsoft Dynamics GP 2013 R2 Feature of the day posts the other day. As they did with the Microsoft Dynamics GP 2013 Feature of the Day posts they are doing them as short posts containing a PowerPoint slide show. I am translating these from the PowerPoints into posts; you can find my series index here.

The thirtieth Feature of the Day covered is Assign An Item To Multiple Sites. This feature will allow the user to select an item and assign it to one, multiple or all sites quickly and easily. The user can also select key item/site default settings for Item Print Option, Primary Vendor and Landed Cost Group during the assignment process.

When I started looking at this feature, it took me a few minutes to actually find it. To assign items to multiple sites open Item Quantities Maintenance (Inventory ¯ Cards ¯ Quantities/Sites) and click the Go To’s button on the ribbon and select Site Item Assignments:

Item Quantities Maintenance

Continue reading → MDGP 2013 R2 Feature of the Day: Assign An Item To Multiple Sites

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●