Microsoft Dynamics GP 2013 "Feature of the Day" - Week 16 & 17 Digest

Since Week 16 brought little movement in the Features department - Pam was out of town and Jay, well I really don't know - I saw it fit to combine it with week 17 features into one Digest article for your reading pleasure. On we go with the Futures of the Day.


Feature of the Day: Payroll integration to Payables

Payroll Vendor Setup window

 Original article: Feature of the Day: Payroll integration to Payables
Link to presentation: GP2013 FOD PIP Voucher Integration FOD.ppsx

Feature of the Day: Inactivate Item/Site

Site Maintenance window

Item Quantities Maintenance window

Original article: Feature of the Day: Inactivate Item/Site
Link to presentation: GP2013 FOD Inactivate a Site and Item Site.ppsx

Feature of the Day: Create Equipment from Manufacturing

Service Setup window: these options will only display with Manufacturing module installed

Original article: Feature of the Day: Create Equipment from Manufacturing
Link to presentation: GP2013 FOD Create Equipment from Manufacturing.ppsx

Feature of the Day: Honor Discontinued Item Status in Field Service

Discounted Item Quantity Available window

Original article: Feature of the Day: Honor Discontinued Item Status in Field Service
Link to presentation: GP2013 FOD Honor Discontinued Items in Field Service.ppsx

Feature of the Day: Return Quantity Types

RMA Type Maintenance window

Original article: Feature of the Day: Return Quantity Types
Link to presentation: GP2013 FOD Return Quantities to Any Quantity Type.ppsx

Feature of the Day: Document Attach

Sales Transaction Entry window: document attach buttons

Document Attach Management window

Original article: Feature of the Day: Document Attach
Link to presentation: GP2013 FOD Document Attach.ppsx

Feature of the Day: Prevent Purchase Order Closing

Edit Purchase Order Status window

Original article: Feature of the Day: Prevent Purchase Order Closing
Link to presentation: GP2013 13928 Prevent PO Close FOD.ppsx

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Microsoft Dynamics GP 2013 "Feature of the Day" - Week 10 Digest

It's been 10 weeks so far of great features in Microsoft Dynamics GP 2013. The list seems to be endless, but at the same time super informative. This shows the amount of work that went into making this one of the biggest releases ever.

Enjoy this weeks features:

Feature of the Day: Journal Entry History Inquiry

Journal Entry Inquiry

Original article: Feature of the Day: Journal Entry History Inquiry
Link to presentation: GP2013 FOD Journal Entry History Inquiry.ppsx
Learning script: N/A


Feature of the Day: Edit Remit To Address

Edit Remit to Address

Original article: Feature of the Day: Edit Remit To Address
Link to presentation: GP2013 FOD Edit Remit To Address.ppsx
Learning script: N/A


Feature of the Day: Update 1099 Information

Update 1099 Information

Edit 1099 Transaction Information

Original article: Feature of the Day: Update 1099 Information
Link to presentation: GP2013 FOD Update 1099 Information.ppsx
Learning script: N/A


Feature of the Day: Update Item Allocation Inquiry in Field Service

Service Call Entry - Parts showing allocated item

Can now inquiry Field Service allocations from Item Allocation Inquiry window

Original article: Feature of the Day: Update Item Allocation Inquiry in Field Service
Link to presentation: GP2013 FOD Update Item Allocation Inquiry from Field Service.ppsx
Learning script: N/A


Feature of the Day: Multitenant SQLReports

Can now select deployment folder for reports of a specific company

Original article: Feature of the Day: Multitenant SQLReports
Link to presentation: GP2013 FOD Multi-tenant SQL Reports Server.ppsx
Learning script: GP2013 Multitenant SQL Reports Server Learning Script

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Microsoft Dynamics GP 2013 "Feature of the Day" - Week 9 Digest

Just back from the Microsoft Dynamics GP Technical Airlift 2012, I could probably write a full series with the remaining features left to be covered, but that honor goes to the Inside Microsoft Dynamics GP blog team, which was able to kick these out while the event was in full swing in Fargo, North Dakota.

