How to display existing record notes on an existing Dynamics GP window with Modifer and VBA

The following post started out as many of my other posts -- with a question on the Dynamics GP newsgroup: how to display record notes for items on the Item Inquiry window. When I first thought of this question, it was immediately apparent that a few SQL calls in VBA to read the notes table and return the text to some VBA form, and bit of old fashioned "hide" and "show" of fields could get the job done, but then I thought, what if I could make this code act and behave just like that of standard Microsoft Dynamics GP windows with record level notes? That is, the buttons had to look and feel just like standard GP windows, then when you click on the buttons, they had to open the standard GP notes windows, and even better, what if you could attach new notes a la Dynamics GP from those same inquiry windows too.

Code History

On my first iteration, I used the new VBA UserInfo object to create an ADO connection to the company database to retrieve the Note Index from the Record Notes Master table (SY03900). Since I had constructed the notes buttons with Modifier, all I had to do was use the BeforeUserChanged events on these buttons to toggle the correct note button if there was a note present by forming a pass-thru sanScript command string with the traditional code used in pass versions of Dynamics GP to open the notes window. This method presented a problem: the traditional sanScript code behind the notes buttons is quite extensive and impractical for the purposes of delivering an elegant customization.

Click here to download package file of my first version.

There is also another known method to retrieve record notes that uses the Dynamics GP built-in NoteObj object-based approach. This method also presented another challenge as it requires defining a local variable in Modifier which could be setup as the NoteObj data type. Since the NoteObj data type is defined as a global resource in the dictionary, it cannot be used with fields created with Modifier, effectively putting this method out of reach.

The method I decided to use came about in a late night IM conversation with David Musgrave, who pointed out that before the NoteObj object-based approach was created, there were two globals procedures created to manage record notes: Note_Button and Check_For_Record_Note. I liked this approach because it meant less lines of code, I could focus solely on pass-thru sanScript -- eliminating my initial use of SQL calls -- and in turn I could also make use of a local variable in Modifier to set the value returned by the Note_Button Dexterity global procedure, and once again call the Check_For_Record_Note proc to manage the toggle of the record note buttons.

Implementation

1) With the Item Inquiry window open (Inquiry > Inventory > Item), press CTRL+F10 to open Modifier.

2) Set the drop-down on the Toolbox to Local Fields, and create 3 local variables as follow:

a) for the first variable -- '(L) Item Note Show Hide', click New to open the Local Field Definition window. Complete the information as shown below and click Ok when finished:













b) for the second variable -- '(L) RecordNoteAbsent', click New to open the Local Field Definition window. Complete the information as shown below and click Ok (twice) when finished:














c) for the third variable -- '(L) RecordNotePresent', click New to open the Local Field Definition window. Complete the information as shown below and click Ok (twice) when finished:














3) Drag the recently created fields to the window layout as shown below.












NOTE: The above illustration is provided only as a reference. Keep the record note buttons contiguous, but DO NOT STACK yet. You can return to Modifier when indicated to stack them.

Change the Visual Appearance property of the record note buttons to 2D Border. Change the Back Color Visual property to Bright Green.

4) Save the newly modified window and return to GP. Grant security to the modified Item Inquiry window.













5) Open the Item Inquiry window and verify that you can see the newly added record note buttons.












6) Add the Item Inquiry window to Visual Basic. Press CTRL+F11 on your keyboard.

7) Press SHIFT+F11 on your keyboard and add the following fields to Visual Basic: 'Item Number', the two record note buttons, and the '(L) Item Note Show Hide Field'.

NOTE: At this point, return to Modifier to stack the record note buttons and change the Visible Visual property of the '(L) Item Note Show Hide' field to False. Save the changes and return to GP. Open the Item Inquiry window to verify layout, then press ALT+F11 on your keyboard to open the Visual Basic editor.

8) Locate the ItemInquiry (Window) object under the Microsoft_Dynamics_GP project in the Project Explorer window. Double-click to open the Code Editor window.














9) Copy and Paste the following code to the Code Editor

' ------------------------------------------------------------------------------------------------------------
' NOTE RECORDS DEMO - Item Inquiry Window
' Created by: Mariano M. Gomez Bent, MVP, MCP
' Maximum Global Business, LLC.
' http://www.maximumglobalbusiness.com
' Updated by: David Musgrave
' ------------------------------------------------------------------------------------------------------------

Private Sub Window_BeforeOpen(OpenVisible As Boolean)
  Call ItemNoteShowHide_AfterUserChanged
End Sub

Private Sub ItemNumber_Changed()
  Call ItemNoteShowHide_AfterUserChanged
End Sub

Private Sub RecordNoteAbsent_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
' Dim CompilerApp As New Dynamics.Application
  Dim CompilerApp As Object
  Dim NoteIndex As Long

  Set CompilerApp = CreateObject("Dynamics.Application")

  If Not ItemNumber.Empty Then
    NoteIndex = CompilerApp.GetDataValue("'Note Index' of table IV_Item_MSTR of form IV_Item_Inquiry")
  Else
    NoteIndex = 0
  End If

  Call Note_Button(NoteIndex, ItemNumber)
End Sub

Private Sub RecordNotePresent_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
  'Run the note absent script
  Call RecordNoteAbsent_BeforeUserChanged(KeepFocus, CancelLogic)
End Sub

Private Sub ItemNoteShowHide_AfterUserChanged()
' Dim CompilerApp As New Dynamics.Application
  Dim CompilerApp As Object
  Dim NoteIndex As Long

  Set CompilerApp = CreateObject("Dynamics.Application")

  If Not ItemNumber.Empty Then
    NoteIndex = CompilerApp.GetDataValue("'Note Index' of table IV_Item_MSTR of form IV_Item_Inquiry")
  Else
    NoteIndex = 0
  End If

  Call RecordNoteShowHide(NoteIndex)
