MDGP 2018 R2 Feature of the Day: Exclude Items on HITB Report With Zero Quantity or Value

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 R2 on which I am following and adding commentary. The series index for this series of posts is here.

The tenth Feature of the Day is exclude items on HITB report with zero quantity or value

The Historical Inventory Trial Balance (HITB) report has two new options for Dynamics GP 2018 R2; when printing the HITB report you can now decide if you want to include/exclude items with a zero quantity or value. The new options are:

  • Include Items with Zero Quantity
  • Include Items with Zero Value

These items will be excluded on the report if the options are not checked; previously, by default, these items were included. Make sure the checkboxes are unmakred to exclude the items with either zero value or quantity:

Inventory Activity Report Options

This is a good update to the HITB; I have a number of clients with large number of items which will generally have a zero quantity of stock while having other items with stock levels they on which they would want to report. Being able to exclude these will make the report usable.

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

MDGP 2018 R2 Feature of the Day: Transaction Level Post Through General Ledger

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 R2 on which I am following and adding commentary. The series index for this series of posts is here.

The seventh Feature of the Day is transaction level post through general ledger. This feature is a new posting option in the Posting Setup window (Administration area page » Setup » Posting » Posting); when Allow Transaction Posting is marked, a new checkbox for Post Through General Ledger Files is now available:

Posting Setup

Continue reading “MDGP 2018 R2 Feature of the Day: Transaction Level Post Through General Ledger”

MDGP 2018 R2 Feature of the Day: Monthly Recurring Batches

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 R2 on which I am following and adding commentary. The series index for this series of posts is here.

The first Feature of the Day is monthly recurring batches. This new feature allows ou to, on both Monthly and Bi-monthly batches to mark a Use last day of month checkbox which will update the Posting Date to the last day of the month.

This functionality has been added to Receivables Management, Payables Management and Inventory Control:

Receivables Batch Entry

The example above, shows a receivabled batch dates 30/04/2027 which, with the new checkbox marked, will automatically set the posting date to the last day of the month. So, when the batch is posted the next posting date would be set to May 31.

My initial impression was that this would be a good addition, until I realised that it did not include the General Ledger Financial Batches. I can’t think of a single client who uses recurring batches in any of Receivables, Payables or Inventory.

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

Debugging Integration Manager Crashes

Microsoft Dynamics GPWe’ve had two separate clients recently log calls where Integration Manager was crashing while performing an integration.

The first client was doing an Inventory Items integration on Microsoft Dynamics GP 2015 R2 and the other was doing a Sales Transaction integration on Microsoft Dynamics GP 2016 R2.

Both of the integrations had been running fine for quite a long period of time before starting to crash; no changes had been made to either system and I’ve not been able to determine why Integration Manager (IM) has started crashing.

However, by the use of two ini file switches, we were able to determine what error was being encountered by IM resulted in a crash.

The two switches are already present in the ini file (C:\Program Files (x86)\Microsoft Dynamics\Integration Manager nn/Microsoft.Dynamics.GP.IntegrationManager.ini by default) whre the False value needs to be changed to True:

[IMGPPrv]
ShowDynamics=False
DoUIRedraw=False

When these switches are set to True, the Microsoft Dynamics GP windows are not hidden when the integration runs.

We found that for the Inventory Items integration, accounts either didn’t exist, didn’t allow account entry or were inactive; for the Sales Transaction integration, Analytical Accounting (AA) had been enabled for one of the accounts and the integration didn’t handle AA.

I created a VB script for the Inventory Items integration to check the accounts in the Before Document script and cancel the document if the account was going to be a problem.

MS Connect Suggestion: On Purchase Stop Summarising PR Lines

Microsoft Dynamics GPThis suggestion came about from an issue raised by a client while I was working with them to implement Purchase Order Processing with Workflow for approvals of purchase requisitions (PRs).

The issue encountered, is that when multiple lines for the same product at the same cost are added to the PR, approved and converted to PO, these lines are consolidated. This results in loss of information as only one item description or comments are pulled through to the PO.

To illustrate this, I created the following example.

First, create a PR with at least two lines. I have used the 2-A3284A product and changed the description on both rows:

Purchase Requisition Entry

Continue reading “MS Connect Suggestion: On Purchase Stop Summarising PR Lines”

Historical Excel Reporting Price Increase Coming

Microsoft Dynamics GPMark Polino has a set of Historical Excel Reports available for sale. The reports available are as follows:

  • Receivables Management Historical Aged Trial Balance
  • Payables Management Historical Aged Trial Balance
  • Historical Inventory Trial Balance
  • Historic Stock Status Report

Now is the ideal time to buy them, as the price is increasing on 1st April 2017.

SQL Script To Create macro To Activate BOMs

Microsoft Dynamics GPWhile implementing Microsoft Dynamics GP for a new client a while ago, we used Integration Manager to import over 100,000 Inventory items and then a SQL script to insert the 80,000 bill of materials (BOMs).

This worked well, in that it got all of the information loaded, but found that if we inserted the BOMs in this way, they could not be viewed in the BOM Inquiry window.

I used a SQL query to change the status from Active to Pending and then looked at the best way of changing them to Active through the Dynamics GP.

