How to Display Parent Company Information on SOP documents

A few days aback I encountered a really interesting discussion on the Microsoft Dynamics GP Community forum where the user needed to get the parent customer information on the SOP Blank Invoice Form. I asked myself, "Well, how difficult can that be?"

The first thought around these requests is, you create a table relationship between the Sales Transaction Work (SOP_HDR_WORK) table and the RM Customer Master (RM_Customer_MSTR) table by Customer Number and you get the parent customer ID. In that train of thought you immediately realize that there's no way to alias the RM Customer Master table back into the table relationship to retrieve the parent customer ID information. [NOTE TO SELF: this would be a cool Report Writer feature, but I digress.]

Of course, a second alternative is to use VBA, but many customers and partners are wary of implementing a VBA solution, especially when they are considering upgrading to Microsoft Dynamics GP 2013 and taking advantage of the web client. VBA customizations simply do not work with the web client.

As I thought through an alternative, I quickly realized this would be a good fit for the Support Debugging Tool and it's awesome Runtime Execute feature. As it turns out, the Support Debugging Tool is capable of executing Dexterity sanScript code within the context of any dictionary, but not only that; it provides some really cool templates that save time and minimizes the amount of code an entry level developer needs to create to accomplish a solution. In turn, there are a number of Report Writer API scripts built into the Dynamics application dictionary (DYNAMICS.DIC) to allow third party developers to expose data from their tables onto an existing Microsoft Dynamics GP report without having to write a complex interface to do so.

The Solution

1. The first task at hand is to open the Runtime Execute window in the Support Debugging Tool. Once the window is open, click on the Helper button to open the Insert Helper Function window. From the drop-down list, select template for rw_TableHeaderString.

Insert Helper Function window

Below is the signature for the function as we will use it.

The rw_TableHeaderString function

function returns string sData;

in integer dict_id;  {Support Debugging Tool Dictionary ID}
in string report_name;  {Support Debugging Tool Script ID to call}
in string sNumber;  {Parent Customer ID field we will retrieve}
in integer sType;  {Not used, we will default to 0}
in integer iControl;  {which piece of data to return} 

We will use this function to return a string value for the selected Parent Customer ID information field to a Report Writer calculated field.

By clicking OK, the Support Debugging Tool inserts the code to get us started in the right direction.

Helper Code

Very cool! All that's left now is to add the extra bits of code to retrieve the parent customer information from the RM Customer Master. In addition, we need to make sure that this code executes in the context of the Microsoft Dynamics GP product.

Additional Code

The template function even indicates where our code should be inserted and as you can see this is easy stuff for any entry level Dex developer. All we did here was use the MBS_Number local field as the parameter value that will contain our parent customer ID (to be passed from Report Writer) to get the piece of data we are interested in based on the value that we will supply to the MBS_Control local field (again, from Report Writer). The returned table value will then be stored in the MBS_TableHeaderString local field.

The Support Debugging Tool then goes to action and call its MBS_Param_Set API function to talk back to the Report Writer API script and deliver the values retrieved to it. Isn't this awesome!

2. The second part of our solution consists of 3 parts really: a) we need to create a table relationship between the Sales Transaction Work table and the RM Customer Master table as this relationship does not exist out of the box, b) We then need to create calculated fields for the pieces of Parent Customer data we would like to retrieve, and c) We need to complete the layout and grant security to the modified report.

I will assume for this article that you are familiar with the processes of creating new table relationships in Report Writer and laying out the new calculated fields and granting security to the modified report. You can always browse the Report Writer manual accompanying your Microsoft Dynamics GP application for information of these processes, so my attention will focus solely on the calculated field.

In this example, I want to retrieve the parent customer name. For this we will create a new calculated field as follows:

a) Create a new calculated field, in this case I will call it (A) Parent Name. The result type of the calculation will be a String and the expression type is Calculated.

b) Since we are using the rw_TableHeaderString user-defined function, we will choose this accordingly:

rw_TableHeaderString

