Automatically close Dynamics GP report destination windows in VS Tools

By Steve Endow
 
  I am working on a project that involves automating a process in Dynamics GP.  Since there is no API for the particular steps I need to automate, I'm having to use the low-tech method of opening a GP window, populating some field values, and clicking a few buttons.

This is fairly easy to do with Visual Studio Tools for Dynamics GP...in concept.

But, unsurprisingly, Dynamics GP seems to always have to have the last word, and such seemingly simple projects are rarely easy.

In my particular case, after I performed the last button click on the GP window, Report Destination windows would appear to print posting reports.

The process I am automating generates not one, or two, or four, but FIVE report destination windows.  Yes, it is possible to turn off the reports so that the window doesn't appear, but that change would affect many other processes throughout GP where the client might want to actually print some of the posting reports.  It is possible to temporarily update the posting report settings in SQL to disable them, and then quickly re-enable them, but that method runs the risk of disabling at the same time that another user needs the report.

Unfortunately, the Report Destination dialog boxes are not standard modal dialogs that would be detected by the BeforeModalDialog event handler in VS Tools.  So, some "off roading" is required.

When I ran into this problem I was lucky enough to find this excellent post by Paul Maxan on the Code Project web site.

http://www.codeproject.com/Articles/801319/Closing-Microsoft-Dynamics-GP-Report-Destination-w

Paul wrote a great article and did a very nice job crafting code that uses the Windows API to detect the Report Destination dialog box and use windows handles and messages to click through the dialogs.

He posted some very nice code that I packaged up into a separate class that can be easily invoked just about anywhere in your VS Tools project.

During my testing on GP 2013, I found that his combination of TAB + ESC didn't work for some reason.  After some fiddling, I found that ESC by itself did work, so I have used that in my GP 2013 project.

The Report Destination dialog boxes still flash on screen as they are dispatched by Paul's "Closer" code, but it seems to work well and definitely gets the job done.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Bug in Dynamics GP VS Tools Event Handlers for DexUIForm Windows

  By Steve Endow

A few days ago I wrote about an odd behavior in VS Tools where an event handler wouldn't fire if a VS Tools window initiated an event on a GP window.

Specifically, on the GP 2013 Void Historical Payables Transactions window, when you click on the Mark All button, two different dialog boxes appear. (the dialogs are not present on GP 2010)

I needed to suppress those two dialogs.  I wrote an AddIn with an event handler for the BeforeModalDialog event, which worked perfectly when I clicked the Mark All button in GP.

pmVoidPaymentsWindow.BeforeModalDialog += new EventHandler(PmVoidPayments_BeforeModalDialog);

But if I had a VS Tools window "click" that button, the event handler did not fire.  At all.

I was stumped.  After hours of testing and trying various scenarios, I figured it was a bug, and I would have to develop a workaround.  I tried 2 or 3 different workarounds--all of which worked fine in GP, but none of which worked when a VS Tools window clicked Mark All.

For instance, I added VBA script to the window to see if VBA could take care of the dialogs.

Private Sub Window_BeforeModalDialog(ByVal DlgType As DialogType, PromptString As String, Control1String As String, Control2String As String, Control3String As String, Answer As DialogCtrl)

    If Left(PromptString, 61) = "Your selection may include payments that have been reconciled" Then
        Answer = dcButton2
    ElseIf Left(PromptString, 69) = "Your selection may include credit card payments with related invoices" Then
        Answer = dcButton2
    End If
    
End Sub

Once again, this worked fine if I manually clicked the button in GP, but when my VS Tools window clicked the button, the event handler would not work and the dialogs would appear.

I briefly considered trying Continuum, but I couldn't easily figure out what script to use to click through the dialogs.  I momentarily thought of using a macro, but didn't want to go there.

While desperately searching for a solution, I begged for help from Andrew Dean and Tim Wappat, both of whom are expert .NET and GP developers.  They both confirmed the issue and were puzzled.  They came up with a few workarounds to try, but nothing seemed to work.

I then finally resorted to using the Windows API to detect the dialog boxes and click through them, completely outside of the GP dev tools and VS Tools.  After a day of research and testing, I finally got that to work.  It was a kludge, but it did work, literally clicking the Continue button of the dialog boxes as they flashed up on screen.

