Asynchronous pluggable Protocol Handler for Dynamics GP (for drilldown/drillback)

Protocol (or scheme) handlers are a windows operating feature that take a URI request from the windows operating system and redirect to an application that has previously been registered with windows to “handle” that URI scheme. That application then performs some action based upon the passed URI. A most common example of these you may have encountered is with a “mailto:[email protected]” link that launches a mail program on the client computer.

Drill down (or drill back ) is one of the seriously under promoted and under utilised features of GP, it allows users to hyperlink from another application into almost any transaction or master data (card) in GP. If a drill down is not already defined by the base GP application, then custom drilldowns can be created using drill down builder (extra product with smart list builder).

One of the most common questions regarding this functionality is if the drill down can open GP, it can not, GP must be logged in and running for the drill down to work. The machine also needs access to the domain controller for authentication purposes.

We can take advantage of protocol handers in our own applications to interact with Dynamics GP.

[more]Drill down is most often seen demonstrated from excel, or from reporting services but is also useful using any application that can allow clicking of URLs. I personally only found out about drill downs when a colleague came back from Convergence 2006, with some literature that mentioned drill down and the other big thing of the day office “Smart Tags”. I enthusiastically blogged about both, even implemented smart tags, but that post seems to have been lost in blog site migrations and time.

How to include a drill down link in an email

Start the email, outlook new email, create a title, then put in the text  the following:

“Please could you see if the customer TEST could have an increased credit limit please.”

Now select and right click the customer ID test in the text just entered. Select hyperlink, and paste the following into the hyperlink edit box:

dgpp://DGPB/?Db=&Srv={SERVER}&Cmp={DatabaseNameForCompany}&Prod=0&Act=OPEN&Func=OpenCustNmbr&CUSTNMBR=TEST

Replace the bits in {}, including the braces with the correct details for your GP server.

Now send the email. If the recipient has GP open with the correct company, when they click the customer number, GP will open the debtor card on that customer. Yes this is clunky, but if a customisation to GP is made to auto-build the textual stub for the email and launch the new email it becomes no effort. There can be a “button” on the debtor maintenance screen to send an email to a college about this debtor that generates the new email with a standard form text stub in it ready to be appended to for the needs of that particular email.

Needless to say this could also be useful to link from internal web applications or windows forms applications such as 3rd party CRM or support case management systems.

Helper SQL functions

There are over 40 helper SQL functions available in the GP company database for building uris, all named dgpp* the functions generate the company specific URI for you.

Here are some examples extracted from these UDFs.

Full list from my GP instance:
dgppAccountIndex
dgppAcctNumberDetail
dgppAssetIndex
dgppBudgetTrxSummary
dgppCheckbookID
dgppCMTransactionZoom
dgppCustomerID
dgppEmployeeID
dgppEquipmentID
dgppGLBatchEntry
dgppItemID
dgppItemTransactionNumber
dgppJournalEntry
dgppJournalInquiry
dgppManufactureOrder
dgppMFGBOM
dgppMFGItemID
dgppMOView
dgppOpenPMTrxNmbr
dgppOpenPOPEnterMatchInvRcptNmbr
dgppOpenPOPReceivingRcptNmbr
dgppOpenSalesOrder
dgppOpenSecurityRoles
dgppOpenSecurityTask
dgppOpenUserSecurity
dgppOpenUserSetup
dgppPayablesTransactionNumber
dgppPMBatchEntry
dgppPTEExpenseDocument
dgppPTETimesheet
dgppPurchaseOrder
dgppPurchaseOrderPM
dgppPurchaseOrderPOTRX
dgppPurchaseOrderReceipt
dgppPurchaseOrderReturn
dgppPurchaseRequisitionNumber
dgppReceivablesTransactionNumber
dgppRMANumber
dgppRMBatchEntry
dgppRTVNumber
dgppSalesOrder
dgppSalesOrderIVC
dgppSalesOrderRM
dgppSalesOrderSOP
dgppSalespersonID
dgppServiceRecordType
dgppSmartListView
dgppUPRESSDirectDeposit
dgppUPRESSEmpProfile
dgppUPRESSSkillsTraining
dgppUPRESSW4
dgppUPRTimecard
dgppVendorID
dgppWorkOrderNumber