c) Now we have to setup the parameters as indicated by the function signature above. The first parameter we will add is the Support Debugging Tool's dictionary ID, 5261. We will add this as an integer constant.

Dictionary constant 5261 to call the script we wrote

d) Now we have to call the Runtime Execute script we created, GETPARENTINFO. We will add this as a string constant.

GETPARENTINFO

e) We now need to pass the parent customer number parameter, our default (not used) parameter, and the value number for the field that will be retrieved for the parent customer data, according to how we coded our script above. This is shown below.

Last 3 parameters.

The RM_Customer_MSTR.Corporate Customer Number field is retrieved from the RM Customer Master table which we created via a new table relationship and subsequently adding that table relationship to our report.

The following shows a muck-up of the report layout with the parent customer name calculated field created:

Report Layout

The report should display something like this when run in Microsoft Dynamics GP:

Invoice Layout

Final Notes

The above script could be extended to retrieve other pieces of data for our parent customer, like contact, address, city, state, zip, phone numbers, etc. In this case you will need to add more entries to the case...end case statement above and retrieve the correct table column. You can find more information on the table columns for the RM Customer Master in the Microsoft Dynamics GP software development kit (SDK).

This solution is absolutely compatible with Word Templates and the Web Client. In the case of Word Templates, you will need to export a new report definition file (XML) and import into your template fields. In the case of the Web Client, all the components above are within the confines of a Dexterity application and the Support Debugging Tool happens to be compatible with the Web Client, just as much as Report Writer is. Your modified report should render absolutely fine on the Silverlight canvas control, if printing the standard report.

For all workstations to take advantage of the Runtime Execute script, the Support Debugging Tool must be deployed with the recommended configuration - all workstations pointing to a centralized configuration file, debugger.xml.

---

The Support Debugging Tool never ceases to amaze me. You can find similar article to this one and more information here:

The Dynamics GP Blogster - Adding Customer Item User Defined Field to SOP Invoice

Developing for Dynamics GP - The Support Debugging Tool Portal

Until next post!

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

Read More

Microsoft Dexterity Training back by popular demand

(C) Microsoft Corporation

You asked for it and we are responding to your request. Given the success of our past Microsoft Dexterity training roadshow, we are launching a new round of training sessions for 2014. This time, we have expanded the number of training offers to cover advanced Dexterity topics, and for the first time, we are introducing Visual Studio Tools for Dexterity developers (and .NET developers too).

• DEX001: Microsoft Dexterity Fundamentals - classes in Atlanta, Miami, and Toronto
• DEX002: Advanced Microsoft Dexterity Development - classes in Boston and Montreal
• DEX003: Visual Studio Tools for Microsoft Dexterity Developers - classes in Las Vegas and Seattle.

Want to attend, but don't want to leave your offices? We will come to you. (*)

You can find all about each class offered on our training page. For more information, contact Chuck Boyles at +1 (678) 993-6971.

(*) Some restrictions apply.

Until next post!

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

Read More

Accessing Microsoft Dynamics GP Default Settings from Visual Studio Tools

Rarely you will hear the Dex.ini file being called the Microsoft Dynamics GP defaults file. However, the Dex.ini contains keys (and their associated values) that define how Microsoft Dynamics GP behaves in some cases. For example, SQLLastUser=sa stores the login of the last user who accessed the system on that specific instance of Microsoft Dynamics GP, in this case, 'sa'. Likewise, AutoInstallChunks=TRUE, allows Microsoft Dynamics GP to bypass prompting the user for new code (chunk file) to be installed at the time of launching the application. There are other settings controlling how certain features behave.

You can find a list of Dex.ini settings here.

Recently, I was helping a good friend of mine up in Maine who attended my Dexterity class in Boston and this time he wanted to know how to read the path for the Microsoft Dynamics GP help file. Of course, there's a simple (predefined) method for this as my good friend Patrick Roth with the Escalation Engineering team in Fargo pointed out, if you are familiar with some of Microsoft Dynamics GP form procedures:

string strPath = "";
strPath = Dynamics.Forms.MainMenu.Functions.GetDynamicsHelpPath.Invoke();
MessageBox.Show(strPath);

So, let's assume you had no clue this function existed. How would you address this issue? Frankly, I can think of at least 3 ways to do this:

1) Using a hybrid method whereby you create a Dexterity dictionary to wrap the Defaults_Read() Dexterity sanScript function into a user-defined global function. This method presents a challenge as you need to use the Dictionary Assembly Generator (DAG.EXE) to create an assembly you can reference from your VST project. In addition, you may or may not want to add an entirely new dictionary into the picture, when delivering your final solution. However, in case you are interested, this is the implementation:

GetDexIniSetting function

For more information on developing hybrid applications with Dexterity and Visual Studio Tools take a look at my articles:

Developing Microsoft Dynamics GP hybrid integrating applications
Hybrid development for the Managed Code developer
Hybrid development for the Managed Code developer (cont.)

2) You could use the Continuum API from Visual Studio Tools to access the Defaults_Read() sanScript function. The Continuum API is the Component Object Model (COM) API that is available for Microsoft Dynamics GP. The Continuum API documentation is available for download here. In this method, all you have to do is add a reference to the interop.Dynamics.dll COM library, which hosts the Continuum API.

// Created by Mariano Gomez, MVP
// This code is licensed under the Creative Commons 
// Attribution-NonCommercial-ShareAlike 3.0 Unported License.
// http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode

public string GetDexIniSetting(string searchKey)
{
   ParameterHandlerClass myParamHandler = new ParameterHandlerClass();
            
   try
   { Dynamics.Application gpApp = (Dynamics.Application)Activator.CreateInstance(Type.GetTypeFromProgID("Dynamics.Application"));
     if (gpApp == null) 
       return "";
     else
     {
       string passthrough_code = "";
       string compile_err;
       int error_code, result;

       result = gpApp.SetParamHandler(myParamHandler);
       myParamHandler.IN_Key = searchKey;

       passthrough_code += "local boolean err_val;";
       passthrough_code += "local string dex_key_val, dex_key;";
       passthrough_code += @"err_val = OLE_GetProperty(""IN_Key"", dex_key); "; 
       passthrough_code += "dex_key_val = Defaults_Read(dex_key);";
       passthrough_code += @"err_val = OLE_SetProperty(""OUT_KeyVal"", dex_key_val);";

       gpApp.CurrentProductID = 0; 

       error_code = gpApp.ExecuteSanscript(passthrough_code, out compile_err);
       return myParamHandler.OUT_KeyVal;
     }
   }
   catch
   { MessageBox.Show("Failed to initialize gpApp");
     return "";
   }
 }

public class ParameterHandlerClass
{
  public string IN_Key { get; set; }
  public string OUT_KeyVal { get; set; }
}

In the above code, we define a parameter handler class with couple properties for the key that will be read from the Dex.ini and the key value to be retrieved.

In our GetDexIniSettings method, we instantiate our class and use the Continuum SetParaHandler() method to load the IN_Key property value. We then setup our pass-through code and invoke sanScript's OLE_GetProperty() function to retrieve the value of the IN_Key from our VST application via some old fashioned OLE automation. The OLE_SetProperty() function then returns the value to our class property, OUT_KeyVal.

That's it!

3) The third method uses some classic C# reflection to get the path of the AddIns. It then converts this to the path of the Data folder and the dex.ini file, and then uses the Pinvoke() method to read the key in the dex.ini and return the value.

[DllImport("kernel32.dll", CharSet = CharSet.Unicode)]
 static extern uint GetPrivateProfileString(
 string lpAppName,
 string lpKeyName,
 string lpDefault,
 StringBuilder lpReturnedString,
 uint nSize,
 string lpFileName);

//get path which should be the addins folder
String strPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);

