Friday, September 12, 2014

Bug in GP 2013 VS Tools Window Positioning

By Steve Endow

This week I finished up a custom Dynamics GP 2013 eConnect integration that I developed as a Visual Studio Tools AddIn.  The customer wanted to have a GP menu item that would open the integration window, so I used the Menus for Visual Studio Tools library to add a custom menu item.

The integration worked great and I packaged it up for deployment at the client.

Except as I was doing some final testing and double checking, I noticed a strange behavior with the window.  Sometimes when I opened the custom VST window, it would open "CenterScreen", which is what I wanted.  But other times, it would open in the upper left corner of my screen.

I tested it a few more times, and it seemed like the first time it opened after launching GP, it would open centered, but then subsequently it would open in the upper left corner of my screen.  I tried various settings to try and position the window in the center of the screen, like manually setting the window Location property.  But after several more tests, it would ALWAYS open in the upper left corner.  It was ignoring my manual Location setting in the code.

It drives me nuts to have windows open in random positions, and I can't stand windows that open in the upper left corner for no reason.

Baffled, I created a brand new VS Tools project with a test window.  When I opened that window from GP, it worked fine.  It was always centered.  What was going on??

I then compared every single window property in my integration project against the test VST project.  All of the window properties were the same.  Yet they behaved differently.

And then I finally noticed the difference.  In my test project, where the window would dutifully open CenterScreen every time, I saw that the icon in the upper left corner of the window was different.  It didn't have the GP window icon.  This meant that it was a standard Windows Form and not a  Dynamics GP VST Form.  Hmmm.

I then added a second window to the test project--this time a Dynamics GP Form.  I set all of the window properties exactly the same as the Windows Form.  I then launched the windows.  Sure enough, the Dynamics GP Form opened in the upper left corner of my screen, while the Windows Form opened Center Screen.



This screen shot shows how the standard Windows Form opens CenterScreen, while the Dynamics GP Form opens in the upper left corner of the screen.

And if that isn't the cutest photo ever featured in a Dynamics GP blog post, I don't know what is...

It seems that once you modify the window Startup Position or Location settings on the Dynamics GP Form, it triggers some bug that prevents the window from positioning properly, ignoring the window properties.  I tried changing the settings to different values, but none would reliably reproduce the CenterScreen equivalent.

So after dozens of tests, here is what I have found.

If you have StartPosition = CenterScreen, and if the Location value is 0, 0, the VS Tools window will always open in the upper left corner.  It may position properly the first few times, but if you change the StartPosition setting or Location setting, it seems to consistently revert to the upper left.

If you add code to set the form Location property during the OnLoad event, the window will open center screen the first time, but then subsequent opens will reposition it to the upper left.

But, if you then change the Location to some value other than zero, such as 100, 100, then setting the Location property will work every time.

However, there appears to be one additional catch.  Based on dozens of more tests, setting the Location only works if it is the first line in your Form Load event.  If you have any other code that refreshes the form, the Location setting will be ignored the second and subsequent time the window is opened.

So to summarize:

1. Set the form Location property to a value other than 0, 0, such as 100, 100

2. Add this as the very first line in your Form Load event:

this.Location = new Point((Screen.PrimaryScreen.Bounds.Size.Width / 2) - (this.Size.Width / 2), (Screen.PrimaryScreen.Bounds.Size.Height / 2) - (this.Size.Height / 2));


After hours of testing, I believe that these two techniques will allow you to work around the VS Tools window positioning bug.  And after another test, it looks like changing the Location property may not be necessary if the Location setting is the first line in Form Load.

I don't recall ever having this issue before, so I'm guessing it was introduced in GP 2013 or GP 2013 R2.  Given how subtle it is, I don't have high hopes that it will be fixed any time soon.


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





Wednesday, September 10, 2014

Importing Dynamics GP Vendors with Temporary Vendor IDs

By Steve Endow

In the last 10 years, I've only had one situation where a client considered temporary vendor IDs.  I remember looking into them, but don't know that the client ever used them.

Last week I was asked to develop an eConnect integration that would import AP Invoices, and every invoice would be issued to a temporary vendor ID.  So before each invoice was imported, I had to create a new vendor record with a temporary ID.

Dynamics GP has the slightly odd, or perhaps anachronistic, feature that allows you to delete temporary vendor records while retaining any transactions related to that temporary vendor.

My initial guess is that the feature may have been motivated by database size concerns of ye olde days.  Today, vendor records are usually a trivial concern relative to database size and server storage capacity, so that isn't a terribly compelling reason to use temporary vendors.

I say the feature is odd primarily because there is no corresponding Temporary Customer.  Why not?  I know of quite a few businesses that have thousands and thousands of one time customers, but relatively few one time vendors.