Here are the highlights from last week:

Feature of the Day: Multi-Select Serial Numbers

Item Serial Number Entry

Original article: Feature of the Day: Multi-Select Serial Numbers
Link to presentation: GP2013 FOD Multi-Select Serial Numbers.ppsx
Learning script: N/A


Feature of the Day: Saved Batch Approval

Query shows Approval User ID (APRVLUSERID) and Approval Date (APPRVLDT) columns with info

Original article: Feature of the Day: Saved Batch Approval
Link to presentation: GP2013 FOD Saved Batch Approval.ppsx
Learning script: N/A


Feature of the Day: Unapply Credit Documents when Voiding a Check

We start out with a fully applied invoice for $1,070 by a payment and a credit memo

We now void the payment for $705.00

Now the invoice and the credit memo return to open after voiding the payment

Original article: Feature of the Day: Unapply Credit Documents when Voiding a Check
Link to presentation: GP2013 FOD_UnapplyCreditDocumentsWhenVoidingCheck.ppsx
Learning script: N/A


Feature of the Day:Update Back Order Item Qty in Field Service

Work Order Entry/Update generated back order quantity of 2 and updates overall item BO qty

Original article: Feature of the Day:Update Back Order Item Qty in Field Service
Link to presentation: GP2013 FOD-Update Back Ordered Item Qty from Field Service.ppsx
Learning script: N/A


Feature of the Day: Pay Code, Benefit & Deduction

Original article: Feature of the Day: Pay Code, Benefit & Deduction
Link to presentation: GP2013 FOD Pay Code, Benefit & Deduction Modifier.ppsx
Learning script: N/A

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Microsoft Dynamics GP 2013 "Feature of the Day" - Week 4 Digest

Week 4th of the Feature of the Day series went by with some really cool additions. The central theme: lots of visual cues for improved transaction readability and more reporting capabilities from within Microsoft Dynamics GP - yes, I can smell it too... Report Writer days, as the main user experience, are nearing the end.
Feature of the Day: Email Indicators

Email Indicator

Original article: Feature of the Day: Email Indicators
Link to presentation: GP2013 FOD Email Indicators.ppsx
Learning script: N/A


Feature of the Day: Reconcile IV to GL

Reconcile IV to GL spreadsheet

Original article: Feature of the Day: Reconcile IV to GL
Link to presentation: GP2013 FOD Reconcile IV to GL.ppsx
Learning script: GP2013 Reconcile IV to GL Script.docx


Feature of the Day: SRS Reports on Forms

SSRS Reports run from the GP forms directly

Original article: Feature of the Day: SRS Reports on Forms
Link to presentation: GP2013 FOD SSRS Reports on Forms.ppsx
Learning script: GP2013 Printing SSRS Reports From Forms Learning Script.docx


Feature of the Day: Item Standard Cost Adjustment

Change Item Standard Cost

Original article: Feature of the Day: Item Standard Cost Adjustment
Link to presentation: GP2013 FOD Item Standard Cost Adjustment.ppsx
Learning script: N/A


Feature of the Day: Reconciled Indicator

Reconciled Indicator

Original article: Feature of the Day: Reconciled Indicator
Link to presentation: GP2013 FOD_Reconciled_Indicators.ppsx
Learning script: N/A


Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Adding Customer Item User Defined fields to SOP Invoice

Just recently I ran across a request to add the Customer Item user defined fields to the Sales Blank Invoice Form report in Report Writer. As usual with these type of requests, chances are it's been done before and/or it's probably documented in some KB article.

As it turned out, KB article 918943 outlines the steps required to add the Customer Item Number to the Sales Blank Invoice Form report:

How to add the "Customer Item Number" field to a quote, order, or invoice form in Sales Order Processing in Microsoft Dynamics GP

