My first post over at Dynamics Latam

September 29, 2010

My first post on COM interop and Dexterity is out over at Dynamics Latam! This will be an excellent venue for me to give back to Spanish speaking community Microsoft Dynamics GP in North, Central, and South America and will sure up other venues to spread the GP gospel.

Until next post!

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

Advertisements

VBA – Suppressing CTRL+Break or CTRL+C in VBA Customizations

March 8, 2010

VBA is by far one of the best customization tools available to Microsoft Dynamics GP developers and as such it is widely used across a number of production environments to deliver functionality that ranges in various degrees of complexity.

However, one of the issues with VBA — at least until now — is the fact that an end user can inadvertly press CTRL+Break (older keyboards) or CTRL+C and stop the execution of a script. Now imagine if that script is say, some code developed to calculate 401K contributions when payroll is ran… the results will certainly not be pretty!

With this in mind, we can use a USER32.DLL library funcion to disable user input when critical VBA code is required to be executed in a block. The following shows how to implement such code:

BlockInput function


Private Declare Function BlockInput Lib "user32" (ByVal fBlock As Long) As
Long

We can now use this function across any of our VBA customizations, as follows:

Sample Window_BeforeOpen code


Private Sub Window_BeforeOpen(OpenVisible As Boolean)
BlockInput True
' All the code you need to run here
...
' MAKE SURE TO RE-ENABLE THE INPUT
BlockInput False
End Sub

There are several applications for this code, but whatever you do, don’t forget to re-enable the user input, otherwise you will end up with one dead mouse and keyboard!

Until next post!

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


Building a COM Interop Assembly to use with Microsoft Dexterity

January 10, 2010

I am currently building some customizations for a customer of mine in the aerospace industry. My customer required a library of trigonometric functions that could be used to extend their Dexterity integrating applications.

To solve this problem, we turned to .NET to create COM interop assembly. The idea was to take advantage of the standard Math class methods available with the System namespace – System.Math . The following is an excerpt of the code we created:

TrigonometricFunctions.cs

//Created by Mariano Gomez, MVP
using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;

namespace TrigonometricFunctions
{
    public class TrigonometricFunctions
    {
        [GuidAttribute("8268A95E-6FCB-4FB2-88A1-1E38F49F4FB8"), ClassInterface(ClassInterfaceType.AutoDual)]
        public class TrigFn
        {
            // dx in degrees
            public double fSin(double dx)
            {
                double angle = Math.PI * dx / 180.0;
                // returns sin(dx)
                return Math.Sin(angle);
            }

            // dx in degrees
            public double fCos(double dx)
            {
                double angle = Math.PI * dx / 180.0;
                // returns cos(dx)
                return Math.Cos(angle);
            }

            // dx in degrees
            public double fTan(double dx)
            {
                double angle = Math.PI * dx / 180.0;
                // returns tan(dx)
                return Math.Tan(angle);
            }

        }
    }
}

Once the functions in the TrigFn class were in place, we set up the assembly information in Visual Studio’s marking the option to Make assembly COM visible.


So we did not have to register the assembly manually, we took advantage of Visual Studio’s ability to register the assembly for COM interop under the Build settings. For purposes of demostration, I created a simple Dexterity form, as shown below:

The following is the code added to the ‘(L) Sine’ button:

Sine button CHG script

{ script: l_Sine_CHG }

local TrigonometricFunctions oTrig;
local currency angle, sine;

oTrig = new TrigonometricFunctions.TrigFn();

'(L) Prompt' = "The Sine value is ";
'(L) Conversion' = oTrig.fSin('(L) Angle');

In the code above, TrigonometricFunctions is a data type created after adding the TrigonometricFunctions COM interop assembly as a library to our Dexterity application. The data type references the TrigonometricFunctions.TrigFn class.

