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.
Below is the signature for the function as we will use it.
The rw_TableHeaderString function
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.
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.
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:
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.
d) Now we have to call the Runtime Execute script we created, GETPARENTINFO. We will add this as a string constant.
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.
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:
The report should display something like this when run in Microsoft Dynamics GP:
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
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
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 |
 |
| Additional Code |
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 |
 |
| 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 |
 |
| 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/