The KB article however, makes use of the Report Writer function rw_SOP_GetCustomerItemNumber to get around the issue. There's also an additional Report Writer function, rw_SOP_GetCustomerItemDescription which allows you to retrieve the description for the customer item. Now, that's all cool, but what about the user-defined fields?

Customer Item Maintenance

The screenshot above highlights the fields in question. As it turned out, there are no Report Writer functions to retrieve those.

In looking at possible ways to add these fields you could try 3 different things:

1. Use Modifier with VBA to retrieve the user-defined fields. While this is a viable solution, the partner was adamant about not using VBA because of lack of supportability on the Microsoft Dynamics GP 2013 Web Client.

2. Table relationships in Report Writer. After looking at the table keys on SOP_LINE_WORK (SOP10200) and sopCustomerItemXref (SOP60300), I concluded that table relationships would not work either. The problem here is that the SOP_LINE_WORK table does not contain the Customer Number field, required to be able to establish a relationship to the sopCustomerItemXref. After all, if the field existed, there would be no need for the Report Writer functions mentioned above.

3. Support  Debugging Tool. The SDT has the ability to encapsulate Dexterity code with it's Runtime Execute feature. That code can be saved to a Script ID. Furthermore, the Support Debugging Tool leverages the 6 generic Report Writer functions available in the Microsoft Dynamics GP dictionary. These functions were created to allow third party developers a way around an Alternate Report, with the ability to retrieve data from their tables.

For more information on the generic Report Writer functions, take a look at KB article 888884:

Useful functions for developers to use instead of creating alternate reports in Microsoft Dynamics GP

Given all the options and drawbacks, it was decided that Support Debugging Tool would be able to accomplish the job. Since it's a Dex application, it certainly would move forward to Microsoft Dynamics GP 2013 and Web Client, and in addition, would be able to work around the table relationships issue.

---

One thing I like about the Support Debugging Tool's Runtime Execute is the ability to use the Helper functions. In particular, the helper function needed is one that allows us to leverage a generic Report Writer function, the rw_TableLineString, while line items are being printed on the report - when a line item is displayed, we must display the corresponding Customer Item user defined field.

Insert Helper Function window

Since there's a template for the above function, all we need to do is click on the Ok button. As if the Support Debugging Tool wasn't already helpful, it now autogenerates some of the code needed to work with the rw_TableLineString:

Runtime Execute window with auto-generated Dexterity code

All that's left now is to add a script ID and enter the bit of code needed to retrieve the user-defined fields:

Runtime Execute script

The full version of the script can be found below:
Script ID: CUSTITMUDF

local string MBS_TableLineString;
local string MBS_Number;
local integer MBS_Type;
local currency MBS_SequenceOne;
local currency MBS_SequenceTwo;
local integer MBS_Control;
local string MBS_String;

{Declarations}
local string ItemNumber;
local string CustomerNumber;

call with name "MBS_Param_Get" in dictionary 5261, "Number", MBS_Number;
call with name "MBS_Param_Get" in dictionary 5261, "Type", MBS_String;
MBS_Type = integer(value(MBS_String));
call with name "MBS_Param_Get" in dictionary 5261, "SequenceOne", MBS_String;
MBS_SequenceOne = currency(value(MBS_String));
call with name "MBS_Param_Get" in dictionary 5261, "SequenceTwo", MBS_String;
MBS_SequenceTwo = currency(value(MBS_String));
call with name "MBS_Param_Get" in dictionary 5261, "Control", MBS_String;
MBS_Control = integer(value(MBS_String));
MBS_TableLineString = "";

{ Add your code below here }

{ Retrieve the item number in question }
clear table SOP_LINE_WORK;
set 'SOP Number' of table SOP_LINE_WORK to MBS_Number;
set 'SOP Type' of table SOP_LINE_WORK to MBS_Type;
set 'Line Item Sequence' of table SOP_LINE_WORK to MBS_SequenceOne;
set 'Component Sequence' of table SOP_LINE_WORK to MBS_SequenceTwo;