And then today, Andrew created a VS Tools test app that was able to get the BeforeModalDialog event to fire properly.  Before I had a chance to review it, Tim Wappat had reviewed the code and found the reason:  Andrew had used a standard Windows Form rather than the VS Tools DexUIForm for his test window.

Sure enough, when I modified my test window to inherit from the Form class rather than the DexUIForm class, the BeforeModalDialog event handler worked properly when my window clicked the Mark All button.

public partial class FrmVoidTest : Form  //DexUIForm

So there it was--an apparent bug in the DexUIForm class caused the event handler for the Void Historical Payables window to not fire.  And strangely, it only seems to occur with some windows--for instance, the problem does not occur with the SOP Transaction Entry window.  So there is something about the Void Historical Payables Transactions window that triggers the bug.

Unbelievable.  It took me 3 full days to try several different workarounds and it turns out that changing one word on line line of code was the solution.  It's problems like this that make me frustrated with Dynamics GP.  Incredible waste of time for such a seemingly small problem, yet that problem is critical, and prevents me from completing my project.

The only good thing is that I learned quite a bit about the plumbing of VS Tools and Dynamics GP windows, and I am now also familiar with the Win API code to detect, analyze and automate actions on Windows applications.  But it was a painful journey.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Installing missing Professional Services Tools Library stored procedures

By Steve Endow

One of my clients was trying to run the Professional Services Tools Library (PSTL) Vendor Modifier tool on GP 2010.  When they tested it in their test database, they received this message:

"Could not find stored procedure smVendorChange1"

We checked the database, and the stored procedure did not exist.  As a test, I created the SQL script for that procedure from my server and we ran it on their database--we received messages indicating that there were other dependencies, so it seems that none of the PSTL procedures were present in this test database.

After doing some reading, I found that PSTL should automatically create its stored procedures.  But it clearly hadn't done so with this database.

We then tried using the SQL Maintenance window to recreate the procedures.

Unfortunately, that didn't appear to do anything.  None of the missing procedures were created.

We then logged into a different company database and opened the PSTL window.  PSTL immediately launched its status window and created the stored procedures.  Hmmm.

Puzzled, we logged back into our test database and launched the PSTL window.  It didn't create the procedures automatically.  Out of curiosity, we clicked on the Register button.  The default registration code was displayed, so that didn't seem to be the issue.

But when we clicked on the OK button for the Registration code window, a status window displayed and PSTL started to install its stored procedures!

I don't know why it didn't automatically install them when it launched, but if you run into a situation where the stored procedures are missing or need to be reinstalled, try clicking on Register, then OK, and it should trigger the reinstall process.

With GP 2013 (or 2013 R2?), the PSTL tools are included and do not require a separate install, so I'm assuming this issue is only relevant for GP 2010.


Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Server field is blank when you launch Dynamics GP due to space at beginning of DSN

By Steve Endow

I just had a call with a client whose Dynamics GP Server field was always blank when they launched GP.

The drop down list had entries, and the user could log into GP, but every time they launched GP, they had to re-select the Server value.

I checked the Dex.ini file and saw that it did have a SQLLastDataSource value.

SQLLastDataSource= Dynamics GP 2013

I noticed that there was a space at the between the equal sign and the name, so I removed the space, saved the Dex.ini and relaunched GP, but that didn't resolve the issue.

I then found this KB article, but it discusses a completely blank Server drop down list--not a Server field value that remains blank and doesn't doesn't display a default value.

But based on that article, I decided to double check the ODBC DSN settings.  We confirmed that the GP DSN was present in the 32-bit ODBC settings window and was using the proper SQL driver, so I was stumped.

On a whim, I decided to confirm the DSN settings, and that's when I saw the likely culprit.

Did you catch that?  See the issue?

Look again:

Notice that there is a sliver of blue before the word Dynamics?

There was a space in front of the DSN name.

The client had manually created their Dynamics GP DSNs, and in the process had accidentally typed a space at the beginning of the name.

We removed the space from the beginning of the DSN name, relaunched GP, and the Server value defaulted just fine.

Apparently Dynamics GP can't handle a DSN that starts with a space.  The DSN will work, but it will never default in the Server field when you launch Dynamics GP.

Whenever I think I've seen all of the GP oddities, a new one pops up right on queue.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Limitation with Dynamics GP VS Tools Event Handlers?

By Steve Endow