The only way to bulk change the data was by using a GP Macro; the usual way we do this is to do an extract of the data and then mailmerge this into a prerecorded macro in Microsoft Excel.

However, this can be avoided, by using the SQL select to not only get the data, but to output the macro at the same time:

/*
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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT DISTINCT
	'# DEXVERSION=11.00.0364.000 2 2
	CheckActiveWin dictionary ''default''  form bmBillMaintenance window bmBillMaintenance 
	  TypeTo field ''Item Number'' , ''' + RTRIM(BMH.ITEMNMBR) + '''
	  MoveTo field ''Bill Status'' item 0 
	  ClickHit field ''Bill Status'' item 2  # ''Pending'' 
	  MoveTo field ''Expansion Button 1'' 
	  ClickHit field ''Expansion Button 1'' 
	NewActiveWin dictionary ''default''  form bmBillMaintenance window ChangeStatus 
	  ClickHit field ''Bill Status'' item 1  # ''Active'' 
	  MoveTo field ''Process Button P'' 
	  ClickHit field ''Process Button P'' 
	NewActiveWin dictionary ''default''  form bmBillMaintenance window bmBillMaintenance 
	  MoveTo field ''Save Button'' 
	  ClickHit field ''Save Button'' 
	'
FROM
	BM00101 AS BMH
INNER JOIN
	BM00111 AS BMC
		ON BMC.ITEMNMBR = BMH.ITEMNMBR
WHERE
	BMH.Bill_Status = 2

I needed to make sure that SSMS was configured to return the data into text and that the data returned was more than the default 256 characters.

Once you have the returned macros, save the file, open Bill of Materials Maintenance and then run the macro.

MDGP 2016 R1 Feature of the Day: Inventory All-In-One Document View

Microsoft Dynamics GP 2015 R2 saw the introduction of the Purchasing All-In-One Document View, and Dynamics GP 2016 R1 sees the introduction of an Inventory equivalent:

Inventory All-In-One Document View

The main features of Inventory All-In-One Document View are:

  • View quantity in and quantity out transactions for one item in one window
  • Accessible from Item Maintenance, many item inquiry windows, navigation lists and the home page

As with the Sales All-In-One Document View I am prepared to be underwhelmed by this feature of the day based on the Purchasing one introduced in Dynamics GP 2015 R2.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

View To Return Sales Orders (Work Status) Requiring An Assembly

Microsoft Dynamics GPI have some clients who use the Bill of Materials and assemblies within the Inventory series rather than the Manufacturing series and full MRP; their processes are not so complex that they need this level of MRP functionality. To make it easy to link an assembly to a sales order, the assemblies are created with the same ID as the order (one of the clients has a high level of automation added via customisations to automatically create the assembly from the order).

I’ve created a script to return this information on more than one occasion, so finally decided to post it here so I can easily find it.


CREATE VIEW uv_AZRCRV_SalesOrdersToBeAssembled 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
	CASE WHEN LEFT(RTRIM(SOP102.SOPNUMBE),3) = 'ORD' THEN
		SUBSTRING(RTRIM(SOP102.SOPNUMBE),4,LEN(RTRIM(SOP102.SOPNUMBE))-3)
	ELSE
		RTRIM(SOP102.SOPNUMBE)
	END +
	CASE WHEN (SELECT COUNT(SOPNUMBE) FROM SOP10200 SOP102I WHERE SOP102I.SOPNUMBE = SOP102.SOPNUMBE) > 1 THEN
		'_' + CAST(SOP102.LNITMSEQ/13684 AS VARCHAR(2))
	ELSE
		''
	END AS 'Assembly'
	,FORMAT(GETDATE(), 'yyyyMMdd') AS 'Batch Number'
	,RTRIM(SOP102.ITEMNMBR) AS 'Item Number'
	,CASE WHEN SOP102.UOFM = 'EACH' THEN
		CAST(CAST(SOP102.QUANTITY AS DECIMAL(10,0)) AS VARCHAR(10))
	ELSE
		CAST(CAST(SOP102.QUANTITY*10000 AS DECIMAL(10,0)) AS VARCHAR(10))
	END AS 'Quantity'
	,SOP102.UOFM AS 'UofM'
	,SOP101.BACHNUMB AS 'Sales Batch'
FROM
	SOP10200 SOP102 WITH (NOLOCK)
INNER JOIN
	SOP10100 SOP101 WITH (NOLOCK)
		ON
			SOP101.SOPNUMBE = SOP102.SOPNUMBE
		AND
			SOP101.SOPTYPE = SOP102.SOPTYPE
INNER JOIN
	BM00101 BM101 WITH (NOLOCK)
		ON
			BM101.ITEMNMBR = SOP102.ITEMNMBR
		AND
			BM101.Bill_Status = 1
LEFT JOIN
	BM10200 BM102 WITH (NOLOCK)
		ON BM102.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3))
LEFT JOIN
	BM30200 BM302 WITH (NOLOCK)
		ON BM302.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3))
WHERE
	SOP102.SOPTYPE = 2
AND
	BM102.TRX_ID IS NULL
AND
	BM302.TRX_ID IS NULL
GO

GRANT SELECT ON uv_AZRCRV_SalesOrdersToBeAssembled TO DYNGRP

MS Connect Suggestion: Add Bar Code Field to Item Maintenance

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.