get table SOP_LINE_WORK by number 1;
if err() = OKAY then
 set ItemNumber to 'Item Number' of table SOP_LINE_WORK;
end if;

{ Now, get the customer number }
clear table SOP_HDR_WORK;
set 'SOP Number' of table SOP_HDR_WORK to MBS_Number;
set 'SOP Type' of table SOP_HDR_WORK to MBS_Type;

get table SOP_HDR_WORK by number 1;
if err() = OKAY then
 set CustomerNumber to 'Customer Number' of table SOP_HDR_WORK;
end if;

{Since we have the customer number and item number, we should now be able to
 retrieve the customer item number
}

if not empty(ItemNumber) and not empty(CustomerNumber) then
 clear table sopCustomerItemXref;
 set 'Item Number' of table sopCustomerItemXref to ItemNumber;
 set 'Customer Number' of table sopCustomerItemXref to CustomerNumber;
 
 get table sopCustomerItemXref by number 1;
 if err() = OKAY then
  case MBS_Control 
   in [1] {UDF 1}
    MBS_TableLineString = 'User Defined 1' of table sopCustomerItemXref;
   in [2] {UDF 2}
    MBS_TableLineString = 'User Defined 2' of table sopCustomerItemXref;
   in [3] {UDF 3}
    MBS_TableLineString = 'User Defined 3' of table sopCustomerItemXref;
   in [4] {UDF 4}
    MBS_TableLineString = 'User Defined 4' of table sopCustomerItemXref;
   in [5] {UDF 5}
    MBS_TableLineString = 'User Defined 5' of table sopCustomerItemXref;
   in [6] {UDF Key 1}
    MBS_TableLineString = 'User Defined Key1' of table sopCustomerItemXref;
   in [7] {UDF Key 2}
    MBS_TableLineString = 'User Defined Key2' of table sopCustomerItemXref;
   in [8] {UDF Key 3}
    MBS_TableLineString = 'User Defined Key3' of table sopCustomerItemXref;
   in [9] {UDF Key 4}
    MBS_TableLineString = 'User Defined Key4' of table sopCustomerItemXref;
  end case;
 else
  MBS_TableLineString = "";
 end if;
end if;

{ Add your code above here }

call with name "MBS_Param_Set" in dictionary 5261, "TableLineString", MBS_TableLineString;

Cool balloons! One aspect of the programming was, how to leverage the same function (as opposed to creating multiple scripts that would perform the same operations) to pull in different UDFs? As it turns out, there's a parameter in the rw_TableLineString function, which allows you to specify a control value which determines the position of the piece of data to be returned. This control value is an integer type. Knowing this allowed me to send in a numeric value for the UDF I wanted to retrieve and define a case...end case statement evaluating the control parameter to return the proper UDF field.

---

Finally, the rest is just house keeping at the Report Writer level... First, we must create two currency calculated fields: one for the Line Item Sequence and another for the Component Sequence. As it turns out, our Report Writer custom function requires two currency parameters. Below is the rw_TableLineString function signature

rw_TableLineString()

in integer dict_id; {Dictionary ID}
in string script_id; {Script ID} in string sNumber; {control field 1}
in integer sType; {control field 2}
in currency cSequenceOne; {control field 3}
in currency cSequenceTwo; {control field 4} 
in integer iControl; {which piece of data to return}

Our calculated fields are shown below:

(C) Component Sequence calculated field

(C) Line Item Sequence calculated field

Finally, we need a calculated field to invoke our Report Writer function, which in turn will call our script ID created in Support Debugging Tool. In this specific case, we are going to retrieve the Customer Item user-defined field 1.

CustomerItemUDF1 Calculated Field

FUNCTION_SCRIPT( rw_TableLineString 5261 "CUSTITMUDF" SOP_LINE_WORK.SOP Number SOP_LINE_WORK.SOP Type (C) Line Sequence Number (C) Component Sequence 1)

We can now move our CustomerItemUDF1 calculated field onto the report layout:

