azurecurve

Merged Systems Duplicating Note Indexes

Thursday 27th Nov 2014 Ian Grieve3 minute read1 Comment

We have been doing some work for a client who is merging two Dynamics GP implementations into one and during testing found that new orders being entered in the newly merged system were showing notes for previous orders.

The reason is that the next number to be used is an entry on the SY01500 (Company Master) table. When the systems were merged, it was done by creating a new company in Dynamics GP, restoring the source systems database over the top and then configuring the setup stored in the System database, such as the Company Setup (Administration area page » Setup » Company » Company).

So we had a database which had notes attached, but a brand new entry in the Company Master (SY01500) table. As we still had the DYNAMICS for the source company database it is a fairly easy job to identify the correct value and update into Company Master.

However, should you want to automate this process, there is a KB article from Microsoft available: Cross-linked or incorrect notes indexes in Microsoft Dynamics GP. The KB article contains a SQL script which is run against the company database and identifies the highest used Notes Index and then resets the Company Master value.

azurecurve Links Updated: The Dex God Has A New Blog

Wednesday 26th Nov 2014Saturday 19th Jan 2019 Ian Grieve2 minute read0 comment

There has been some movement on Dynamics GP related blogs since I last updated my links page.

Steve Endow was awarded MVP so I have moved him to the MVP section from the Consultants one.

And, obviously, the biggest news is that David Musgrave left Microsoft and founded Winthrop Development Consultants and is now blogging at the Winthrop DC blog.

David’s contributions to the Dynamics GP community are one of the main reasons why I started blogging (his posts were so damn useful I decided to offer back whatever I could), so I am delighted to see that he will be continuing to contribute to the community following his departure from Microsoft.

If there are other blogs out there I should have listed, please let me know.

SQL View To Select User, Company and Security Role Assignment

Wednesday 26th Nov 2014Tuesday 3rd Mar 2015 Ian Grieve4 minute read4 Comments

We had a user recently who wanted to quickly see which users had access to which companies and the Security Roles which had been granted. While Dynamics GP does have a number of security reports, they are the standard ones which, while readable in the usual screen or printer output, cannot easily be exported to Excel. However, the client is on Microsoft Dynamics GP 2013 R2 and therefore has access to SmartList Designer.

While I could have done this entirely as a SmartList Designer report, I already had the majority of the SQL needed to generate this as a SQL View which SmartList Designer can access:

CREATE VIEW uv_PI_UserAccessAndGrantedSecurityRoles AS
SELECT
	['User Master'].USERID AS 'User ID'
	,['User Master'].USERNAME AS 'Username'
	,['User Master'].USRCLASS AS 'User Class'
	,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description'
	,ISNULL(['Company Master'].INTERID, '') AS 'Intercompany ID'
	,ISNULL(['Company Master'].CMPNYNAM, '') AS 'Company Name'
	,ISNULL(['Security Assignment User Role'].SECURITYROLEID, '') AS 'Security Role ID'
	,ISNULL(['Security Roles Master'].SECURITYROLENAME, '') AS 'Security Role Name'
FROM
	SY01400 AS ['User Master']
LEFT JOIN
	SY40400 AS ['Class Master']
		ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
LEFT JOIN
	SY60100 AS ['User-Company Access']
		ON ['User-Company Access'].USERID = ['User Master'].USERID
LEFT JOIN
	SY10500 AS ['Security Assignment User Role']
		ON ['Security Assignment User Role'].CMPANYID = ['User-Company Access'].CMPANYID
			AND ['Security Assignment User Role'].USERID = ['User-Company Access'].USERID
LEFT JOIN
	SY09100 AS ['Security Roles Master']
		ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID
LEFT JOIN
	SY01500 AS ['Company Master']
		ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
GO

Of course, the other reason I used the SQL rather than recreating entirely in SmartList Designer is that I can use this SQL in future, but a SmartList Designer is only usable on the system on which it is created as there is no import/export functionality.

