Aaron Berquist Rotating Header Image

VBA

Creating Custom Desktop Alerts In Dynamics GP

Jul 7th, 2012
by Aaron Berquist.
7 comments
Sample Desktop Alert

First off, full credit goes to David Musgrave for sharing the bulk of this code with me and for providing some great troubleshooting help as I worked to call the code properly from VBA.

Allow me to share some background information. While at Convergence 2012 in Houston this year, I attended a presentation on the Support Debugging Tool by David Musgrave and Mariano Gomez. (Protip: I’ve found the sessions they run to be the most valuable ones at Convergence, by far.)

At the session, David mentioned in passing that the Support Debugging Tool was configured to raise a “Desktop Alert” when certain criteria were met. As soon as he demoed this, I thought – this would be a great way to provide feedback to a user without interrupting their workflow. Up until now, any time I wanted to provide a message to the user (such as “Data Saved”, etc) the only tool I had in my toolbox was a message box which they would have to click on to dismiss.

After emailing David with some questions about how he had implemented this feature, and with a lot of hand-holding from him as well, I had managed to code up a subroutine in VBA (and later, VB.NET) which I could call from a customization to send a Desktop Alert to the user. Here is a sample:

Sample Desktop Alert

The code to do this is actually pretty simple, and uses the (unsupported, undocumented) pass-through Dexterity method to call the Dexterity code which raises a desktop alert:

VBA / VB.NET

Public Sub RaiseDesktopAlert(AlertTitle As String, AlertMessage As String)
        'Author: Aaron Berquist
        'Date: 07/16/2012
        'Purpose: Raise a Dynamics GP "Desktop Alert" message in the bottom right-hand corner of the screen.
        'Inputs: 
        'AlertTitle - The title to be displayed in the Desktop Alert
        'AlertMessage - The message to be displayed in the Desktop Alert
        'Based on sample code provided by David Musgrave
        'http://blogs.msdn.com/b/developingfordynamicsgp/

        Dim CompilerApp As Object
        Dim compilercommand As String
 
        CompilerApp = CreateObject("Dynamics.Application")
        Dim CompilerError As Integer
        Dim CompilerMessage As String = ""
 
        CompilerCommand = "local string IN_Title;" & vbCrLf
        CompilerCommand = CompilerCommand & "local string IN_Message;" & vbCrLf
 
        CompilerCommand = CompilerCommand & "IN_Message = " & Chr(34) & AlertMessage & Chr(34) & ";" & vbCrLf
        CompilerCommand = CompilerCommand & "IN_Title = " & Chr(34) & AlertTitle & Chr(34) & ";" & vbCrLf
        CompilerCommand = CompilerCommand & "call wfDisplayDesktopNotification, IN_Title, IN_Message;"
 
 
 
        'Set the Product ID to 0 (Microsoft Dynamics GP)
        CompilerApp.CurrentProductID = 0
        'Execute SanScript

        CompilerError = CompilerApp.ExecuteSanscript(compilercommand, CompilerMessage)
    End Sub

I hope you find this code useful. I have have good feedback from several users now who appreciate the fact that the Desktop Alert does not interupt their workflow, yet still provides valuable feedback to them about certain actions which have occured inside the system.

Post to Twitter

Tool for Improving “Mark All” Speed in Contract Administration

Dec 1st, 2010
by Aaron Berquist.
5 comments

One of my users complained recently that, when using the “Mark All” button on the Revenue Recognition screen in Contract Administration (Dynamics GP: Transactions: Contract Administration: Revenue Recognition), it would sometimes take 6-8 hours for the process to complete. This was not to post the revenue, merely to mark all of the records in the window for processing.

Now, in some of our larger company databases, over 30,000 records were being marked to process, due to a combination of:

  • # of contract records
  • the business process of recognizing multiple months worth of revenue

Clearly, this is not an acceptable amount of time to wait for a process to complete. I arranged to do a SQL trace while the user clicked the “Mark All” button. I found that the Revenue Recognition window would run a SQL stored procedure (SVC_Check_Contract_Revenue) for every contract record in the window to determine if the contract was “Marked to Post” by another user. If so, a message would display, alerting the user to the fact that another user had a record marked to post. Technically, the code was:

  • examining the SVC00625 table (SVC_Contract_Revenue_WORK)
  • looking at each contract/fiscal period combination to see if another user also had that contract/fiscal period record marked to post (MKDTOPST = 1).

The key thing I found was that this routine was being run row-by-row, one record at a time. Even though the check was very quick to complete for any one given record, the number of records it had to check added up to the 6-8 hours I mentioned earlier. Fortunately, I was able to devise a solution to this issue. In reality, my users did not care if someone else posted the record, and did not need to know which contract in question was going to be posted by someone else. It was enough for them to know “Hey – some of the records you want to mark are currently marked by someone else. I’m going to skip marking those particular records, and just mark the ones that are safe to post.” By taking this approach, I could craft a routine which would work on ALL records at once, which was orders of magnitude faster.

The solution I devised to this problem involved 4 components:

  1. I modified the Revenue Recognition window to add my own “Mark All” and “Unmark All” buttons. I hid and disabled the actual “Mark All” and “Unmark All” buttons in Modifier.
  2. I created a custom stored procedure (X_OT_SVC_REVREC_MARK_UNMARK_ALL) to mark all/unmark all records to post. It will skip any records marked by other users, and raise an alert if any are found.
  3. I added VBA to the window which will call the procedure when either of the buttons are clicked.
  4. I noticed that the window did not refresh to show the buttons as checked/unchecked after the stored procedure was run. I also noticed that if I clicked the “down” arrow on the keyboard, the line would refresh. So, I recorded a macro of pressing “down” 16 times (as that’s the number of rows that show in the window) which is then called from the VBA code.

If you’re experiencing slow performance when marking/unmarking all, try using the code pack below to implement this solution in your environment (test first!). Instructions on how to install are included, as there are a few tweaks you will have to make based on how your environment has been crafted. If you have questions, or if this solution helps you out, please leave a comment below!

Download the package here: Enhanced Mark All Revenue Recognition Routine.zip

Post to Twitter