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

● Ian Grieve ●  ● 2 Comments   ● 

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

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

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

● Ian Grieve ●  ● 1 Comment   ● 

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 a Sales equivalent:

Sales All-In-One Document View

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

  • View all related sales documents for a single transaction in one window
  • Accessible from Customer Maintenance, many sales inquiry windows, navigation lists and the home page

When I heard of the Purchasing version of this in 2015 R2 I was looking forward to it, but found the reality to be a little underwhelming as the window isn’ the friendliest around and is a little clunky. The screenshot above shows that the Sales All-In-One Document View is very similar to the purchasing one so fear I will find this equally clunky.

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

● 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 ●

MDGP 2015 R2 Feature of the Day: Customer Credit Limit Visual

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 has been released 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 twenty fourth Feature of the Day is Customer Credit Limit Visual. In Microsoft Dynamics GP 2015 R2, you have additional options that determine how and when you are notified of a customer who is over the credit limit you set for them.

In Receivables Setup, you can select to display an icon or give a warning message for a customer who is over their limit.

The existing process for an existing credit limit password will still be asked when entering a line item or saving a transaction.

The warning message:

Continue reading → MDGP 2015 R2 Feature of the Day: Customer Credit Limit Visual

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

MDGP 2015 R2 Feature of the Day: Enable Email on All Sales Order Document Formats

● Ian Grieve ●  ● 1 Comment   ● 

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 twenty first Feature of the Day is Enable Email on All Sales Order Document Formats. The Print Options for Sales Orders currently only support email for ‘Blank’ document formats. This feature will support email for any document format for all Sales Order documents.

This feature is supported when printing from the Transaction Entry Window, Navigation Lists, and Print Sales Documents window.

Print Sales Documents

While being able to all PO documents is a welcome feature, it is even more welcome on the Sales Order Processing side of things as I have a number of clients who need to produce a proforma invoice when the order is raised and this is typically done using one of the other document formats such as the Despatch Note Blank Form which can be emailed. It is going to be good to be able to configure the SOP Short Order Form as the proforma invoice and be able to use the despatch note for its intended purpose.

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 ●

MS Connect Suggestion Sunday: Navigation List Doesn’t Allow Sales Documents Printing In Currency

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPThis recently came up at a newly implemented client who makes extensive use of the Sales Order Processing module and sells across the world. Some of the sales orders and invoices printed off were coming off in the originating company and others in the functional.

We discussed with the users how the documents were being printed and established that some users were printing from the Sales Transaction Entry window, others from Print Sales Documents and others were using the Sales Order Transactions navigation list.

The documents printed through the navigation list were the ones which were always in the functional currency. The reason is that Sales Transaction Entry and Print Sales Documents have an option to select the Currency to Print whereas the navigation list print button doesn’t.

To remedy this, I have created an MS Connect suggestion to have the Currency to Print added to the print dialog on the navigation list which I would appreciate people voting for:

Navigation List Print Sales Document

If you have an MS Connect suggestions you want publicised, contact me with details.

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

MDGP 2013 R2 Feature of the Day: Print Remaining Documents

● 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 thirty-fifth Feature of the Day covered is Print Remaining Documents. Users will now have the ability to email and print batches or statements in one step rather than having to handle those that will be emailed or printed separately.

In the PM, RM, SOP and POP Batch windows the UI will not change; the user will simply mark both options to Print and Send Documents in E-mail.

The Print Receivables Statements form will see the introduction of a new option for use hen emailing statements:

GP windows

Click to show/hide the Microsoft Dynamics GP 2013 R2 Feature of the Day Series Index

Click to show/hide the Microsoft Dynamics GP 2013 R2 Feature of the Day Series Index

Microsoft Dynamics GP 2013 R2 Feature of the Day
MDGP 2013 R2 Feature of the Day: SmartList Designer Go To's
MDGP 2013 R2 Feature of the Day: Identity Management - Login
MDGP 2013 R2 Feature of the Day: Purchase Requisitions
MDGP 2013 R2 Feature of the Day: Copy And Paste To General Ledger Transaction Entry
MDGP 2013 R2 Feature of the Day: Workflow
MDGP 2013 R2 Feature of the Day: Workflow Types
MDGP 2013 R2 Feature of the Day: Workflow User Delegation
MDGP 2013 R2 Feature of the Day: Default Fixed Asset ID From The Asset Class
MDGP 2013 R2 Feature of the Day: Identity Management - Scalability Groups
MDGP 2013 R2 Feature of the Day: Workflow Calendar
MDGP 2013 R2 Feature of the Day: Reprint Outstanding Transaction in Bank Reconciliation
MDGP 2013 R2 Feature of the Day: Requisition Management Integration Points
MDGP 2013 R2 Feature of the Day: Default Sort Order for Checks
MDGP 2013 R2 Feature of the Day: Document Attach - Scan
MDGP 2013 R2 Feature of the Day: Workflow History
MDGP 2013 R2 Feature of the Day: Workflow Email Notifications
MDGP 2013 R2 Feature of the Day: Azure Backups
MDGP 2013 R2 Feature of the Day: Suggested Item Enhancements - Analysis and Assignment
MDGP 2013 R2 Feature of the Day: Suggested Item Enhancements - Sales Script and Additional Information
MDGP 2013 R2 Feature of the Day: Dashboards
MDGP 2013 R2 Feature of the Day: Management Reporter Web Viewer
MDGP 2013 R2 Feature of the Day: Take Company Offline
MDGP 2013 R2 Feature of the Day: Encumbrance SQL Reporting Services Reports
MDGP 2013 R2 Feature of the Day: Email Or Print Any Report In Word Format
MDGP 2013 R2 Feature of the Day: Reverse Year End Close
MDGP 2013 R2 Feature of the Day: Document Attachment On Payables Transactions
MDGP 2013 R2 Feature of the Day: Roll Down Segment Changes
MDGP 2013 R2 Feature of the Day: Assign An Item To Multiple Sites
MDGP 2013 R2 Feature of the Day: Replace OLE Note With Document Attachment
MDGP 2013 R2 Feature of the Day: OLE Notes Migration Utility
MDGP 2013 R2 Feature of the Day: Purchase Order Prepayment Additions
MDGP 2013 R2 Feature of the Day: Print Remaining Documents
MDGP 2013 R2 Feature of the Day: Email By Document Type
● 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 ●