Tuesday, April 28, 2015

Code to Import a Note with an eConnect AP Invoice

By Steve Endow

Last week a client asked me to add a new field to their AP Invoice Import.  They wanted to import a unique transaction ID for their AP invoices, which were being generated by their operational system.

They were already using the Document Number field and the PO Number field.  So that left them with the AP Invoice Note field.  Since the unique transaction ID didn't need to be queried or searched within GP, the Note field, while not ideal, would work for them.

But there was one problem.

When I tried to modify my application to import the new value into the AP Invoice Note field, there was no NOTETEXT eConnect node.  That's odd.

I pulled up the handy eConnect Programmer's Guide, jumped to the taPMTransactionInsert documentation, and searched for "note".


No matches found.  What?  Something must be wrong.  Clearly the documentation is incorrect.

Then I checked another eConnect help file.  No note.  Then I checked the taPMTransactionInsert stored procedure.  No NOTETEXT parameter.

So, it would seem that the summer intern who developed the taPMTransactionInsert eConnect method forgot to include the transaction note.  I can't imagine any valid reason why you can't import a Note for an AP Invoice.  And it's unbelievable that eConnect has been around this long and the Note field still hasn't been added.

Although admittedly, it would seem that this is the first time I've ever needed to import an AP Invoice Note, as I don't recall noticing this issue before.

So...  What does one do when eConnect doesn't have what you need?  I call it "off roading".

But what will we need to do in order to import a note for a transaction that we are importing via eConnect?  Well, it's a bit of a hassle.  Not feeling eager to write the code to insert a note, I installed WinGrep on one of my development servers and started searching through my Visual Studio source code files to see if I could find anything.

And behold, I found it.  Back in 2012, I apparently wrote an integration that had to insert notes for cash receipt transactions.  And guess what.  eConnect does not support Notes for cash receipts!  The same summer intern must have coded taRMCashReceiptInsert as well.

So, here is the code that I wrote to insert notes for AP Invoices imported with eConnect.

And yes, I know that you could also use the eConnect Post stored procedure, but I don't like them for three good reasons.

1. Debugging SQL stored procedures is a pain compared to debugging C# in Visual Studio
2. Putting custom logic in a Post stored procedure means having code in an additional location
3. When you apply a GP update or new release, the eConnect Pre and Post procs are typically dropped, and nobody ever remembers to re-script the procs.  Been there!

Yes, I am using a stored procedure to insert and update the note, but that proc is very granular and will not be dropped by a GP service pack or new release.  You could always move the note insert proc into C#, but I don't see much benefit of doing so.


First, there is the stored procedure to perform the insert or update of the note.  It even checks which version of GP is being used and appends the appropriate line break.

IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name = 'csspInsertUpdateNote')
DROP PROCEDURE csspInsertUpdateNote
GO

CREATE PROCEDURE csspInsertUpdateNote
@NOTEINDX AS int, 
@NOTETEXT varchar(8000)
AS
BEGIN
   
--SET NOCOUNT ON;
DECLARE @TXTFIELD varbinary(16);
DECLARE @LENGTH int;
DECLARE @CRLF varchar(10);
DECLARE @APPEND varchar(8000);
DECLARE @GPVERSION int;

--GP 2010+ uses CRLF for notes, whereas GP 9 and 10 use CR only
SELECT TOP 1 @GPVERSION = versionMajor FROM DYNAMICS..DU000020 WHERE COMPANYID = -32767 AND PRODID = 0
IF @GPVERSION >= 11
SET @CRLF = CHAR(13)+CHAR(10);
ELSE 
SET @CRLF = CHAR(13);

SET @APPEND = @CRLF + @NOTETEXT;

--Check if a note record exists
IF (SELECT COUNT(*) FROM SY03900 WHERE NOTEINDX = @NOTEINDX) = 0
BEGIN
--If not, insert a new note record with the update
INSERT INTO SY03900 (NOTEINDX, DATE1, TIME1, TXTFIELD) VALUES (@NOTEINDX, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())), CONVERT(VARCHAR(8),GETDATE(),108), @NOTETEXT)
END
ELSE
BEGIN
--If so, update the existing note
--Get the text pointer for the service call note
SELECT @TXTFIELD = TEXTPTR(TXTFIELD), @LENGTH = DATALENGTH(TXTFIELD) FROM SY03900 WHERE NOTEINDX = @NOTEINDX;

