Engineering Change Request View SQL Script

Since Dynamics GP Manufacturing originally began its life as a third party product, some of the core features supporting Manufacturing modules never made it into the product. For instance, Quality Assurance, Engineering Change Management, Routings, Forecasting and Job Costing do not have any out-of-the-box SmartLists.

I typically wind up using SQL Views, SmartList Builder and now SmartList Designer to create custom built SmartLists for these modules. In this post, I have included a SQL Script, which can be used to create a SQL View as the foundation for SmartLists or SQL Server Reporting Services (SSRS) Reports for Engineering Change Requests. Here it is:

Create VIEW Engineering_Change_Request

AS

select

DATEENTERED_I Date_of_Request,

CASE WHEN CUST.CUSTNMBR is not null then CUST.CUSTNAME

       Else  ''

End Customer_Name,

CASE WHEN CUST.CUSTNMBR is not null then CUST.CUSTNMBR

       ELSE ''

End Customer_ID,

ECMH.ENDDATE ECR_Complete_by_Date,

ECMH.ITEMNMBR Part_Number,

ECMH.ITEMDESC Part_Description,

'' Old_Rev,

ECMH.REVISIONLEVEL_I New_Rev,

ECMH.ECM_Short_Description ECR_Description,

DoEC.text1 BOM_Changes,

RFC.text2 Reason_for_Change,

NCO.text3 Notify_Customer,

EI.text4 Expected_Impact,

ECML.DISPOSITIONNOTES_I WIP_Instructions

from EC010031 ECMH

inner join EC050031 ECML on ECMH.ECNumber = ECML.ECNumber

left join RM00101 CUST on ECMH.CUSTNMBR = CUST.CUSTNMBR

left join EC010100 DoEC on ECMH.ECNumber = DoEC.ECNumber

left join EC010200 RFC on ECMH.ECNumber = RFC.ECNumber

left join EC010300 NCO on ECMH.ECNumber = NCO.ECNumber

left join EC010400 EI on ECMH.ECNumber = EI.ECNumber

GO

Grant Select on Engineering_Change_Request to DYNGRP  

Solver and eOne Team-Up! Try to Take Over the World...

In case you missed it, Solver and eOne recently announced a partnership to provide Business Intelligence solutions to the Dynamics Community.  Solver and eOne have been major players separately in the Dynamics Marketplace, this combination has tons of potential.

Solver brings Business Intelligence acumen, which extends across multiple product lines.  We've deployed BI360 on both Dynamics GP and Dynamics SL.  Imagine what eOne might do with the collaboration of a partner who has a deep understanding of multiple Dynamics products.  I can't wait to see how this pans out.

Here is a link to the announcement:

http://www.sys-con.com/node/3149608

eOne Announces new features for SmartList Builder

eOne, the original makers of SmartLists and SmartList Builders have recently announced the release of new features in SmartList Builder.

I am super excited about Two of these features!

1. Easy modification of EXISTING SmartLists... Quite possibly the most requested feature in SmartList history.  The ability to use existing SmartLists as a foundation for modification is revolutionary.

2. SQL data pulls on the fly.  If you've ever used SQL to create a view and then deployed a new SmartList using the view, you'll understand why I am so excited about the addition of this feature.

Check out this blog post from eOne to get description of all the new features of SmarList Builder:

http://blog.eonesolutions.com/2013/12/look-whats-coming-to-smartlist-builder.html