After granting security to the modified report, we can print an invoice to verify the report works.

As you can see, once more we have leveraged the power of the Support Debugging Tool to deliver what would have otherwise seemed like a difficult or unattainable customization to a report. I hope this is yet another incentive to install the tools and begin taking advantage of its features.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL - Follow up!

Ok, of course my dear friend Corey in Louisville, Ohio had to challenge my post from yesterday and ask a question that is, frankly, a logical and natural progression of things - see Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL.

In yesterday's post I showed a query that could create a year-to-year Inventory Margin report using T-SQL's PIVOT operator. The query, while very useful, hardcodes the years you want to display in the pivoted columns list. An excerpt of the PIVOT operator and the pivoted columns list as follows:

.
.
.
PIVOT (
 SUM(MARGIN)
 FOR TRXYEAR IN ([2014], [2015], [2016], [2017])
 ) AS pvt

So naturally, Corey's question was, "What if I want to add the pivoted columns list dynamically, instead of hardcoding them?". Since all transactions have a date, it would make sense to add the list of years based on the document dates. The good news is this is possible. For this we would need to create a dynamic pivoted list using, well, dynamic SQL, as follows:

DECLARE @listCol NVARCHAR(MAX), @sqlstmt NVARCHAR(MAX);

SELECT  @listCol = STUFF(
 ( SELECT DISTINCT '],[' + CONVERT(NVARCHAR(5), YEAR(DOCDATE))
   FROM SOP30200
   ORDER BY '],[' + CONVERT(NVARCHAR(5), YEAR(DOCDATE))
   FOR XML PATH('')
 ), 
 1, 
 2, 
 '') + ']'

SET @sqlstmt = 
N';WITH CTE AS (
 SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
 FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE) 
 WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0) 
 GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC
)
SELECT ITEMNMBR AS [Item Number], ITEMDESC AS [Item Description],' + @listCol + N'
FROM (
 SELECT TRXYEAR, ITEMNMBR, ITEMDESC, MARGIN 
 FROM CTE
) p 
PIVOT (
 SUM(MARGIN)
 FOR TRXYEAR IN (' + @listCol + N')
 ) AS pvt
ORDER BY ITEMNMBR;';

EXEC sp_executesql @sqlstmt;

Couple observations...

We use the FOR XML clause with the PATH mode to build a list of elements and attributes based on the distinct year values stored in the document date (DOCDATE) column in our SOP30200 (SOP History) table, which we store in the @listcol variable length Unicode character data type (NVARCHAR).

Our CTE is now embedded in an NVARCHAR variable, which we call @sqlstmt, with just the right breaks to concatenate our pivoted column list variable, @listcol as part of the overall SQL statement character string that will be executed.

We finally use the sp_executesql system stored procedure to run our dynamic SQL query and produce the results below:

Item Number Item Description 2014 2015 2016 2017 2018
100XLG                          Green Phone                                                                                           NULL NULL 307.05000 40.05000 NULL
3-B3813A                        Keyboard                                                                                              NULL NULL NULL 40.00000 NULL
3-C2924A                        SCSI Cable, 2.5m. 68-pin HI-Density                                                                   NULL NULL NULL 148.50000 NULL
3-C2924A                        T0101 - SCSI Cable, 2.5m. 68-pin HI-Density                                                           NULL NULL NULL NULL 84.38000
3-C2924A                        T0102 - SCSI Cable, 2.5m. 68-pin HI-Density                                                           NULL NULL NULL NULL 67.50000

Finally, you will notice NULL values for some of the years where there was no sales activity for an item. You can take care of these and how they display, directly on your report or Excel spreadsheet.

Not bad at all!

For a primer on the Do's and Don'ts of dynamic SQL, I invite you to read SQL Server MVP, Earland Sommarskog article, The Curse and Blessings of Dynamic SQL.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL

As of late I have been camping out at the SQL Developer Center's  Transact-SQL Forum and I have to say, I have learned a great deal from my fellow SQL Server MVPs. Very alike the Microsoft Dynamics GP MVPs, these folks are willing to resolve pretty much any T-SQL request that comes across the forum.

So long you follow some simple posting guides, like including your table definitions, providing some sample data and detailing your expected results, there's nothing these folks won't do for you.

I decided to put some of what I have learned to the test by creating a T-SQL query that would produce a Year-to-year Inventory Margin Report (in currency) by using the T-SQL PIVOT operator. My fellow MVP Mark Polino is very well versed with Microsoft Excel and PowerPivot and I thought this would be a way to demonstrate that you can still use T-SQL to resolve some very complex issues which otherwise would require the use of front-end tool or some back-end cubes.

The following query can be run against the Fabrikam test company and adjusted to meet your specific needs:

;WITH CTE AS (
 SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
 FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE) 
 WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0) 
 GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC
)
SELECT ITEMNMBR AS [Item Number], ITEMDESC AS [Item Description], 
 COALESCE([2014], 0) AS Y2014, 
 COALESCE([2015], 0) AS Y2015,
 COALESCE([2016], 0) AS Y2016, 
 COALESCE([2017], 0) AS Y2017 
FROM (
 SELECT TRXYEAR, ITEMNMBR, ITEMDESC, MARGIN 
 FROM CTE
) p 
PIVOT (
 SUM(MARGIN)
 FOR TRXYEAR IN ([2014], [2015], [2016], [2017])
 ) AS pvt
ORDER BY ITEMNMBR;

The script uses a query encapsulated in a Common Table Expression (CTE) to produce a temporary result for the sales data by item, grouped over each year. However, this result would produce rows of records for each year, as shown below:

SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
 FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE) 
 WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0) 
 GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC

/* Results */

TRXYEAR ITEMNMBR ITEMDESC XTNDPRCE EXTDCOST MARGIN
2014 ACCS-CRD-12WH                   Phone Cord - 12' White                                                                                39.80000 13.16000 26.64000
2014 ACCS-RST-DXBK                   Shoulder Rest-Deluxe Black                                                                            29.85000 13.65000 16.20000
2014 ACCS-RST-DXWH                   Shoulder Rest - Deluxe White                                                                          39.80000 18.20000 21.60000
2014 ANSW-ATT-1000                   Attractive Answering System 1000                                                                      119.95000 59.29000 60.66000
2014 ANSW-PAN-1450                   Panache KX-T1450 answer                                                                               219.90000 100.50000 119.40000
2014 FAXX-CAN-9800                   Cantata FaxPhone 9800                                                                                 23999.50000 11970.00000 12029.50000
2014 FAXX-HLP-5433                   Hewlett Packard FAX-310                                                                               854.50000 395.10000 459.40000
2014 FAXX-SLK-0172                   Sleek UX-172 fax                                                                                      2699.90000 1349.00000 1350.90000
2014 HDWR-PNL-0001                   Control Panel                                                                                         609.95000 303.85000 306.10000
2014 HDWR-PRO-4862                   Processor 486/25MHz                                                                                   5999.95000 2998.15000 3001.80000
2014 PHON-ATT-53BL                   Cordless-Attractive 5352-Blue                                                                         1139.70000 561.30000 578.40000
2014 PHON-ATT-53WH                   Cordless-Attractive 5352-White                                                                        189.95000 92.59000 97.36000
2014 PHON-BUS-1250                   Handset,multi-line                                                                                    359.95000 165.85000 194.10000
2014 PHON-PAN-2315                   Panache KX-T231 wall                                                                                  119.90000 59.50000 60.40000
2014 WIRE-SCD-0001                   Single conductor wire                                                                                 8.75000 4.00000 4.75000
2016 100XLG                          Green Phone                                                                                           4136.55000 3829.50000 307.05000

But in order to produce the results we want, having a true year by year comparison, it is necessary to pivot the results of the above query. Here's where the PIVOT operator comes into play, as shown in the initial query. This is the output produced:

Item Number       Item Description      Y2014  Y2015  Y2016  Y2017
100XLG            Green Phone                                            0.00000 0.00000  307.05000 40.05000
3-B3813A          Keyboard                                               0.00000 0.00000  0.00000  40.00000
3-C2924A          SCSI Cable, 2.5m. 68-pin HI-Density                    0.00000 0.00000  0.00000  148.50000
3-C2924A          T0101 - SCSI Cable, 2.5m. 68-pin HI-Density            0.00000 0.00000  0.00000  0.00000
3-C2924A          T0102 - SCSI Cable, 2.5m. 68-pin HI-Density            0.00000 0.00000  0.00000  0.00000
3-D2657A          T0101 - DB 15 Male Adapter                             0.00000 0.00000  0.00000  0.00000
3-D2657A          T0102 - DB 15 Male Adapter                             0.00000 0.00000  0.00000  0.00000
3-D2659A          T0101 - DB 25 Female Adapter                           0.00000 0.00000  0.00000  0.00000
3-D2659A          T0102 - DB 25 Female Adapter                           0.00000 0.00000  0.00000  0.00000
3-E4471A          HP Extractor Fan, 200-240V                             0.00000 0.00000  0.00000  134.00000
3-E4592A          SurgeArrest Plus                                       0.00000 0.00000  0.00000  180.00000
3-E4592A          T0101 - SurgeArrest Plus                               0.00000 0.00000  0.00000  0.00000
3-E4592A          T0106 - SurgeArrest Plus                               0.00000 0.00000  0.00000  0.00000
3-J2094A          HP-PB 16 Channel RS-232C Modem Conn MUX                0.00000 0.00000  0.00000  473.00000
4-A3666A          4.2GB LP Disk Drive                                    0.00000 0.00000  0.00000  350.00000
5-FEE             Per Call Fee                                           0.00000 0.00000  0.00000  0.00000
5-FEE             T0101 - Per Call Fee                                   0.00000 0.00000  0.00000  0.00000
5-FEE             T0102 - Per Call Fee                                   0.00000 0.00000  0.00000  0.00000
5-OVTLABOR        T0101 - Overtime service labor                         0.00000 0.00000  0.00000  0.00000
5-OVTLABOR        T0102 - Overtime service labor                         0.00000 0.00000  0.00000  0.00000
5-STDLABOR        T0101 - Standard service labor                         0.00000 0.00000  0.00000  0.00000
5-STDLABOR        T0102 - Standard service labor                         0.00000 0.00000  0.00000  0.00000
5-STDLABOR        T0106 - Standard service labor                         0.00000 0.00000  0.00000  0.00000
5-TVLLABOR        T0101 - Travel Labor                                   0.00000 0.00000  0.00000  0.00000
5-TVLLABOR        T0102 - Travel Labor                                   0.00000 0.00000  0.00000  0.00000
5-TVLLABOR        T0106 - Travel Labor                                   0.00000 0.00000  0.00000  0.00000
ACCS-CRD-12WH     Phone Cord - 12' White                                 26.64000 0.00000  199.80000 226.44000
ACCS-CRD-25BK     Phone Cord - 25' Black                                 0.00000 0.00000  69.85000 83.82000
ACCS-HDS-1EAR     Headset-Single Ear                                     0.00000 0.00000  1034.00000 813.35000
ACCS-HDS-2EAR     Headset - Dual Ear                                     0.00000 0.00000  0.00000  527.67000
ACCS-RST-DXBK     Shoulder Rest-Deluxe Black                             16.20000 0.00000  226.80000 226.80000
ACCS-RST-DXWH     Shoulder Rest - Deluxe White                           21.60000 0.00000  163.20000 205.20000
ANSW-ATT-1000     Attractive Answering System 1000                       60.66000 0.00000  242.64000 303.30000
ANSW-PAN-1450     Panache KX-T1450 answer                                119.40000 0.00000  1194.00000 1134.30000
ANSW-PAN-2460     Panache KX-T2460 answer                                0.00000 0.00000  149.60000 169.60000
FAXX-CAN-9800     Cantata FaxPhone 9800                                  12029.50000 0.00000  66987.41000 51129.85000
FAXX-HLP-5433     Hewlett Packard FAX-310                                459.40000 0.00000  0.00000  0.00000
FAXX-RIC-060E     Richelieu Fax 60E                                      0.00000 0.00000  2404.50000 2404.50000
FAXX-SLK-0172     Sleek UX-172 fax                                       1350.90000 0.00000  1350.90000 675.45000