Visual Studio Tools Event Handlers are a great feature that allows you to respond to events and actions in Dynamics GP.  Let's say that you want to validate data after a user selects a value from a GP drop down list, or you want to run some code when the user selects a customer ID in a window.

Or, as in my current project, let's say that you want to suppress or click past some Dynamics GP dialog boxes!

The Void Historical Payables Transaction window was modified in Dynamics GP 2013 to add a Vendor ID filter.  This is a huge improvement for customers that actually need to use this window--you can now filter on Vendor ID and Document Number to quickly find the document to be voided.

But another change made in the Void Historical Payables Window in GP 2013 is that if you use the "Mark All" button, two very annoying dialog boxes appear.

I understand that these dialogs have a purpose, but for my current project of automating a PM void, they are getting in the way.

So, using a handy-dandy VS Tools event handler, I'm able to suppress the dialog boxes.

Here I am registering the event handler:

pmVoidPaymentsWindow.BeforeModalDialog += new EventHandlerBeforeModalDialogEventArgs>(PmVoidPayments_BeforeModalDialog);

And here is the code that will fire when the event occurs:

void PmVoidPayments_BeforeModalDialog(object sender, Microsoft.Dexterity.Bridge.BeforeModalDialogEventArgs e)

{

    if (e.Message.StartsWith("Your selection may include payments that have been reconciled"))

    {

        e.Response = Microsoft.Dexterity.Bridge.DialogResponse.Button2;

    }

    else if (e.Message.StartsWith("Your selection may include credit card payments with related invoices"))

    {

        e.Response = Microsoft.Dexterity.Bridge.DialogResponse.Button2;

    }

}

This Event Handler code works great.  If I click on the Mark All button on the GP window, I don't even see the dialog boxes--they are suppressed by the VS Tools code.  And if I have some code in the GpAddIn.cs class that clicks the Mark All button programmatically, it also works.

But.......if I have code outside of the GpAddIn.cs file that clicks the Mark All button, the event handler doesn't fire.

In the screen shot above, I have a separate VS Tools form (FrmVoidTest) with a Mark All button.

The button simply calls pmVoidPaymentsWindow.MarkAll.RunValidate(), just like my test code in GpAddIn.cs.  But for some reason, when that method is called from the .NET form, the BeforeModalDialog event handler does not fire, and the two dialog boxes are displayed.

So based on my testing, there appears to be some limitation of VS Tools that requires any code that interacts with the GP form be called from within the GpAddIn.cs file in order to trigger event handlers.  Even if my test form calls the exact same method in the GpAddIn.cs file that clicks the Mark All button, the event handler is not triggered.

This seems really odd, but I've tried 3 or 4 different approaches and the event handler code just won't fire if I click the Mark All button outside of GpAddIn.cs.  I'm still researching it and asking a colleague to review my code to see if I'm missing something.

If anyone has any ideas or clever suggestions, I'm all ears!

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Apparent bug in eConnect taSopSerial stored procedure for invoice with negative quantities

By Steve Endow

This is an obscure one, but I thought I would document it for the Google-verse.

I developed an eConnect SOP Invoice import for a GP 2010 customer that sells and services lots of serialized items.  When they visit a customer, they may find that an item needs service, but the customer needs a loaner item in the meantime.

To record this transaction, they will create an invoice with the loaner item with quantity 1, and then have a second line for the serialized item being brought back to the shop for repair with a quantity of -1.  It is a little unusual, but if you have an invoice with a serialized item with quantity -1, it allows you to bring that serialized item into inventory.  

This process works well for invoices entered directly into Dynamics GP.  But if you try and import an invoice with a serialized item with a negative quantity, you'll get this error.

"taSopSerial Error - Serial Number does not exist in Item Serial Number Master -IV00200"

Notice that the Quantity node is -1.  The customer is trying to receive the serial number into inventory, but eConnect isn't allowing it.

If you review the SQL script for the eConnect taSopSerial stored procedure, you'll see this tidbit somewhere around line 215-230, depending on the formatting.

      if ( @I_vSOPTYPE = 3 

         and @I_vQUANTITY = -1 ) 

      begin 

          select @I_vQUANTITY = 1 

      end 

For some reason, the taSopSerial procedure is converting quantities of -1 to 1 for invoices.  The eConnect procedures don't include any comments, so it isn't clear why this is occurring in the 1800+ line procedure.