Old Style Emailing Of Dynamics GP RM Statements Error: “Path for the E-mail Status Report is Not Setup”

Monday 24th Nov 2014 Ian Grieve1 minute read0 comment

A client on Dynamics GP 2010 R2 has been configuring email for the RM statements recently, but after configuring the system were still receiving an error message:

Path for the E-mail Status Report is Not Setup

When we were assisting to diagnose the problem, we found that while they did have the EmailStmtStatusPath line in their Dex.ini file (as detailed in this KB article), the specified path did not have a trailing slash; as soon as this was done the report started working without issue.

MDGP 2015 Feature of the Day: Existing Purchase Order Warning

Sunday 23rd Nov 2014 Ian Grieve3 minute read0 comment

The Inside Microsoft Dynamics GP blog has started a series of posts on the new features of, the soon to be released, Microsoft Dynamics GP 2015. As I have done for the last two releases, I intend to shadow these posts and add my own opinions on these new feature; the series index is here.

The thirty fifth Feature of the Day is Existing Purchase Order Warning. A new warning message has been added to the Payables Transaction Entry form to prompt the end-user if they attempt to post or save an invoice for a vendor that already has an available Purchase Order in the application:

This new functionality will be disabled by default on upgrades but can be enabled by selecting the Warn if Vendor has Existing Purchase Order checkbox on the Payables Management setup window. For new installs, the functionality will be enabled by default.

It has always been a bit of a debate as to how non-PO invoices should be entered into Dynamics GP; do you use Payables Transaction Entry (Purchasing area page » Transactions » Transaction Entry) or enter a Shipment/Invoice in Receivings Transaction Entry (Purchasing area page » Transactions » Receivings Transaction Entry). Most clients have opted to use Payables Transaction Entry which means this new warning message could be very useful for preventing the entry of payabes invoices when there is an outstanding PO.

The roles targeted by this feature are:

Payables Clerk, Accounting Manager
Purchasing Agent

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

MDGP 2015 Feature of the Day
General Ledger Batch Approval
Payables Management Batch Approval
Receivables Management Batch Approval
Vendor Approval
Project Employee Expense Report
Organizational Accounts User Authentication
Invoicing in the Web Client
Service Based Architecture
Service Based Architecture Deployment
Service Based Architecture Deployment
.NET Interop
Employee Benefits
Employee Self Service Functionality
Employee Profile, Skills & Training
HRP Workflow Pending Approval Navigation List
Employee W4 and Workflow
Direct Deposit and Workflow
Employee Paystubs
Fixed Assets Year-End Closing Report
Canadian Payroll Web Client
Create Refreshable Excel Reports In SmartList Designer
Payment Terms – Transaction Calculation
Payment Terms – Discount Date Calculation
Copy Home Page and Area Page
Management Reporter Integration
Management Reporter Integration Setup
Management Reporter in Business Analyzer
Business Analyzer Cross Platform
Void Intercompany Journal Entries
Edit Email on Historical Transactions
Intercompany Journal Entry Inquiry
Project Employee Expense Report Entry
Management Reporter Data Integration
Employee Self Service Purchase Requisitions
Existing Purchase Order Warning

MDGP 2015 Feature of the Day: Employee Self Service Purchase Requisitions

Friday 21st Nov 2014 Ian Grieve2 minute read0 comment

The thirty fourth Feature of the Day is Employee Self Service Purchase Requisitions. Dynamics GP 2015 Purchase Requisitions lets a user enter a requisition which ties in with Purchase Order Processing functionality.
Â
As part of the Employee Self Service functionality, navigation is streamlined for the requester to see a requisition status or enter a requisition.
Â
Users are able to quickly see important information right from their Home Page using the Saved, Ready for Purchase, Pending User Action, Rejected Workflow, and Approval tiles:

Continue reading “MDGP 2015 Feature of the Day: Employee Self Service Purchase Requisitions”