References:

  • Using a .NET Assembly from a Dexterity-Based Application – Click here
  • Microsoft Dynamics GP 10.0 White Paper: Using a .NET Assembly from a Dexterity-based Application – Click here
  • Downloads:

    TrigonometricFunctions .NET project – Click here
    Dexterity Sample App – Click here

    Until next post!

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


    Inside multi-platform integrations with Integration Manager

    October 1, 2009

    I have been working on a very challenging systems conversion project, from JD Edwards on AS/400 and DB2 to Microsoft Dynamics GP v9. Part of the challenge stems from the fact that certain processes, mainly in-house applications developed for the AS/400 will continue to support existing business processes pass the “go-live” date.

    I am tasked with developing multiplatform integrations to support application processes in place and by doing so, I’ve had to dust off my DB2 – AS/400 skills. Per the client’s request, the integrations need to be easy to use, extremely flexible and adaptable, and low maintenance (as in little to no programming required). Many of you may think that due to the nature of the systems involved, this could very well be an eConnect implementation. However, Integration Manager was chosen because of the characteristics previously described.

    As I work through implementing the first integration to manage expense reimbursements from Lotus Notes, my integration needs to read data from some interface tables in a DB2 catalog. As such, we deployed the IBM Client Access ODBC driver. Working through the configuration was fairly simple, but due to the driver features I was afraid Integration Manager was not going to be able to store the DSN configuration.

    One thing I discovered while working with IM v9 SP4 (9.00.0054) is that due to it’s underlaying implementation of the almost outdated use of ADO technology, it cannot perform a preview of the source queries, with the following error message:

    Upon further research, it seems this error is generated because server-side cursors are not supported with ISAM (Indexed Sequential Access Method) files. As it turns out, ISAM was originally developed by IBM and later replaced with a storage file system called methodology called VSAM (Virtual Storage Access Method). VSAM is the physical access method used in DB2.
    What does this have to do with IM? Well, IM — version 9 specifically — uses ADO to connect and retrieve data. The problem with ADO and VSAM file systems is that the cursor location needs to be set to AdUseClient and the cursor type needs to be checked for bookmark support:

    oRecordSet.Supports(adBookmark)

    Apparently, this is not being done in IM v9’s internal ADO implementation. The good news is, I checked this against IM v10 and I have to say it works just fine, since, IM v10 was developed from the ground up with ADO.NET.

    Given there was no preview available, testing began with a small dataset.

    Integration Manager began importing expense vouchers and in the process neglected randomly to import the distributions. I executed the same integration a few times and obtained random results: transactions that previously imported fine along with their distributions did not import and failed the second time around. I verified the RecordSource Rule and Source properties to make sure they were set to Default Non-Imported and the source query for the distributions, respectively.


    Upon adding some VBScript to write the records that were being read and attempted during the integration, I noticed that the header records were being read in order while the distribution records were shifting randomly. In some cases IM would match the header record with the distribution record, but in some cases it would skip a valid set of distributions for an expense voucher. By changing the UseOptimizedFiltering flag in IM to False the imports began to work as expected.

    Integration Manager is a very useful tool and should not be overlooked when considering multiplatform systems integrations, especially if the customer insists on easy of use. All the integrations will be scheduled on a server and will run unattended, which only validates IM’s flexibility.

    Until next post!

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


    VBA – Creating a keyboard shortcut for a Microsoft Dynamics GP window expansion button

    August 17, 2009

    Well, I had been absent (more like out of ideas) from writing any new VBA articles. The truth is, they are hard to come by without giving away too many secrets. 🙂

    However, this one is worth giving away as I have seen many users request this feature to speed up data entry and processing. Take for example the Purchase Order Entry window. Won’t you just like the ability to press some combination of keys on your keyboard to quickly access the Vendor Detail Entry window where you can quickly change around shipping methods, or other information needed? Well this example shows just that, but can be used to add shortcuts to any other button control in any other Dynamics GP window.

    The solution is simple:

    1) Add a pixel size button to the window with Modifier.

    2) Caption the button. Buttons can have captions preceeded with the ampersand (“&”) character, which in turn acts like a shortcut when used in combination with the Alt key on the keyboard.

    As a personal choice, I captioned the button with &4, as the expansion button is Expansion Button 4.

    3) In addition, we can change the button’s Visual properties to blend it in with the expansion button. I particularly prefer to stack these shortcut buttons on top of or next to the expansion button they will serve as a shortcut for.

    4) The button can then be added to VBA where code can be written to force the execution the expansion button Change script. To run the Expansion Button 4 change script that will open the window, we use the Dynamics Continuum Integration library and pass-through Dexterity Sanscript.


    Private Sub 4_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
    Dim CompilerApp As Object
    Dim CompilerMessage As String
    Dim CompilerError As Integer
    Dim Commands As String

    ' Create link without having reference marked
    Set CompilerApp = CreateObject("Dynamics.Application")
    Commands = ""
    Commands = Commands & "run script 'Expansion Button 4' of window POP_PO_Entry of form POP_PO_Entry;"

    ' Execute SanScript
    CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
    If CompilerError 0 Then
    MsgBox CompilerMessage
    End If
    End Sub

    The following — and much simpler! — code will also do the trick for you non-Dexterity developers:


    Private Sub 4_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
    ExpansionButton4.Value = 1
    End Sub

    You will need to add the expansion button to VBA in addition to the pixel size button for the above code to work.

    Very simple! Now when you go to the Purchase Order Entry screen, enter PO number and select a vendor, you can press Alt + 4 on the keyboard to open the associated window to the Vendor ID expansion button.

    Hope you find this sample project useful.

    Downloads

    v10 – Sample Expansion Button shortcut package file – click here.

    Until next post!

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


    Retrieving Windows Registry key values with Microsoft Dexterity

    June 12, 2009

    Every so often you get these development requests that seem to push Dexterity to its limits. One of such requests is being able to read a Windows Registry key value using nothing more than SanScript.

    The following example will show how to use the Microsoft Windows Management Instrumentation (WMI) Scripting library, ADVAPI32.DLL to retrieve a Windows Registry key value by examining how to retrieve the default Internet browser software being used.

    The code will use the RegOpenKeyA and RegQueryValueExA DLL functions to a) return a handle for the registry path where we can found the key, b) then retrieve the actual key value. In order to access external DLL functions, it is necessary to create Dexterity prototype global procedures for the external DLL functions.

    RegOpenKeyA@ADVAPI.DLL


    { prototype procedure RegOpenKeyA@ADVAPI.DLL }
    out long return_value; {function returns ERROR_SUCCESS }
    in long hKey; {Handle of parent key to open the new key under}
    in string lpcstr; {Name of the key under hkey to open }
    inout long phkey; {Destination for the resulting Handle }

    RegQueryValueExA@ADVAPI32.DLL


    { prototype procedure RegQueryValueExA@ADVAPI32.DLL }
    out long return_value; {function returns ERROR_SUCCESS }
    in long hKey; {handle of the key to query }
    in string sName; {Name of value under hkey to query }
    in long lReserved; {Reserved, must be null }
    inout long lType; {Destination for the value type, or NULL if not}
    {required. }
    inout string sKeyValue; {Destination for the values contents, or NULL }
    {if not required. }
    inout long lResultLen; {Size of sKeyValue, updated with the number of }
    {bytes returned. }

    Now that we have the prototype functions, we can proceed to use the Constant Definition window in Dexterity to define values for each Registry hive.

    Registry Hive Constants


    Constant Name Constant Value
    HKEY_CLASSES_ROOT 2147483648
    HKEY_CURRENT_USER 2147483649
    HKEY_LOCAL_MACHINE 2147483650
    HKEY_USERS 2147483651
    HKEY_PERFORMANCE_DATA 2147483652
    HKEY_CURRENT_CONFIG 2147483653

    NOTE: These constants are usually known by their hexadecimal values, but Dexterity does treats hexadecimal constants as strings, hence the decimal notation used.

    Once the prototype functions have been defined, we can wrap these in an API that isolates the developer from dealing with the innerworks of the calls. We will define to global functions as follow:

    RegKeyExists


    { global function RegKeyExists }
    function returns long phkey;

    in long hkey;
    in string lpcstr;

    local long return_value;

    try
    extern 'RegOpenKeyA@ADVAPI32.dll'
    , return_value
    , hkey
    , lpcstr
    , phkey;
    catch[EXCEPTION_CLASS_OBJECT_EXCEPTION]
    error "Error calling RegOpenKeyA@ADVAPI32.DLL. Could not locate DLL pointer.";
    else
    throw;
    end try;

    RegKeyExists accepts the registry hive parameter (hkey) and the registry path and returns a handler if the path is valid (phkey).

    RegGetKeyValue


    { global function RegGetKeyValue }
    function returns string key_value;
    in long hKey;

    local long return_value, lValueType, lValueLength;
    local string sKeyValue;

    set lValueLength to 255;

    try
    extern 'RegQueryValueExA@ADVAPI32.DLL'
    , return_value
    , hKey
    , ""
    , 0
    , lValueType
    , sKeyValue
    , lValueLength;
    catch[EXCEPTION_CLASS_OBJECT_EXCEPTION]
    error "Error calling RegQueryValueExA@ADVAPI32.DLL. Could not locate DLL pointer.";
    else
    throw;
    end try;

    if return_value = OKAY then
    key_value = sKeyValue;
    else
    key_value = "KEY_ERROR";
    end if;

    RegGetKeyValue in turn will take the handler (returned by RegKeyExists) and attempt to retrieve a value for the Default entry of the path previously given. If the function succeeds, it will return a string with the actual value, else the user will get a KEY_ERROR message.

    Making it all work together..

    For this project, I have created the following form:

    The form contains 4 local variables:

    ‘(L) RegistryHive’: is a drop-down list with the following string values corresponding to each registry hive, as follows: HKEY_CLASSES_ROOT, HKEY_CURRENT_USER, HKEY_LOCAL_MACHINE, HKEY_USERS, HKEY_PERFORMANCE_DATA, and HKEY_CURRENT_CONFIG.

    ‘(L) RegistryPath’: is a string of 255 characters in length.

    ‘(L) RegistryKey’: is a string of 50 characters in length, not used in this project. I will leave this for a future post.

    ‘(L) RegKeyValue’: is a string of 255 characters in length. Set the property to Editable to False.

    Now that you have the strings and drop-down list, drag the ‘OK Button’ push button control to finish — I am working in the Dynamics dictionary, DYNAMICS.DIC.

    The actual production form will look like this:

    We can now add the following Sanscript code to the OK Button change script:

    syGetRegistryKey OK Button K_CHG


    local long hKey; { [In] Handle to an open key. }
    local long lHive;

    local long return_value, nSubkeys, nSubkeyMaxSize,nMaxChars, nValues, lValueName, lValueData, lClassLen;
    local reference ft;
    local string lClass;

    { check for the last backslash character on the path string }
    if substring('(L) RegistryPath', length('(L) RegistryPath'), 1) CH_BACKSLASH then
    '(L) RegistryPath' = '(L) RegistryPath' + CH_BACKSLASH;
    end if;

    { check the hive selected from the DDL and assign the proper constant }
    case '(L) RegistryHive'
    in [1] lHive = HKEY_CLASSES_ROOT;
    in [2] lHive = HKEY_CURRENT_USER;
    in [3] lHive = HKEY_LOCAL_MACHINE;
    in [4] lHive = HKEY_USERS;
    in [5] lHive = HKEY_PERFORMANCE_DATA;
    in [6] lHive = HKEY_CURRENT_CONFIG;
    end case;

    { establish if the key entered is valid within the hive }
    hKey = RegKeyExists(lHive, '(L) RegistryPath');

    { RegKeyExists will return 0 if the path does not exist within the hive,
    else it will return the handle value for the key
    }

    lClass = "";
    lClassLen = 0;

    if hKey 0 then
    { Get the key value; will append the key to the path to make it whole }
    '(L) RegKeyValue' = trim(RegGetKeyValue(hKey));
    else
    warning "Invalid registry key entered";
    end if;

    You can compile and run the code in test mode and use the Developer Assistant form to open the newly created form. For example, let’s check the registry for the default Internet browser running:

    I will be taking a shot a two other functions in the Microsoft WMI Scripting library in a futute installment. Hope you enjoy this article and can’t wait to hear your comments.

    Acknowledgements

    A big thank you to Jon Eastman for his insight on the ADVAPI32.DLL. He gave me the idea for this post… that’s what it’s all about!

    Until next post!

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


    What is the Dynamic User Object Store (DUOS)?

    March 24, 2009

    I have written a number of articles on my blog showcasing storing data in the DUOS, you may have read a number of postings on Developing for Dynamics GP on the subject, but many of you have asked what exactly is the DUOS and how to store and retrieve the data in the DUOS.

    For all its fancy name, simply put the DUOS is a SQL Server table – dbo.SY90000 – a part of your company database. However, there is more to it than meet the eyes. But first, lets take a look at the table definition:


    /****** Object: Table [dbo].[SY90000] Script Date: 03/23/2009 14:48:44 ******/

    SET ANSI_NULLS OFF
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING OFF
    GO

    CREATE TABLE [dbo].[SY90000](
    [ObjectType] [char](31) NOT NULL,
    [ObjectID] [char](61) NOT NULL,
    [PropertyName] [char](31) NOT NULL,
    [PropertyValue] [char](133) NOT NULL,
    [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
    CONSTRAINT [PKSY90000] PRIMARY KEY NONCLUSTERED
    (
    [ObjectType] ASC,
    [ObjectID] ASC,
    [PropertyName] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    SET ANSI_PADDING OFF
    GO

    The important aspect to highlight from this table definition is the primary key constraint as, before hand, you will not be able to import records with a duplicate Object Type, and Object ID, and PropertyName.

    When do I use DUOS?

    To understand when to use DUOS, I will first refer to the methods of accessing and storing data in the DUOS itself.

    There are two methods for programming DUOS:

    1) Using Dexterity
    2) Using Modifier with Visual Basic for Applications.

    While Dexterity offers a good support for accessing DUOS (after all, it’s only another Dexterity table) with its standard get, save table, change, and range statements, DUOS was really implemented to be used in conjuction with Modifier and Visual Basic for Applications.

    DUOS is primarily designed to support minor customizations (with Modifier and VBA) requiring limited data storage, this is, a few extra fields added here and there. All extra fields and their values are stored as strings in the physical DUOS table, hence the performance overhead that large data sets can bring as their values will require conversion to match proper datatypes.

    NOTE: If you have a need to store large data sets it is recommended you explore other methods like independent SQL Server tables with their own data definitions. These tables can be created and accessed with Dexterity, or created in SQL Server and accessed with ADO.

    The DUOS object model

    The DUOS object model is a standard part of Microsoft Dynamics GP Visual Basic for Applications object library – the library resides in the DEXVBA.DLL assembly file and exposed to COM via an OLE type library file, DEXVBA.TLB.

    The library exposes the DUOSObjects and DUOSProperties collections and two objects, the DUOSObject and DUOSProperty. The following image illustrates the object model with its methods and properties.


    DUOS Resources

    Now that you have an understanding of the object model and how/when to utilize DUOS take a look at some of the coolest samples around the blogosphere.

    DUOS examples – David Musgrave at Developing for Dynamics GP. Click here.
    VBA Workshop series – Click here.

    Until next post!

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