End Sub


Private Sub RecordNoteShowHide(IN_NoteIndex As Long)
' Dim CompilerApp As New Dynamics.Application
  Dim CompilerApp As Object
  Dim CompilerMsg As String
  Dim CompilerError As Integer
  Dim CompilerCmd As String

  Dim l_result As Integer

  CompilerCmd = ""
  CompilerCmd = CompilerCmd & "call Check_For_Record_Note,"
  CompilerCmd = CompilerCmd & Str(IN_NoteIndex) & ","
  CompilerCmd = CompilerCmd & " '(L) Item Note Show Hide' of window IV_Item_Inquiry of form IV_Item_Inquiry;"
  CompilerCmd = CompilerCmd & "if '(L) Item Note Show Hide' of window IV_Item_Inquiry of form IV_Item_Inquiry = 1 then"
  CompilerCmd = CompilerCmd & " hide '(L) RecordNoteAbsent' of window IV_Item_Inquiry of form IV_Item_Inquiry;"
  CompilerCmd = CompilerCmd & " show '(L) RecordNotePresent' of window IV_Item_Inquiry of form IV_Item_Inquiry;"
  CompilerCmd = CompilerCmd & "else"
  CompilerCmd = CompilerCmd & " show '(L) RecordNoteAbsent' of window IV_Item_Inquiry of form IV_Item_Inquiry;"
  CompilerCmd = CompilerCmd & " hide '(L) RecordNotePresent' of window IV_Item_Inquiry of form IV_Item_Inquiry;"
  CompilerCmd = CompilerCmd & "end if;"

  Set CompilerApp = CreateObject("Dynamics.Application")
  CompilerApp.CurrentProductID = 0 ' DYNAMICS
  CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
  CompilerError = CompilerApp.ExecuteSanscript(CompilerCmd, CompilerMsg)

  If CompilerError <> 0 Then
    l_result = MsgBox(CompilerMsg, vbOKOnly, "Dexterity Compiler")
  End If
End Sub

Private Sub Note_Button(IN_Note_Index As Long, IN_Item_Number)
' Dim CompilerApp As New Dynamics.Application
  Dim CompilerApp As Object
  Dim CompilerMsg As String
  Dim CompilerError As Integer
  Dim CompilerCmd As String

  Dim l_result As Integer

' Pass-through sanScript code to be compiled by the

  CompilerCmd = ""
  CompilerCmd = CompilerCmd & "call Note_Button,"
  CompilerCmd = CompilerCmd & """" & IN_Item_Number & ""","
  CompilerCmd = CompilerCmd & " """", {form display name}"
  CompilerCmd = CompilerCmd & " true, {record note button}"
  CompilerCmd = CompilerCmd & " 20010, {An item number must be entered before you can add a note.}"
  CompilerCmd = CompilerCmd & Str(IN_Note_Index) & ","
  CompilerCmd = CompilerCmd & " 'Item Number' of window IV_Item_Inquiry of form IV_Item_Inquiry, {field with the record note}"
  CompilerCmd = CompilerCmd & " table IV_Item_MSTR, {file with the record note}"
  CompilerCmd = CompilerCmd & " '(L) Item Note Show Hide' of window IV_Item_Inquiry of form IV_Item_Inquiry;"

  Set CompilerApp = CreateObject("Dynamics.Application")
  CompilerApp.CurrentProductID = 0 ' DYNAMICS
  CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
  CompilerError = CompilerApp.ExecuteSanscript(CompilerCmd, CompilerMsg)

  If CompilerError <> 0 Then
    l_result = MsgBox(CompilerMsg, vbOKOnly, "Dexterity Compiler")
  End If
End Sub

WARNING: This customization uses a method of executing Dexterity SanScript code from VBA which is unsupported by Microsoft.

10) Compile and save the code. Close the Visual Basic Editor. Return to GP and test by opening the inquiry window, selecting an item with the lookup, using the browse buttons to go through the items, and finally, adding a note to an item.

Techniques Used

The above implementation uses the Dynamics Continuum Integration Library to execute pass-through sanScript in the context of the modified form. In addition, it uses calls to standard Microsoft Dynamics GP global procedures designed to manage record notes. An important aspect for this customization is portability. To avoid using referenced objects, I implemented calls to the Continuum library by instantiating the object with the CreateObject method, rather than the standard VBA "Dim New As Object".

Downloads

You can download the package file for the above code with the links below:

Click here for Dynamics GP v10

Click here for Dynamics GP v9

Click here for Dynamics GP v8

VBA can prove an invaluable tool when implementing customizations and can help reduce the the maintenance headaches involved with workarounds.

Please drop a note describing your experience with this customization or any comments you may have. If there is something you want to see posted on this site please let me know as well.

My thanks go out to David Musgrave for pushing me in researching the techniques and pointing out some of the coding techniques required for this customization. He has more to say about the implementation of this customization at:

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/09/11/adding-record-notes-to-a-window-example.aspx

Be sure to check his site for more fun customizations and articles.

Until next post!

MG.-
Mariano Gomez, MVP, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Please note that the customization being provided in this communication cannot be considered to be an official hotfix or service pack for any reported issue, and hence you take all responsibility for its implementation and ongoing support as well as future upgrades. Although it has been unit tested, it is strongly recommended that it also be tested by both you and your Microsoft Dynamics GP partner in a suitable test environments, before being released into a production environment.