MDGP 2015 Feature of the Day: Management Reporter Data Integration

Friday 21st Nov 2014 Ian Grieve3 minute read0 comment

The thirty third Feature of the Day is Management Reporter Data Integration. In Microsoft Dynamics GP 2015, the user can determine the data that is available to the Microsoft Dynamics Management Reporter DataMart, improving the performance and giving the user control over the data selected.
Â
The user can choose per company (including sample company) if they want the General Ledger data available for reporting.
Â
The user also has the ability to include or exclude Analytical Accounting data for each company:

I am slightly puzzled by this feature of the day as you can simply not import a company into Management Reporter if you don’t want to report on it. While I steer clients away from AA where possible, I am not entirely opposed to it, but I do wonder who would want to report on AA without also wanting to be able to report on the GL.

The roles targeted by this feature are:

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

MDGP 2015 Feature of the Day
General Ledger Batch Approval
Payables Management Batch Approval
Receivables Management Batch Approval
Vendor Approval
Project Employee Expense Report
Organizational Accounts User Authentication
Invoicing in the Web Client
Service Based Architecture
Service Based Architecture Deployment
Service Based Architecture Deployment
.NET Interop
Employee Benefits
Employee Self Service Functionality
Employee Profile, Skills & Training
HRP Workflow Pending Approval Navigation List
Employee W4 and Workflow
Direct Deposit and Workflow
Employee Paystubs
Fixed Assets Year-End Closing Report
Canadian Payroll Web Client
Create Refreshable Excel Reports In SmartList Designer
Payment Terms – Transaction Calculation
Payment Terms – Discount Date Calculation
Copy Home Page and Area Page
Management Reporter Integration
Management Reporter Integration Setup
Management Reporter in Business Analyzer
Business Analyzer Cross Platform
Void Intercompany Journal Entries
Edit Email on Historical Transactions
Intercompany Journal Entry Inquiry
Project Employee Expense Report Entry
Management Reporter Data Integration
Employee Self Service Purchase Requisitions
Existing Purchase Order Warning

MDGP 2015 Feature of the Day: Project Employee Expense Report Entry

Friday 21st Nov 2014 Ian Grieve2 minute read0 comment

The thirty second Feature of the Day is Project Employee Expense Entry. Project Accounting employees can create expense reports for any projects to which they are assigned. Employees can also be set up to allow delegates to enter expense reports on their behalf.

Workflow approvals and task assignments are defined for expense reports; workflow actions are executed on the documents from the Expense Entry window or from the Navigation lists.

Continue reading “MDGP 2015 Feature of the Day: Project Employee Expense Report Entry”

MDGP 2015 Feature of the Day: Intercompany Journal Entry Inquiry

Friday 21st Nov 2014 Ian Grieve2 minute read0 comment

The thirty first Feature of the Day is Void Intercompany Journal Entries. In Microsoft Dynamics GP 2015, one of the improvements to the intercompany functionality is the ability to view the intercompany distributions of a journal entry from another company.

Having the Intercompany Journal Entry Inquiry window eliminates the need for users to switch between companies to view all originating- and destination-company distributions of an intercompany journal entry. All distributions can be viewed from one company.

The Intercompany Journal Entry Inquiry window is launched from the Journal Entry Inquiry (Financial area page » Inquiry » Journal Entry) using the Intercompany button at the bottom of the window:

Continue reading “MDGP 2015 Feature of the Day: Intercompany Journal Entry Inquiry”

Professional Services Tools Library – DBMS Error 4920

Tuesday 11th Nov 2014Sunday 26th Oct 2014 Ian Grieve1 minute read0 comment

I ws recently doing some work for a client, one part of which was to configure Microsoft Dynamics GP to duplicate creditors created in one company into another using the PM Master Trigger functionality in the Professional Services Tools Library (PSTL).

When I tried to enable the trigger I received the following error message: