MDGP 2018 R2 Feature of the Day: Mass Update Master Records from Navigation List

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 twenty-second Feature of the Day is mass update of master records from navigation lists.

This feature today is the ability to mass update a group of master records from the Navigation Lists; you can now inactivate and reactive multiple records at the same time for the following master record types:

  • Accounts
  • Checkbooks
  • Customers
  • Vendors
  • Items
  • Employees

In addition, the vendor navigation list allows you to mass update the temporary status.

A visual indicator quickly lets you know the status of these records:

Vendors navigation list

Since navigation lists were introduced in Dynamics GP 10, they have grown increasingly common in usage, in particular new clients; there are some navigation lists which rarely see use, but ones for master records and transactions seem to be used a lot. There have been a few enhancements to ists over time, and I welcome even more being introduced, so it is good to see this feature.

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

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.

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