To run the SQL function, lets take dgppCustomerID as an example, where xxx represents the customer id (debtor id):

SELECT [dbo].[dgppCustomerID] (1, 'xxx')

Gives us result:
&Act=OPEN&Func=OpenCustNmbr&CUSTNMBR=xxx

SELECT  [dbo].[dgppSalesOrderSOP](1,3,11,'C12345',4,11,1,1102)

Gives us result:
&Act=OPEN&Func=OpenSOP&SOURCE=11&DCSTATUS=3&SOPNUMBE=C12345&SOPTYPE=4&ModuleID=11&CurncyView=1&CallID=1102

More about the drill down parameters:
DCSTATUS in the SOP example, typically uses the following integer values:
0: Reserved key / unused
1: Work - unposted or editable transactions in work tables
2: Open - posted transactions that are still open/uncleared
3: History - completed, paid, or archived transactions in history tables

CurncyView in the SOP example, typically uses the following integer values:
0: Originating Currency - The currency in which the transaction originally took place.
1: Functional Currency - The core home currency of the specific company database.
2: Reporting Currency - An additional currency used for corporate reporting or consolidation.

ModuleID, this is the Dexterity GP Module being referenced- see key at end of this post.

CallID, this is the calling reference, gives opened window context about the calling form or window ID. As we are not opening from dexterity I'm not sure this is important or not.

Protocol Handler for Dynamics GP

Protocol handlers register themselves with the operating system under the registry key H_CLASSES_ROOT . This is where the application and parameters are defined that should be executed when the operating system encounters the registered scheme.

Registry key [HKEY_CLASSES_ROOT\dgpp]

[HKEY_CLASSES_ROOT\dgpp]
@="\"URL:Dynamics GP Protocol\""
"EditFlags"=hex:02,00,00,00
"URL Protocol"="\"\""

[HKEY_CLASSES_ROOT\dgpp\DefaultIcon]
@="\"Microsoft.Dynamics.GP.ProtocolHandler.exe,1\""

[HKEY_CLASSES_ROOT\dgpp\shell]

[HKEY_CLASSES_ROOT\dgpp\shell\open]

[HKEY_CLASSES_ROOT\dgpp\shell\open\command]
@="\"C:\\Program Files (x86)\\Common Files\\Microsoft Shared\\Dexterity\\Microsoft.Dynamics.GP.ProtocolHandler.exe\" \"%1\""

It can be seen that for Microsoft Dynamics GP, the protocol handler is an application located in the location C:\Program Files (x86)\Common Files\microsoft shared\Dexterity in that folder, having the plain english name of Microsoft.Dynamics.GP.ProtocolHandler.exe and the associated .config file gives us a clue this is a .NET assembly. This application is responsible for taking the URL request from the operating system and handing it over to the Dynamics GP process. This works both in webclient and native fat GP. Note that there are particular issues around drill down and getting it working in shared desktop environments like terminal server/citrix.

Opening up the .config reveals that this is indeed a .NET application and obviously is using WCF named pipes to talk to Dynamics GP (I am so glad I did a lot of work with WCF in the early days to know this stuff!).

There may be a future blog post where I poke around this named pipe a little -assuming I get some time. For now, essentially named pipes allow processes running on the machine to talk to one another using memory mapped files. Something I’ve used for my own applications from time to time. It is a really fun and exciting subject that is outside the scope of this post.

Next post in this series

Further reading

• drilling back source data dynamics gp 2013 using dashboards
• hands on with microsoft dynamics gp 2015 r2 set up drill down
• Tech Tuesday: Using Drill Down Builder outside of Excel Report Builder - from maker of drill down builder
• Feature of the day drill down builder - GP blog
• Drill Down to Microsoft Dynamics GP windows from Excel - from microsoft help

GP Dexterity Module lookup

ref: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/how-to-use-dexterity-sanscript-code-to-check-if-module-is-registered