But in this client's situation, each vendor will always be paid once, for a small amount, and the client has indicated that it is highly unlikely that any of these particular vendors will ever be paid again--if so, it would be very infrequent.  In this case, the vendors are "one time" vendors, so it makes sense to utilize the Temporary vendor feature.

So what makes importing temporary vendors different than normal vendors?  As the title of this post hints, it has to do with temporary vendor IDs.  In the Payables Setup Options window, there is a "Next Temp. Vendor ID" value, that allows you to specify a vendor ID series just for temp vendors.


 This number is utilized when you press CTRL + T on the vendor ID field of the Payables Transaction Entry window, and causes GP to automatically populate the temp vendor ID into the Transaction Entry window and the Vendor Maintenance window.


So that's neat and all, but how would you import temporary vendors?

Well, just like normal vendors, except that you have to grab the next temporary vendor ID.  Which, of course, is something eConnect does not support.  And as far as I can tell, Dynamics GP gets the next vendor ID using code, and not a stored procedure.

So, I had to roll my own.


Below are my C# routines to get the next temporary vendor ID.  A few small details make it tricky.  Once you retrieve the next temp ID, I check to make sure that the ID hasn't been used already.  There is no logic in GP that prevents a Temp ID from being manually entered, and it is possible for the next temp ID to be changed.

Next, you have to increment the ID and store the new "next" value back to the database.  Because the temp vendor ID is an alphanumeric string, you need to retain the prefix and increment the numeric suffix.  And I couldn't assume that the vendor's Temp ID sequence prefix would be the same as my test environment.  While not rocket science, it was a bit tricky to find an elegant way to split the string, increment the numeric suffix, and then re-join the string.  I eventually found a forum post with a nice Regex implementation which worked quite well.



internal string GetNextTempVendorID()
{
    try
    {
        string records = string.Empty;
        int tries = 0;
        bool success = false;

        int digitStartIndex = 0;
        string alpha = string.Empty;
        string digits = string.Empty;
        int digitLength = 0;
        int tempVendorNumber = 0;

        Match regexMatch;

        string sqlCommand = "SELECT RTRIM(NXTVNDID) AS NextVendorID FROM PM40100 WITH (TABLOCKX HOLDLOCK)";
        string tempVendorID = DataAccess.ExecuteScalar(CommandType.Text, sqlCommand, null);

        bool vendorExists = VendorIDExists(tempVendorID);

        while (vendorExists && tries < 50)
        {
            tries++;

            regexMatch = Regex.Match(tempVendorID, "[0-9]");
            if (regexMatch.Success)
            {
                digitStartIndex = regexMatch.Index;
                alpha = tempVendorID.Substring(0, digitStartIndex);
                digits = tempVendorID.Substring(digitStartIndex);
                digitLength = digits.Length;
                tempVendorNumber = Convert.ToInt32(digits);

                tempVendorID = alpha + (tempVendorNumber + 1).ToString().PadLeft(digitLength, '0');

                vendorExists = VendorIDExists(tempVendorID);
            }
        }

        vendorExists = VendorIDExists(tempVendorID);
        if (vendorExists)
        {
            Log.Write("Failed to get next temp vendor ID");
            return string.Empty;
        }
        else
        {
            success = UpdateNextTempVendorID(tempVendorID);
            if (success)
            {
                return tempVendorID.Trim();
            }
            else
            {
                Log.Write("Failed to UpdateNextTempVendorID");
                return string.Empty;
            }
        }

    }
    catch (Exception ex)
    {
        Log.Write("An unexpected error occurred in GetNextTempVendorID: " + ex.Message);
        return string.Empty;
    }

}