A few lines later in the procedure, there is validation for quantities of -1 and 1, so this would seem to indicate that the above lines aren't valid.  This code is explicitly acknowledging that an invoice item can have a quantity of -1.

        if ( @I_vQUANTITY not in ( -1, 1 ) ) 

        or ( @I_vQUANTITY = -1 

             and @I_vSOPTYPE <> 3 ) 

And only changing the value of lines with quantity of -1 seems pretty odd (why not all negative quantities?), and it just happens to affect my customer's import of invoices with serialized items that have a -1 quantity.

Since the GP client allows the entry of -1 for serialized items on an invoice, it isn't clear why eConnect would not, and whether this is a bug, or if there is a specific reason why eConnect would not allow a -1 line item quantity.

To work around this, we commented out those 5 lines, and that resolved the issue.  The invoices with -1 quantities for the serialized items import just fine, and after using the modified taSopSerial procedure for over a year, the client hasn't had any issues.

Except when they update or upgrade GP.  They recently applied a service pack for the 2014 year end updates, and that update apparently dropped and recreated the taSopSerial procedure.  So whenever they do an update, we have to remember why the error is occurring again, and then modify the new taSopSerial procedure.

I verified that this "problem" code also exists in GP 2013.  I have not yet checked GP 2015.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Update Dynamics GP Notes from an eConnect Integration

By Steve Endow

I would have thought that one of the many eConnect integrations I have developed over the years would have required me to update an existing Dynamics GP Note record.  Perhaps I have done it before, but when this seemingly simple requirement came up recently, I couldn't remember having done it before, and Google didn't seem to turn up any results.

If there is an easier way to do this, please post a comment below, as I'm interested in knowing if I missed something obvious.

Let's say that  your .NET eConnect integration needs to update the address and phone number of an existing customer.  No problem.  But when you update the customer contact information, you also need to append an update message to the customer's Note field.  Okay, makes sense.

But if you try and set the eConnect taUpdateCreateCustomerRcd NOTETEXT field, that new value will wipe out any existing customer Note.  After poking around the documentation and searching for options to update an existing GP note programmatically, I didn't see any options.  I searched through the standard zDP stored procedures, as well as the eConnect "ta" stored procedures, but didn't see any options there either to update Notes.  While the SY03900 note table is pretty simple, it would be nice to have a simpler option for updating notes.

You could wrap all of this up into a single SQL stored procedure.  I didn't do that because I didn't want to deploy yet another custom stored procedure for this particular client.

You could also package this up as an eConnect Post procedure, but I find those to be a bit of a hassle because it's just like having a custom stored proc, which I didn't want, and the eConnect Post stored procedures are wiped out by most GP service packs and upgrades, so it is very easy to forget to recreate them after an update.

Anywho...

First, you need to make sure that the customer has a record in the SY03900 table.  Even though new customer records are automatically assigned a NOTEINDX value, by default, a record is not automatically created in SY03900.  So if you update customer ACME001 and attempt to update its note record in SY03900, your update will fail if the record doesn't yet exist.

I quickly pulled together this SQL to check for a Note record and create one if one didn't already exist.

IF NOT EXISTS (SELECT TOP 1 NOTEINDX FROM SY03900 WHERE NOTEINDX = (SELECT NOTEINDX FROM RM00101 WHERE CUSTNMBR = 'ACME001'))

BEGIN

INSERT INTO SY03900 (NOTEINDX, DATE1, TIME1, TXTFIELD)

VALUES  ((SELECT NOTEINDX FROM RM00101 WHERE CUSTNMBR = 'ACME001'), CONVERT(VARCHAR(10), GETDATE(), 101), CONVERT(VARCHAR(12), GETDATE(), 108), '')

END

I then borrowed and repurposed some nice SQL from the very smart Tim Wappat, who has posted a nice script for fixing line breaks in GP note records.

UPDATE SY03900 SET TXTFIELD = CAST(TXTFIELD AS varchar(MAX)) + CHAR(13) + 'Text to append'

WHERE NOTEINDX = (SELECT NOTEINDX FROM RM00101 WHERE CUSTNMBR = 'ACME001')

This update script adds a new line to the note and appends the new text to the bottom of the note.

With this script setup, after you use eConnect to update the existing customer contact info in GP, you have to have a separate method to perform the Note update.

Obviously this sample is only for updating customer notes, but you could repurpose for vendor and other notes, and if you got really fancy, you could probably abstract it to update notes for any record type.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com