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.

A Diary of reIMAGINE 2016: Part 15 – Conference Day 2/Custom Workflow Creation

reIMAGINE2016The final session of the day I attended, was on Custom Workflow Creation. I was interested in this session, as we need to create some new Workflow Types. One which is needed, is a Workflow Type of EFT Bank.

The Vendor Change Workflow Approval is good, but does not do anything when the EFT Bank Details are changed and this is a major requirement for many of our clients in the UK.

The session showed how the new Workflow Type is created, tables updated, buttons added to windows and I took the opportunity to discuss wider requirements such as the need to update code elsewhere to respect the new Workflow Types created.

The changes for new Workflow 2.0 Types must be done in Dexterity.

There is a lot of scope for creating new Workflow Types in addition to EFT Bank Details such as SOP Transactions, Bill of Materials, and there my mind goes blank. Been in this airport too long.

The session on custom workflow creation was one of my favourites due to my interest in Workflow 2.0 (did I mention I am doing a second edition of my Workflow 2.0 book?).

Click to show/hide the A Diary of reIMAGINE 2016 Series Index

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

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.

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

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.

SQL View To Allow Customer/Item Link In SmartList Builder

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