private bool VendorIDExists(string vendorID)
{
    try
    {
        string sqlCommand = "SELECT COUNT(*) AS Records FROM PM00200 WITH (NOLOCK) WHERE VENDORID = @VENDORID";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@VENDORID", System.Data.SqlDbType.VarChar, 15);
        sqlParameters[0].Value = vendorID.Trim();

        string records = DataAccess.ExecuteScalar(CommandType.Text, sqlCommand, sqlParameters);
        int vendorCount = Convert.ToInt32(records);

        if (vendorCount > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch (Exception ex)
    {
        Log.Write("An unexpected error occurred in VendorIDExists: " + ex.Message);
        return false;
    }
}




private bool UpdateNextTempVendorID(string lastTempVendorID)
{
    try
    {
        int digitStartIndex = 0;
        string alpha = string.Empty;
        string digits = string.Empty;
        int digitLength = 0;
        int tempVendorNumber = 0;
        string nextTempVendorID = string.Empty;

        int recordCount = 0;

        Match regexMatch = Regex.Match(lastTempVendorID, "[0-9]");
        if (regexMatch.Success)
        {
            digitStartIndex = regexMatch.Index;
            alpha = lastTempVendorID.Substring(0, digitStartIndex);
            digits = lastTempVendorID.Substring(digitStartIndex);
            digitLength = digits.Length;
            tempVendorNumber = Convert.ToInt32(digits);

            nextTempVendorID = alpha + (tempVendorNumber + 1).ToString().PadLeft(digitLength, '0');

            string sqlCommand = "UPDATE PM40100 WITH (TABLOCKX HOLDLOCK) SET NXTVNDID = @NXTVNDID";
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@NXTVNDID", System.Data.SqlDbType.VarChar, 15);
            sqlParameters[0].Value = nextTempVendorID.Trim();

            recordCount = DataAccess.ExecuteNonQuery(CommandType.Text, sqlCommand, sqlParameters);

        }

        if (recordCount == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch (Exception ex)
    {
        Log.Write("An unexpected error occurred in UpdateNextTempVendorID: " + ex.Message);
        return false;
    }
}


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






Setting distributions for a Integration Manager single file SOP Invoice import using VB Script

By Steve Endow

Let's say you want to import some SOP Invoices into Dynamics GP using Integration Manager.  And let's say that you have a single data file that contains the invoice lines.

Setting up a standard integration with separate Header and Line data sources is fairly straightforward.

But then you realize that you need to set the distributions for each invoice.  You need to assign different AR accounts and different Sales accounts to the invoices.  The customer does not have any default accounts for their GP customers, and you need to setup 3 different integrations, each of which will post to a different AR and sales account.  Fortunately, all invoices for each integration will be assigned to the same AR and sales account--so that was some consolation.

I'm actually a little rusty with this type of IM challenge.  Maybe there is a better way to accomplish this, but the last time I had to do this, about 9 or 10  years ago, I wrote a Before Integration VB Script that read the invoice data file and generated a Distribution file.

Not wanting to dig through 10 year old client files to find my ancient script, and not really wanting to have to add a Distribution data source to this integration, I thought of a hack.

What if we were able to use the customer default accounts so that IM would use those accounts for the invoice distributions?  That would allow us to avoid touching the distributions in the IM integration.  How would we do that?

Well, what if we had a Before Document script that set the default AR and Sales distributions for the customer?  We know which AR and Sales account should be used for each integration, and all invoices in that integration will use the same accounts, so we should be able to set the default account for each customer right before each invoice is imported.

But after we set the default AR and Sales account for the customer, and after the invoice is imported, then that customer has default accounts that we actually don't want as the defaults.  So why not create a similar After Document script to clear the default accounts?  Since this customer does not have any default customer accounts, we just wipe out the AR and Sales accounts for the customer.


So here is the script that I came up with.  I pulled it together and handed it off to a partner to test, so there might be a few bugs or rough edges, but you should get the drift.

Here is the Before Document script, which sets the default account for the customer.  You would change the account numbers and the source field for the Customer ID.

If your customer records have default accounts that you want to preserve, you could query those, store them in integration variables, and then set them back in the After Document script rather than clearing them.


UPDATE & CORRECTION:  So after some testing, the partner found that the default Customer Sales Account is not used for SOP--only for RM.  This makes sense, but was annoying to discover.  So instead of setting the sales account at the customer level, it can be set at the system level.  I've modified the scripts to reflect the update to SY01100 for the sales account.


'9/10/2014
'IM Before Document Script to set AR and Sales accounts for customer prior to IM invoice import

strIntercoID = GPConnection.GPConnInterCompanyID

'Get a connection object.
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "database=" & strIntercoID
GPConnection.Open(objConn)

'Create an ADO command object.
Set objCmd= CreateObject("ADODB.Command")
objConn.CursorLocation = 3

'set the database to the currently logged in db.
objConn.DefaultDatabase = strIntercoID
objCmd.ActiveConnection = objConn

objCmd.CommandType = 1

strARAccount = "000-1200-00"
strSalesAccount = "000-4100-00"

'Read customer ID
strCustomerID = Trim(SourceFields("InvoiceData.Customer ID"))

strSQL1 = "UPDATE RM00101 SET RMARACC = (SELECT ACTINDX FROM GL00105 WHERE ACTNUMST = '" & strARAccount & "') WHERE CUSTNMBR = '" & strCustomerID & "'"

strSQL2 = "UPDATE SY01100 SET ACTINDX = (SELECT ACTINDX FROM GL00105 WHERE ACTNUMST = '" & strSalesAccount & "') WHERE SERIES = 5 AND SEQNUMBR = 400"

'Set and execute the queries
objCmd.CommandType = 1 '1 = Text

objCmd.CommandText = strSQL1
objCmd.Execute

objCmd.CommandText = strSQL2
objCmd.Execute



And here is the After Document script which clears the accounts for the customer.


'9/10/2014
'IM Aftwer Document Script to clear AR and Sales accounts for customer after IM invoice import

strIntercoID = GPConnection.GPConnInterCompanyID

'Get a connection object.
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "database=" & strIntercoID
GPConnection.Open(objConn)

'Create an ADO command object.
Set objCmd= CreateObject("ADODB.Command")
objConn.CursorLocation = 3

'set the database to the currently logged in db.
objConn.DefaultDatabase = strIntercoID
objCmd.ActiveConnection = objConn

objCmd.CommandType = 1

'Read customer ID
strCustomerID = Trim(SourceFields("InvoiceData.Customer ID"))

strSQL1 = "UPDATE RM00101 SET RMARACC = 0 WHERE CUSTNMBR = '" & strCustomerID & "'"

strSQL2 = "UPDATE SY01100 SET ACTINDX = 0 WHERE SERIES = 5 AND SEQNUMBR = 400"

'Set and execute the queries
objCmd.CommandType = 1 '1 = Text

objCmd.CommandText = strSQL1
objCmd.Execute

objCmd.CommandText = strSQL2
objCmd.Execute



The partner is going to test it to confirm that it works for the customer, but if it does the trick, I think it's a pretty clever workaround to bothering with distributions for an Integration Manager import.


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




Monday, September 8, 2014

Dynamics GP BCHSOURC values

By Steve Endow

I was recently asked if there was a list of values and descriptions for the BCHSOURC field in GP.  I didn't find one during a quick search, so here is a list from the core GP modules.


BCHSOURC SERIES Description
ASMENT 5 Assembly Entry
GL_Clearing 2 Clearing Entry
GL_Normal 2 General Entry
Invoice Entry 3 Invoice Entry
IV_Trans 5 Transfer Entry
IV_Trxent 5 Transaction Entry
PM_Payment 4 Payment Entry
PM_Trxent 4 Payables Trx Entry
Rcvg Trx Entry 4 Receivings Trx Entry
Rcvg Trx Ivc 4 Purchasing Invoice Entry
RM_Cash 3 Receivables Cash Receipts
RM_Sales 3 Receivables Sales Entry
Sales Entry 3 Sales Transaction Entry


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



Friday, September 5, 2014

The kinder, gentler BSOD

By Steve Endow

I just finished testing a new release of a Dynamics GP integration.  I was in the middle of updating my documentation, working in Word, something I've done for a few years on my desktop computer.

After typing a word, blam-o, both monitors go black and then one turns blue.


Lovely.

This is the first time I've seen a BSOD on Windows 8 / 8.1.  While less obnoxious than the old Windows NT-style blue screens, it is no more comforting.

My hardware hasn't changed, and I haven't done anything crazy with new software, so hopefully this isn't a sign of my desktop jumping off a cliff like my file server did a few weeks ago.

Thankfully Word recovered my document, so I didn't lose any work.  And Windows 8.1 with an SSD boots up lightning fast, so I was back up and working quickly, but a BSOD is not what I wanted to see today.

Is it possible to actually get work done without interruption?


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


Thursday, September 4, 2014

RUN FOR THE HILLS! Christina is trying to write code!

Well, not really.   But I did borrow the following post from the venerable David Musgrave, in order to set a trigger to default the project accounting Change Order Entry window to make changes to the Forecast budget (rather than the default of Baseline budget).

http://dynamicsgphelp.com/2011/05/setting-default-values-in-gp-windows/

So, if you are familiar with Project Accounting, when you access Cards-Project-Change Order Entry it defaults to Track Changes To: Baseline.  Well, this is a bummer since most folks want to track changes to Forecast and preserve the Baseline as the original budget.  So user error can cause the change orders to be applied to the wrong budget.

So to set the field to default to Forecast instead, I used the Support Debugger option in David's awesome post, and used the following script...

out boolean OUT_Condition;
OUT_Condition = false;
if isopen(form PA_Change_Order_Entry) then
 OUT_Condition = true;
'PA GB Track Changes' of window 'PA_Change_Order_Entry' of form
'PA_Change_Order_Entry' = 1;
end if;

Pretty cool if you ask me!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Dealing with 1/1/1900 with GP and SQL Reports

If you have created reports with SQL Reporting Services for GP, you have inevitably encountered 1/1/1900 showing up on your reports.  Maybe as the inactive date for an employee?  Or the due date on a  payables transaction?

In some cases, you may not mind that the dates display that way.  However, eventually, a client will ask...why do those dates show that way?  Can you hide them if they are blank?  Well, sadly, they aren't actually blank so it's not that easy.  Right?
But is actually pretty easy using an expression for the field, like this...

=IIF(Fields!THISISYOURDATE.value="1/1/1900 12:00:00AM","",Fields!THISISYOURDATE.Value)

So, in this example when my field THISISYOURDATE is 1/1/1900 the field will display as blank, otherwise it will show the date.

Easy easy :)

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.