UPDATETEXT SY03900.TXTFIELD @TXTFIELD @LENGTH 0 @APPEND;
END
END
GO

GRANT EXEC ON csspInsertUpdateNote TO DYNGRP
GO



Next, there is the C# data access method to call the stored proc.

public static bool InsertUpdateNote(string gpDatabase, decimal noteIndex, string noteUpdate)
{

    string commandText = "csspInsertUpdateNote";

    SqlParameter[] sqlParameters = new SqlParameter[2];
    sqlParameters[0] = new SqlParameter("@NOTEINDX", System.Data.SqlDbType.Decimal);
    sqlParameters[0].Value = noteIndex;
    sqlParameters[1] = new SqlParameter("@NOTETEXT", System.Data.SqlDbType.VarChar, 8000);
    sqlParameters[1].Value = noteUpdate.Trim();

    int records = 0;

    try
    {
        records = ExecuteNonQuery(gpDatabase, CommandType.StoredProcedure, commandText, ref sqlParameters);
        if (records == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch (Exception ex)
    {
        Log.Write("An unexpected error occurred in InsertUpdateNote: " + ex.Message, true);
        return false;
    }

}



Here is a helper data access method to get the note index for a voucher.  (The ExecuteScalar method is my own data access wrapper to perform an execute scalar operation.)


public static int GetAPInvoiceNoteIndex(string gpDatabase, string vendorID, string voucherNum)
{
            
    string commandText = "SELECT TOP 1 NOTEINDX FROM PM10000 WHERE VENDORID = @VENDORID AND VCHNUMWK = @VCHNUMWK AND DOCTYPE = 1 ORDER BY DEX_ROW_ID DESC";  

    SqlParameter[] sqlParameters = new SqlParameter[2];
    sqlParameters[0] = new SqlParameter("@VENDORID", System.Data.SqlDbType.VarChar, 15);
    sqlParameters[0].Value = vendorID.Trim();
    sqlParameters[1] = new SqlParameter("@VCHNUMWK", System.Data.SqlDbType.VarChar, 17);
    sqlParameters[1].Value = voucherNum.Trim();
            
    string result = string.Empty;

    try
    {
        result = ExecuteScalar(gpDatabase, CommandType.Text, commandText, sqlParameters);
        return Convert.ToInt32(Convert.ToDecimal(result));
    }
    catch (Exception ex)
    {
        Log.Write("An unexpected error occurred in GetAPInvoiceNoteIndex: " + ex.Message, true);
        return 0;
    }

}


eConnect appears to automatically assign a Note Index to AP invoices, so you don't have to get the next index or assign it, but in case it is of interest, here is my data access method to get the next note index.  (The ExecuteNonQuery method is my own data access wrapper)

public static decimal GetNextNoteIndex(string gpDatabase)
{

    string commandText = "SELECT CMPANYID FROM DYNAMICS.dbo.SY01500 WHERE INTERID = @INTERID";

    SqlParameter[] sqlParameters = new SqlParameter[1];
    sqlParameters[0] = new SqlParameter("@INTERID", System.Data.SqlDbType.VarChar, 5);
    sqlParameters[0].Value = gpDatabase;

    string result = ExecuteScalar(gpDatabase, CommandType.Text, commandText, sqlParameters);
    int companyID = int.Parse(result);

    //Call the smGetNextNoteIndex proc in the Dynamics DB
    commandText = "DYNAMICS.dbo.smGetNextNoteIndex";

    sqlParameters = new SqlParameter[4];
    sqlParameters[0] = new SqlParameter("@I_sCompanyID", System.Data.SqlDbType.SmallInt);
    sqlParameters[0].Value = companyID;
    sqlParameters[1] = new SqlParameter("@I_iSQLSessionID", System.Data.SqlDbType.Int);
    sqlParameters[1].Value = 1;
    sqlParameters[2] = new SqlParameter("@O_mNoteIndex", System.Data.SqlDbType.Decimal);
    sqlParameters[2].Direction = ParameterDirection.Output;
    sqlParameters[2].Value = 0;
    sqlParameters[3] = new SqlParameter("@O_iErrorState", System.Data.SqlDbType.Int);
    sqlParameters[3].Direction = ParameterDirection.Output;
    sqlParameters[3].Value = 0;

    decimal noteIndex = 0;
    int recordCount = ExecuteNonQuery(gpDatabase, CommandType.StoredProcedure, commandText, ref sqlParameters);
    noteIndex = decimal.Parse(sqlParameters[2].Value.ToString());

    return noteIndex;

}



I then have a data access method to get the note index of the AP invoice and update the note.

public static bool AppendAPInvoiceNote(string gpDatabase, string vendorID, string voucherNum, string noteText)
{
    bool newNote = false;

    decimal noteIndex = GetAPInvoiceNoteIndex(gpDatabase, vendorID, voucherNum);

    if (noteIndex == 0)
    {
        newNote = true;
        noteIndex = GetNextNoteIndex(gpDatabase);
    }

    bool success = InsertUpdateNote(gpDatabase, noteIndex, noteText);

    if (success == false)
        return false;

    if (newNote)
        success = AssignAPInvoiceNote(gpDatabase, vendorID, voucherNum, noteIndex);

    return success;

}


public static bool AssignAPInvoiceNote(string gpDatabase, string vendorID, string voucherNum, decimal noteIndex)
{

    string commandText = "UPDATE PM10000 SET NOTEINDX = @NOTEINDX WHERE BCHSOURC = 'PM_Trxent' AND VENDORID = @VENDORID AND VCHNUMWK = @VCHNUMWK AND DOCTYPE = 1 AND NOTEINDX = 0";

    SqlParameter[] sqlParameters = new SqlParameter[3];
    sqlParameters[0] = new SqlParameter("@NOTEINDX", System.Data.SqlDbType.Decimal);
    sqlParameters[0].Value = noteIndex;
    sqlParameters[1] = new SqlParameter("@VENDORID", System.Data.SqlDbType.VarChar, 15);
    sqlParameters[1].Value = vendorID.Trim();
    sqlParameters[2] = new SqlParameter("@VCHNUMWK", System.Data.SqlDbType.VarChar, 17);
    sqlParameters[2].Value = voucherNum.Trim();
            
    int records = 0;

    try
    {
        records = ExecuteNonQuery(gpDatabase, CommandType.Text, commandText, ref sqlParameters);
        if (records == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch (Exception ex)
    {
        Log.Write("An unexpected error occurred in AssignAPInvoiceNote: " + ex.Message, true);
        return false;
    }

}



And finally, I call the AppendAPInvoiceNote method after successfully importing each invoice.

success = DataAccess.AppendAPInvoiceNote(gpDatabase, vendorID, voucherNum, noteText);
if (success == false)
{
    Log.Write("Failed to save Note for Vendor " + vendorID + " transaction " + voucherNum, true);
}



Pretty straightforward, but I am so glad I didn't have to write that from scratch...again, particularly because this was an urgent request and I just didn't have the time available.  That would have been a fair amount of research and coding just to insert a Note.  That eConnect should import for me.


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, April 20, 2015

Automatically post 2 million transactions a day in Dynamics GP

By Steve Endow

I started working with Envisage Software in 2009 to resell Post Master, which automatically posts Dynamics GP batches.  I have now worked with over 250 companies that needed an auto posting solution for Dynamics GP, and as a result, I’ve learned a lot about automatically posting batches in Dynamics GP.

Customers loved the original Post Master product, but customers didn’t want to have to keep a Dynamics GP client running and logged in all of the time just to post batches.  I worked with Andrew Dean at Envisage Software to brainstorm on how fulfill this request, and after 9 months of very hard work, Post Master Enterprise was released in July 2012.  Post Master Enterprise runs as a Windows service, so it does not require an open Windows session, and it does not require Dynamics GP to be running and logged in at all times.  If the server is rebooted, Post Master will automatically restart and resume posting batches without requiring any user intervention.

Post Master Enterprise has been a great success, allowing customers to automatically post thousands of Dynamics GP batches a day without lifting a finger.  But some Dynamics GP companies have batch posting requirements that are beyond imagination.

There are a few Post Master customers who are either importing transactions or posting batches in Dynamics GP 24 hours a day.  They have so much transaction volume that Dynamics GP can barely keep up, and they are constantly struggling to post all of their batches before the start of the next day or before month end close.

Andrew and I discussed how this problem could be solved, and after 4 months of development, a new Multi-Instance version of Post Master Enterprise was born.  The Multi-Instance version of Post Master can load 4, 6, or even 8 instances (and probably more) of Dynamics GP, and all of the instances can post batches simultaneously.

We began beta testing the new version in March, and the performance is astounding.

One beta site posted over 250,000 batches during the month of March using the new version of Post Master Enterprise.  An employee and a consultant were previously tasked with checking the Dynamics GP posting process throughout the night, regularly having to get up at 4am to make sure the Master Post process was running smoothly.  With Post Master Multi-Instance running 6 posting engines on a single server, the back log of imported batches was easily posted each evening, dramatically reducing stress levels.

A second beta site posted over 2 million transactions in one day using the Multi-Instance version of Post Master running 4 instances.  This customer has a challenging timing issue where they must leave all of their batches unposted during the month until they can finalize their landing cost calculations.  They must then quickly post several million transactions at the end of the month in order to complete their monthly close process.  The posting used to take a week with a single instance of Post Master Enterprise processing 24 hours per day.  Now they can comfortably post over 6,000 batches containing 2 million transactions in about 24 hours.  Even Andrew and I are astounded by this performance.  (Note: This customer has extremely high end hardware, so that certainly helped performance)

Post Master Enterprise Multi-Instance with 4 Instances

As a result of feedback from the beta sites, Envisage Software added some very nice features to the new version, making Post Master Enterprise Multi-Instance the most powerful and flexible auto posting solution for Dynamics GP.

First, the new Load Balancing feature automatically distributes batches across the multiple posting engines.  If there are hundreds of batches waiting to be posted, Post Master can now efficiently send the next batch to the first available posting engine.

Second, the Batch Prioritization feature allows batches to be prioritized by company, batch type, and batch ID.  If you need your inventory transactions to post faster than your SOP invoices, or your batches in Company A to post before batches in Company B, you can tell Post Master how to prioritize your batches.

Envisage Software is preparing a final beta release with some additional refinements, and we expect to have an official release by the end of this month.

If you have customers who need to automatically post Dynamics GP batches or are struggling to post a high volume of imported transactions, contact Envisage Software for a free Post Master Enterprise trial.

http://envisagesoftware.com/contactus/


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, April 17, 2015

Microsoft's best kept secret: Office Online

By Steve Endow

Have you heard of Google Docs?  It's a set of fully browser based applications for editing documents.  Think of it as an online version of Microsoft Word that only runs in  your web browser.  There is also Google Sheets, which is a browser based version of Excel.  You can share files with people, control permissions, and best of all, multiple people can edit the same document online at the exact same time--a feature now generally referred to as "collaboration" or "online collaboration".

Google fans--the ones who use Android phones and spurn all-things-Microsoft, talk about how great Google Docs is compared to MS Office, and hey, it's free!  I know of many people that rag on Office as being old and clunky, because, gasp!, it requires an installation on a computer.

Here's one example.


I totally agree with this post--if it is referring to the desktop version of MS Word / MS Office.  It is a nightmare to get edits from several people and combine and merge them.  It simply wasn't designed to do that.

But did you know that Microsoft offers the same thing as Google Docs and Google Sheets?  A fully browser-based online version of MS Office that offers sharing, permissions, and online document collaboration.  And it is remarkably similar to the version of Office you have installed on your PC.

Really.  No joke.

It's called Office Online.  And it is INCREDIBLE.

https://office.live.com/start/default.aspx

In this screen shot, I'm editing a Word document using Word Online in two different browsers to demonstrate the live collaboration functionality.  I'm signed in to my Office 365 account in IE, but have shared the document and am also editing it in Chrome.


The changes made in one browser immediately appear to other users.  Just like Google Docs.

Yes, Microsoft does have paid plans for Office Online, so you may not be able to do everything you want with a free account.  And yes, understanding the plans and potential confusion with Office 365 is likely limiting adoption when compared to the 'everybody knows its free' approach with Google.

Here is a video discussing Office Online vs. Office 365:

https://www.youtube.com/watch?v=iscKrbkWp2M

But if you have ever used Google Docs or Sheets, the functionality doesn't compare to MS Office.  At least I don't think so--but then again, I hate the Gmail interface, while I know people who thing Gmail is the cat's meow, so that's apparently personal preference.

So it's a great tool, but there are obviously a few caveats.  If you don't want your documents in the cloud, or if your corporate policies do not allow documents to be stored online, then Office Online, and Google Docs as well, are not an option.  And if you use the free version of Office Online, then there are some limitations to the features available.

Admittedly, in my case, I'm still old school and am used to having files stored locally on my computers.  While I do store some files online in OneDrive, I do not use it as the sole or primary repository, so using Office Online is currently a limited tool for me--for the rare cases where I might need others to collaboratively edit a document.

But if you need to get input on a document or spreadsheet from several people and have the option of hosting the file online, at least temporarily, you can use Office Online, and you always have the option to download the file when you are done and remove it from the cloud.


And all of this functionality integrates well with the fantastic Office Mobile, which works with iOS, Android, and Windows mobile.

https://products.office.com/en-us/mobile/office

Give it a try, and spread the word that there is a better alternative to Google Docs! (IMHO)

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, April 16, 2015

Tax Account in Vendor Account Maintenance

So, have you ever wondered when the following account is ever used?  From Cards, Purchasing, Vendor, Accounts button...





This came up at a client this week, and got me to wondering.  As I have always entered tax accounts on the tax detail window, Setup, Company, Tax Details.  However, that is actually NOT required.  You can leave the tax detail account blank.  Yes. You can.


I bet you see where I am going with this, don't ya?  If....

  1. The tax account is populated on the tax detail, it will be used on a transaction for the tax liability
  2. If the tax account is blank on the tax detail, the system will look to the vendor tax account instead
So why would you want to have a tax account by vendor?  I think the best example I can think off is with use tax.  If you want to track those amounts by vendor in your GL (or by locality, setting each vendor in a location up with the same account instead of maintaining multiple tax details).

Total sidebar, here is a link to my favorite way to handle use tax in Dynamics GP without additional products.

http://www.summitgroupsoftware.com/blog/tracking-use-tax-microsoft-dynamics-gp


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.

















Behind the Dynamics GP Remember User and Password option

By Steve Endow

Dynamics GP 2010 and GP 2013 have a "Remember user and password" option that allows you to save your GP login info so that Dynamics GP can login automatically.  When combined with the "Remember this company" option, a single click on the GP icon on your task bar will launch GP, login, and select a company.


A colleague was looking to enable this feature to perform some testing, but the Remember user and password option just wouldn't work.  I spent some time trying to figure out why it wasn't working for him, and in the process I had to find all of the places where GP stores the option and settings.  Since I went through that trouble, I figured I would document it all here for posterity.

If your "Remember user and password" option doesn't work, the setting doesn't save, or if the remember user option is always disabled, check these settings to see if one of them might be the problem.

By default, the Remember user and password option is not enabled.


You have to enable the option at the system level.  Open Tools -> Setup -> System -> System Preferences and check the Enable Remember User option, then click OK to save the setting.


When you save this option, it updates a record in the DYNAMICS..SY01402 table.

SELECT * FROM SY01402 WHERE USERID = 'GLOBALUSER' AND syDefaultType = 71


When the option is checked, the SYUSERDFSTR value will be 1.  When the option is unchecked, the value is 0.

So, once you enable that option, you will then have the ability to check the box the next time you launch GP and login.


When you enter your username and password, and then click OK, Dynamics GP creates two registry entries.  Mariano has a post discussing the entries here.


So at this point, you are all set to have GP remember your username and password.

But wait a minute.  How can that be?  How will the GP client know whether you have checked the option to Remember user and password?

Or, what if, despite doing all of these things like my colleague, the Remember user option still doesn't work and is disabled every time GP is launched?

There is the setting in SY01402, of course, but we double checked that and it was correct..  And, if GP hasn't logged in yet, it can't connect to the database, so it can't read the setting from SY01402.  So that wasn't the issue.

The GP client could be reading the registry entries to decide whether or not to login with saved username and password, but the registry entries were present on my colleague's computer and it still didn't work.

So where else does GP save settings besides the database?  Of course the answer is the tricky Dex.ini file.

After reviewing the Dex.ini file, we found the RememberUser setting in my Dex.ini file.  When my colleague checked his Dex.ini file, the RememberUser line was not present for some reason.


When he added the RememberUser=TRUE line to his Dex.ini, the Remember user feature started working.

Just another day in Dynamics GP paradise...


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, April 13, 2015

How to assign a new Dynamics GP Item Class to existing inventory items and roll down class settings

By Steve Endow


UPDATE: A reader contacted me to let me know that the process outlined below may not work properly if you are using Dynamics GP Manufacturing.  Some manufacturing tables contain the item class ID, so the process below does not account for those.  Also, updating the item class ID via SQL may not properly trigger certain updates in manufacturing.  The reader suggested that the macro approach, while more tedious to setup, is probably a safer method.


I was asked if there was a way to create a new Inventory Item Class, apply that new class to a lot of existing inventory items, and roll down the new item class settings to those inventory items.

Obviously you can open an individual inventory item, change the class ID, and when prompted, roll down the class settings to that single item.  But how do you do this in bulk for 50 or 100 or more items?

I searched around to see if anyone had a good solution, and I found a few potential workarounds, such as using a macro, but none of the options sounded very appealing.

After thinking about it for a few minutes, I came up with this solution.  It seems to work, but there may be a particular scenario that it doesn't handle, or some fields that it doesn't update.

Here's my approach to assigning a new item class to a bunch of existing inventory items and rolling down the new class settings to those items.

First, create the new inventory item class and populate as few fields as possible.  I'll explain why below.


Second, use a SQL statement to update the class ID for the relevant inventory items.  Something like this:
UPDATE IV00101 SET ITMCLSCD = 'NEWCLASSID' WHERE ITEMNMBR IN ('WIRE100', 'WATCH', 'TOP100G', 'TRANS100', 'TRANSF100', 'TEST')
Your items will now be assigned to the new classID.



Next, open your new inventory class and edit all of the fields that you need to set.


When you click on Save, you will be prompted if you want to roll down the changes.


Click on Yes to roll down the changes.

After rolling down the changes, open several of the inventory items to confirm that all of the settings rolled down properly.

In my testing, if I only changed one or two fields on the new item class, even though I clicked Yes to roll down the changes, the field changes to the class did not roll down to a test item.  I didn't perform additional testing to see why the small change didn't roll down, or which fields were, or were not, included in the roll down.  But I did test changing several fields, and after I rolled those changes down, they did update the items properly.

It seems like this should work, but as I mentioned earlier, there may be some caveats.  So give it a try and let me know if you find any issues or ways to improve the process.

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




Dynamics GP eConnect error: The source was not found, but some or all event logs could not be searched.

My blog has moved!  Please visit the new blog at:  https://blog.steveendow.com/

I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com

Thanks!



By Steve Endow


UPDATE: See comments at the bottom of this post for a resolution: Temporarily add the eConnect service account to the Local Administrator group.


UPDATE 2: I just worked with a customer where adding the eConnect domain user to the Local Administrator group still didn't resolve the issue.  Lacking any other ideas, the system administrator at the customer rebooted the server.  After the reboot, the eConnect error was properly written to the event log.  So it seems that in some environments, a server reboot may be required.


UPDATE 3:  I just built a new GP 2018 VM and encountered this eConnect permission error. After adding the eConnect windows user to the local Administrators group, I still got the logs error.  I then restarted the eConnect service, and that seems to have reloaded eConnect with the Administrator rights, and the error went away.  So, it could be that a server reboot may not be required, but a restart of the eConnect service is needed to refresh the permissions.


After upgrading an eConnect integration to Dynamics GP 2015, I received the following strange error message:


Exception type:Microsoft.Dynamics.GP.eConnect.eConnectException
Exception message:The source was not found, but some or all event logs could not be searched.  To create the source, you need permission to read all event logs to make sure that the new source name is unique.  Inaccessible logs: Security.
Stack Trace:   at Microsoft.Dynamics.GP.eConnect.ServiceProxy.CreateTransactionEntity(String connectionString, String xml)   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.EntityImportImplementation(String connectionString, String sXML, Boolean isTransaction)   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.ProcessEntityImport(String connectionString, String sXML, Boolean isTransaction)   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.CreateTransactionEntity(String connectionString, String sXML)


That part that stood out was "Inaccessible logs: Security".  This reminded me of some errors I've seen where an application is trying to write to a windows Event Log that doesn't exist, that it is unable to create, or that it doesn't have permissions to write to.

I looked all of the logs Windows Event viewer, but couldn't figure out what was causing the error.

After running the GP 2015 integration a few more times, I eventually received an error I did recognize.
Error Number = 936  Stored Procedure= taPMDistribution  Error Description = Vendor number does not existNode Identifier Parameters: taPMDistribution

Once I fixed the vendor ID in my test data file, the integration ran fine and I didn't receive the strange "The source was not found" message again.

I then installed the integration on the customer's workstation, and once again, the "The source was not found" error message appeared.  After a few more tests, we received a standard eConnect error message, fixed the data, and the integration ran fine.

My guess is that there is some type of issue with the eConnect 2015 install or the process that creates or writes to the eConnect event log when the first error is encountered.  The error appears once, and then things get straightened out in the log, and subsequent errors are logged properly.

Since the error does go away and appears to be a one-time event, I believe that you can safely ignore this message.  Unfortunately it is confusing for users, but if you remember to warn them in advance, or are able to recognize the error and explain the issue to users, it shouldn't be an issue.

And here's some GP geek trivia:  Did anyone notice that the error message refers to "eConnect12"?  Even though the internal version number for GP 2015 is version 14?  




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, April 8, 2015

Why specify a Batch Number when importing bank transactions using eConnect?

By Steve Endow

A few weeks ago I delivered an automated eConnect integration to a client that imports vendors, AP vouchers, GL journal entries, and bank transactions.  This integration replaced several manual Integration Manager imports.

The new eConnect import worked well, but recently the client noticed that they had lots of CMTRX general ledger batches piling up, whereas before they just had one CMTRX batch in the GL for the bank transactions that they imported with Integration Manger.


The fact that the GL batches were piling up tells us, incidentally, that they have their Bank Transactions set to Post To, but not Post Through.  But they had set the option to Append GL transactions to an existing batch in the Posting Setup window.


So why is eConnect creating a separate GL batch for every Bank Transaction?

Good question.  I didn't know either.

On my Dynamics GP test machines, I normally have all of my batch types set to Post Through, as I don't want to deal with piles of GL batches after testing.  I speculate that most GP customers also use Post Through, so I don't see too many issues like this for customers that only use Post To.  So that explains why I would have never noticed the issue of lots of GL batches generated by my eConnect import.

So why is this happening?

I checked my eConnect code to see if there was some configuration option that controlled how the Bank Transactions were posted to the GL, but didn't see anything.  At first.

After some poking around, I finally noticed, to my surprise, that the eConnect Bank Transaction document type has a BACHNUMB parameter!


And even more surprising, the eConnect help file actually explains that the field is for the GL batch number.  Since Bank Transactions don't have batch numbers, I would have never thought to look for this field.

This is a pretty unusual feature for eConnect--but then the Bank Transaction, which doesn't use batches, is also a bit of an unusual transaction for Dynamics GP.  Rather than rely on the Bank Transaction posting settings to determine the GL batch name, eConnect lets you specify a batch number.  If you don't specify a value, it will create a new batch for every single bank transaction of you are using Post To.

My advice:  Specify the batch number.

Once I assigned a batch number of "CMTRX", all of the transactions posted to a single GL batch.

Go figure!

 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





Dynamics GP Integration Manager script to generate a fixed length number

By Steve Endow

There are some situations when importing data into GP where you want to have a consistent or fixed length to a number.

Say you are importing Employees, and your source data file has numeric employee IDs such as 123 or 2345.  But suppose that you want all employee IDs to be a consistent 5 digits, with leading zeroes, in Dynamics GP.  So instead of 123, you want 00123, and instead of 2345, you want 02345.

Here is an Integration Manager field script to "pad" the Employee ID with leading zeroes.  It uses the VB Script Right function, which lets you easily pad a field value with leading zeroes.

CurrentField = Right("00000" & SourceFields("SourceName.EmployeeID"), 5)

This could also be used if you want to prefix an alpha value at the beginning of a document number.

So if you were importing invoices, and the data file had invoice 4567, and you wanted "INV00004567", you could do something like this:

CurrentField = "INV" & Right("00000000" & SourceFields("SourceName.DocNum"), 8)

That produces a consistent 8 digit invoice number, to which you can prefix "INV".


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



Tuesday, April 7, 2015

Bug in Dynamics GP 2013 eConnect Employee Deduction import: taCreateEmployeeDeduction

By Steve Endow

Several years ago I developed a Payroll integration for a customer that transitioned from Dynamics GP HR to Kronos HR.  The client wanted to use Kronos for HR, but keep GP for Payroll.  This resulted in a rather complex integration that required Kronos to send over any type of employee, benefit, and deduction change so that GP payroll could have accurate information.

The integration has been in place for years and running fine.  Recently, the client had to make a change to some of their deductions to modify the Tax Shelter settings.


They had to check the box to shelter the deductions from FICA Medicare.  This FICA Medicare tax shelter option was added in GP 2013, and if you query the UPR40900 and UPR00500 tables, you'll see the new SHFRFICAMED field tacked on near the end of the tables.

After my customer modified their deductions to check the FICA Medicare sheltered check box, they noticed that imported employee deductions did not have the box checked.


If they manually entered the employee deduction in GP, the FICA Medical sheltered option would be checked, as expected.

So this seemed to be a bug in eConnect, and I had to start digging to find it.  I have found a few other eConnect bugs while developing this very complex payroll integration, so it didn't surprise me at all that there might be one more.

The eConnect stored procedure that handles the insert and update of employee deductions is taCreateEmployeeDeduction, so that was the likely culprit.

As far as eConnect procedures go, taCreateEmployeeDeduction is quite simple.  It has validation for the input parameters, and then it performs an insert or update on the UPR00500 table.

I reviewed the stored procedure to see how it handled the SHFRFICAMED field.  There were so few references to the field that I didn't see anything that looked incorrect.  All of the SQL for the SHFRFICAMED seemed to match the other tax sheltered options.  I couldn't see any invalid logic or problems with the script that would lead to the FICA Medicare option to not be set properly in UPR00500.

I tried to debug the stored procedure directly, but it would be a hassle to get it setup for debugging, so I took the low tech approach: debug logging.

I created a logging table called cstbDeductionTrace to record the values of several of the stored procedure parameters.  I then added this line at several points in the stored procedure.  This let me capture the procedure parameter values at various points in the proc to see when the SHFRFICAMED parameter got its incorrect value.

INSERT INTO cstbDeductionTrace SELECT '1 Before Pre', @I_vEMPLOYID, @I_vDEDUCTON, @I_vSFRFEDTX, @I_vSHFRFICA, @I_vSHFRSTTX, @I_vSFRLCLTX, @I_vSHFRFICAMED;

I then ran my import and saw this in the trace table.  And I was puzzled.


Notice that the first four tax sheltered parameter values start out as NULL.  Only after the procedure pulls default values do they get set to 1, which makes sense.

But the SHFRFICAMED parameter starts out with a value of 0 rather than NULL.  And because the stored procedure is designed to only load default values for NULL parameters, the value never gets set properly--it stays at 0.

Huh.

So...why was the parameter being passed in with value of zero rather than NULL?

I wondered whether the eConnect serialization might be sending an invalid value.  I checked the XML I was sending to eConnect, and it didn't have a SHFRFICAMED node.

I then checked the eConnect documentation...and guess what.  The documentation doesn't even list the SHFRFICAMED field as a parameter.  And the eConnect object doesn't have a FICA Medicare property available in Visual Studio.  It looks like Microsoft forgot to add it to eConnect 2013 entirely.  So serialization didn't seem to be the culprit.

I then stared at the stored procedure SQL again.  And then it dawned on me.

If serialization wasn't adding the values for the tax sheltered fields in the XML, then it also wasn't passing values to the stored procedure.  Which means that the parameter values in the stored procedure were coming from...the parameter default values!

This seems a little obvious in hindsight, but when you are tracing and reverse engineering someone else's code, many things are far from obvious.

So I jumped to the top of the stored procedure to check the parameter default values...and sure enough, there was the culprit.


SHFRFICAMED was being defaulted to 0.  But what were the other tax sheltered parameters being defaulted to?


As I guessed, and as my trace data indicated, the other parameters were being defaulted to NULL.  Someone had typed the wrong default value for the new FICA Medicare parameter.  Bad Laama!

So why does a NULL vs. 0 value matter?  Well, the stored procedure is designed to only pull default values from the master deduction if the parameter is NULL.

@I_vSHFRFICAMED = CASE WHEN @I_vSHFRFICAMED IS NULL
                        THEN SHFRFICAMED
                        ELSE @I_vSHFRFICAMED

Since a 0 was being defaulted, the proc code thought that the value was being passed in, and didn't change it or override it.

Changing the parameter default value from 0 to NULL fixed the issue and resolved the issues with my deduction import.

I looked at the taCreateEmployeeDeduction on GP 2015 RTM and it has the same issue--an incorrect default value for the @I_vSHFRFICAMED parameter.  So the bug still exists there.

I checked the help file for GP 2015 eConnect, and guess what--the title page still says "eConnect 2013 R2", and also doesn't list the SHFRFICAMED field, so it seems that eConnect for GP 2015 has not added the FICA Medicare tax sheltered field.  Fun times ahead.

I'll be submitting this to Microsoft to see if they are aware of the issue, but I don't expect a fix for GP 2013, and I'm not holding my breath for a fix for GP 2015.  Fortunately it's fairly easy to correct the stored procedure, but it's a hassle to have to remember to update the proc after service packs and upgrades, as those can replace eConnect procedures.

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