Happy pivoting.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Cannot insert the value NULL into column 'BASEUOFM' error when clicking on Items List in Navigation Pane

Just recently a few partners began reporting getting the error Cannot insert the value NULL into column 'BASEUOFM' when clicking on the Items list under the Inventory Navigation Pane option.




Items list error

  The name of the global temp table - in this case, tempdb.dbo.##0251007 - varies in almost all cases, but the end result of the error is the same. The issue has been identified running Microsoft Dynamics GP 2010 RTM, SP1 or SP2.

Upon further review, the issue is due to bad data in the Unit of Measure Schedule (UOMSCHDL) column in the Item Master table (IV00101). In summary, if you have an item record with a blank Unit of Measure Schedule or a Unit of Measure Schedule that does not exist in the Unit of Schedule Master table (IV40201), it will cause the Items list to fail with the error above.

The following query should help in identifying the offending record(s):

' Created by Mariano Gomez, MVP

'  This code is licensed under the Creative Commons 

'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.

SELECT * FROM IV00101 WHERE UOMSCHDL NOT IN (SELECT UOMSCHDL FROM IV40201);

Once you have identified the record(s) causing the failure, you can use the Item Maintenance window to correct the problem:

Item Maintenance window

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Customizations for the View Indented Bill (bmViewIndentedBillInquiry) window failing for GP 2010

In the past days I had been helping a Microsoft partner and good friend of mine in migrating some Modifier with VBA customizations from Microsoft Dynamics GP version 9.0 to version 2010.  In doing so, we came across an issue with one of the modified objects that needed to be converted: the Inventory module's View Indented Bill window.

View Indented Bill window - Original

The customization in question added a field to the View Indented Bill window to retrieve the quantity available for a given component item on a bill, as shown below:

View Indented Bill window - Modified

The rest of the customization included adding some VBA code to retrieve the quantity available for the component across all sites by using some simple ADO code. So far, good!

The error came into play when we attempted to print the Indented Bill Report, by clicking on the print button on the window.

Unhandled script exception error

After selecting the report type on the destination on the Report Destination window and clicking Ok to generate the actual report, Microsoft Dynamics GP was complaining about an Unhandled script exception an its inability to find script PrintTraverseCallback of form "[Not Found]".

As every good developer would know, troubleshooting this issue meant:

a) Testing that the error did not happen with the original form. Checked!
b) Testing that modifying the form did not corrupt the forms dictionary file, FORMS.DIC. Not Checked!
c) Testing that adding VBA code did not introduce the error, Not Checked!

In cases b) and c) even after rebuilding the forms dictionary and removing the VBA code, the error still occurred, but for case a) the system printed the report without presenting the Unhandled script exception error.

So being the, ehem!, well connected guy I am, I decided to call my good friend [Name excluded to protect the innocent] over at Microsoft. A test was performed to modify the same window and grant security to the modified version of the window, then run the report. The results were the same - Unhandled script exception. Now I could confirm that this was in effect a bug.

The bug also causes the BM Bill of Materials report would also not print the indented bill as expected.

BM Bill of Materials printed from original View Indented Bill window

Now, here is the report printed from the modified window.

BM Bill of Materials printed from the modified View Indented Bill window

Consequently, a problem report has been written up for this to be addressed in the near future - hopefully quick enough for the affected client.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More