//look for \Addins path
if (strPath.EndsWith(@"\AddIns", true, System.Globalization.CultureInfo.InvariantCulture) == true)
{ //and if we find one replace with the data folder
  strPath = strPath.Replace(@"\Addins", @"\Data\Dex.ini");
}
else
{ //if not in addins then we must be in the root GP folder (like GP 2013 does with GP addins) so tack on \Data
  strPath = strPath + @"\Data\Dex.ini";
}
 
StringBuilder sb = new StringBuilder(100);
uint res = GetPrivateProfileString("General", "Pathname", "", sb, (uint)sb.Capacity, strPath);
MessageBox.Show(sb.ToString());

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the 
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

The above method comes courtesy of my good friend Patrick Roth with the Escalation Engineering team at Microsoft.

All the above methods are generic in nature and can read more than just a specific key. It's good to know that there's more than one way to do things. If you are going all hybrid, there's a code for that! If you want to be self-contained, there's a code for that! If you want to deviate completely from sanScript because you hate it or don't know it, there's a code for that! Whatever route you choose it must work to your benefit.

As a good Australian friend of mine would say, "The right solution is the one that works!".

Until next post!

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

Read More

Microsoft Dexterity Training Roadshow - Next Stop: Boston

(C) Microsoft Corporation

The Microsoft Dexterity Training Roadshow rolls around the United States with its next and final stop in Boston, Massachusetts from July 15 - July 19, 2013. Our training will be hosted in the Regus Business Center, from 9:00 AM to 5:00 PM.



Regus Business Center
60 State Street, Suite 700
Boston, MA 02109

We still have some seats left, so if you want to learn some of the development techniques used by Microsoft Dynamics GP developers this is your chance. For registration information, click here.

Stay tuned as we will announce our second Roadshow coming up.

Until next post!

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

Read More

Microsoft Dexterity Training Roadshow - Next Stop: Chicago

(C) Microsoft Corporation

The Microsoft Dexterity Training Roadshow rolls around the United States with its next stop in Chicago, Illinois from May 13 - May 17, 2013. Our training will be hosted at the beautiful John Hancock Center building, from 9:00 AM to 5:00 PM.

John Hancock Center in Chi-town

John Hancock Center
875 N. Michigan Avenue
31st Floor
Chicago, IL 60611

We still have some seats left, so if you want to learn some of the development techniques used by Microsoft Dynamics GP developers this is your chance. For registration information, click here.

Until next post!

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

Read More

Microsoft Dexterity Training Roadshow - Next Stop: Seattle

(C) Microsoft Corporation

The Microsoft Dexterity Training Roadshow rolls around the United States with its next stop in Seattle, Washington from April 22 - April 26, 2013.

We still have plenty of seats left, so if you want to learn some of the development techniques used by Microsoft Dynamics GP developers this is your chance. For registration information, click here.

Until next post!

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

Read More

Microsoft Dexterity Training Roadshow - Atlanta

Don't miss this excellent Microsoft Dexterity training opportunity in the beautiful city of Atlanta, from January 21 - 25 at our midtown facilities. You can find full registration information here or you can contact Chuck Boyles. Other cities and schedules available.

(C) Chuck Koehler

Seating is limited so please sign up now.

Until next post!

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

Read More

Resizing SmartList panes with the Support Debugging Tool

I have a secret to tell...

At the past GPUG Summit in Seattle, I presented a session,  ITP16 Advanced Microsoft Dynamics GP Administration with the Support Debugging Tool. The session concluded with a demo of the Splitter Control feature implemented with the all too popular Support Debugging Tool. The few individuals in attendance were very impressed, since SmartList resizing had always been a royal pain in the rear for them.

If you have been following the Developing for Dynamics GP blog lately, my dear friend and partner of many presentation battles, David Musgrave has now detailed the secrets of the Splitter Control and provides the code to implement it using the Support Debugging Tool.

SmartList with the Splitter Control (shown inside the red circle)

The Splitter Control uses some cool unsupported Dexterity features so please play with this stuff in a test environment. IF you find anything cool or uncool about it, and you would like to express your feelings, please add your comment to David's article:

Update: Resizing SmartList TreeView and ListView panes using the Support Debugging Tool

Finally, there's no similar control in Silverlight for this Dexterity feature so this will not work with the Microsoft Dynamics GP 2013 Web Client.

Until next post!

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

Read More

Microsoft Dexterity Training Roadshow

I'm pleased to announce that I will be visiting a number of cities throughout this year and next conducting Microsoft Dexterity training for the uninitiated. This in response to a number of requests I have had in the past to bring the action directly to your doors - well, somewhere close anyways.

IntellPartners presents Microsoft Dexterity Training for end-user developers, ISV developers and implementation partners.

Microsoft Dexterity is the application development system used to create and customize the award winning Microsoft Dynamics GP, and this course is designed to enable developers to customize GP or create add-on applications. Also, if you are a Visual Studio Tools developer, then this course is for you! Come learn the inner works of Microsoft Dexterity applications and be the star in your organization.

You will learn:

• Microsoft’s basic programming techniques to develop core Dynamics GP modules
• How to customize Dynamics GP forms and reports
• How to develop add-on applications to meet specific end user needs
• How to package and ship your dictionary to avoid issues with Dynamics GP updates
• How to develop add-on applications with the same look and feel as Dynamics GP and provide seamless interface between the applications

Course Topics

Dexterity resources and components         Create a transaction entry project
Create a basic data entry project                Dexterity utilities
Script debugger                                          Dynamics GP integration project
Create a basic lookup project                     Object triggers
Create a multi-user data entry project        Report Writer

Training Schedule

Atlanta, GA	December 10 – 14, 2012
Atlanta, GA	January 21 – 25, 2013
Las Vegas, NV	February 25 – March 1, 2013
London, United Kingdom	April 1 – 5, 2013
Seattle, WA	April 22 – 26, 2013
Chicago, IL	May 13 – 17, 2013
Bogota, Colombia	June 10 – 14, 2013
Boston, MA	July 15 - 19, 2013

Enrollment is limited to ten students per class, on a first come, first serve basis.

For more information on the course and registration, please click here or contact Chuck Boyles at cboyles@intellpartners.com. Please leave your comments if you are interested in the course as well.

Until next post!

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

Read More

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

Week 5 of the "Feature of the Day" series over at Inside Microsoft Dynamics GP is a mix bag of pleasant surprises: improvements in Sales Order Processing, Accounts Receivable, Human Resources/Payroll, and the Dexterity Runtime Engine, you could easily see how keeping track of all these things at once could drive anyone to the loony bin. As usual, this is where I come in by compiling all these features in one place for your delight.

Feature of the Day: Relate-Link Items for Suggested Sales

Two new windows: Suggest Sales Item Entry and Suggest Sales Item Maintenance

Original article: Feature of the Day: Relate-Link Items for Suggested Sales
Link to presentation: GP2013 Relate-Link Items for Suggested Sales.ppsx
Learning script: N/A

Feature of the Day: Cetification, Licensing, and Training Management

CLTM now allows to delete history (password protected) and rows

Orginal article: Feature of the Day: Cetification, Licensing, and Training Management
Link to presentation: GP2013 13915_CLTM_History FOD.ppsx
Learning script: N/A

Feature of the Day: Multicurrency Apply in RM Cash Receipts Entry

Multicurrency cash receipts can now be applied on the fly to invoices

Original article: Feature of the Day: Multicurrency Apply in RM Cash Receipts Entry
Link to presentation: GP2013 11800_MC Apply FOD.ppsx
Learning script: N/A

Feature of the Day: Select Printer at time of Print

Select Printer at Print Time

Original article: Feature of the Day: Select Printer at time of Print
Link to presentation: GP2013 13715_Select_Printer FOD.ppsx
Learning script: N/A

NOTE: It was just about darn time!

Feature of the Day: Deduction in Arrears Templates

Mandatory Arrears and Arrears Template Setup windows

Original article: Feature of the Day: Deduction in Arrears Templates
Link to presentation: GP2013 Deduction in Arrears Templates FOD.ppsx
Learning script: N/A

In all fairness, while they were no learning scripts, the presentations available acted very well as such. Congrats are in order for the Microsoft Dynamics GP Product Management team.

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

Automating the start and stop of Microsoft Dynamics GP logs with the Support Debugging Tool - Part 2

Yesterday, we tackled the first part of this Support Debugging Tool project, showing how to implement the logging trigger portion. To recap, the application logging files (DEXSQL.LOG and Dexterity Script Log) will begin to record when the Hold checkbox on the Vendor Maintenance window is marked. However, registering the logging trigger will only occur when the Vendor Maintenance form is opened, and unregister, when the Vendor Maintenance form is closed. This will provide an effective set of actions to be recorded as opposed to logging every single action that occurs within the application.

Now that we have implemented the logging portion, we need the two non-logging triggers: the trigger that will start the logging trigger, and the one that will stop it.

The Start Trigger

We will start by setting up the non-logging trigger that will activate the VEND_HOLD_CHG logging trigger (which in turn will begin logging only when the Hold checkbox is marked). We will want to attach this trigger to the Vendor Maintenance form PRE script. We want the logging trigger to register once the Microsoft Dynamics GP code has run, hence we will startup the logging trigger after the original event. Unlike the logging trigger, this trigger start automatically upon the user launching and login into Microsoft Dynamics GP.

ST_VENDHOLD_LOG non-logging trigger - Resource tab

We will move to the Script tab and use a Helper function to assist with adding the code that will start up the logging trigger. By clicking on the Helper button, the Insert Helper Function window displays, from where we can choose the Start an Automatic Mode Trigger ID option in the drop-down list.

Insert Helper Function window

Then we can move to select the logging trigger ID, VEND_HOLD_CHG created previously.

Again, the Support Debugging Tool does most of the sanScript coding needed for this purpose by calling the built-in API procedure MBS_Trigger_Start to start up our VEND_HOLD_CHG trigger. Very convenient!

ST_VENDHOLD_LOG non-logging trigger - Script tab

Next we need to setup the non-logging trigger to disable the logging trigger.

The Stop Trigger

For this case, and instead of trying to replicate all these setups, we can take advantage of the Duplicate button of the Automatic Debugger Mode setup window to create a copy of the start up trigger.

Duplicate Button

Finally, we adjust the non-logging trigger to fire after the Vendor Maintenance form post script has executed, and change its script to call the MBS_Trigger_Stop Support Debugging Tool API function to un-register the VEND_HOLD_CHG trigger, effectively stopping all logging.

EN_VENDHOLD_LOG non-logging trigger

Now is time to turn on our non-logging triggers (which are set to start up automatically on login in into the Microsoft Dynamics GP application).

Turning on non-logging automatic start only triggers

A look the Automatic Debugger Mode status window shows our non-logging triggers waiting for the Vendor Maintenance window to open.

Automatic Debugger Mode Status window

If we open the Vendor Maintenance window we will see our trigger VEND_HOLD_CHG trigger is automatically registered (along with a secondary trigger that captures the original value of the field prior to being selected so it can be restored if needed). The Support Debugging Tool main window will change the color and prompt of the Automatic Debugger Mode button. When the Hold checkbox is marked, the logging trigger activates the logs and displays the desktop alert.

And all of this occurred without the end-user's intervention. In addition, the DEXSQL.LOG and Script Log files will only contain all events that occurred while the window was opened and records were manipulated. Once the Vendor Maintenance window was closed, the logs were suspended.

I have attached the configuration file to be imported with the Support Debugging Tool's Configuration Export/Import window. Once you import the XML file just activate the non-logging triggers as indicated above and you should be good to go.

Vendor Hold triggers - VEND_HOLD.zip

Discover the power of the Support Debugging Tool.

Until next post!

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

Read More