Friday, June 5, 2015

Mekorma MICR, Where Did My Stubs Go?

Another interesting case this past week related to Mekorma MICR Checks.  A lot of our clients use this ISV solution, which works great (admittedly we sell it often for the benefits beyond the MICR line, in some cases to clients who don't want to even print the MICR line).

This particular case is regarding a version change, we were applying the latest service pack to a client for GP 2013.  So it would move them to GP 2013 R2.  Well, with this came a change in how Mekorma MICR stores the path to the stubs library. In the original versions, paths were specific to a workstation/install.  But in GP 2013 R2 and later, the paths are a global setting (which is definitely a good thing).

In this case, the client had two machines- a SQL server and a terminal server.  And there was a GP shared location where all of the normal reports and forms dictionaries, amongst other shared resources, were stored.  The update was applied to the SQL server first, whose stubs library was pointed to the GP share.

The issue came after we updated the terminal server and found that our modified stubs were missing!  Well, as it turns out the terminal server was actually pointed local for the stubs library.  So when the global was set to the shared location (where there were stubs, since the SQL server was pointed there previously), the terminal server was no longer viewing the previously modified stubs.

Easy fix fortunately, we located the stubs on the terminal server and copied them over to the share. But an important point to note, especially if you have numerous workstation installs as well.  We are careful to check for local reports and forms dictionaries, but we are now going to check for local stubs libraries as well when applicable.

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.

Interrupting Printing Edit Lists

Once is a fluke. Twice makes you think about it.  Last month we had a client who had a large batch of sales transactions (think 1,000s of transactions).  They printed an edit list, and the system locked up.  So what  did they do?  Something they thought would be benign...they used Ctrl-Alt-Delete to end their GP session.  And here is where it goes awry...


When they logged back in to GP, it told them that there was a batch in batch recovery. Okay.  Fine.  So they go to recover the batch, and are MORTIFIED when it goes ahead and POSTS THE BATCH.  Before they were ready, before they had completed some additional steps to their own process.  Ugh.  Double Ugh.  Triple Ugh.


So we worked through restoring a backup, and I admittedly did not think too much about it other than it being an odd fluke.  Until it happened again, to a different client.  And then I got to thinking how it makes PERFECT SENSE.  Yes, perfect sense.


Printing an edit list uses the same report as printing a posting journal, so presumably it changes the  batch's status when printing.  So it would make sense if that process is interrupted, that the status in the SY00500  table for the batch might indicate that it was indeed in the posting process.  So batch recovery would take that information and act on it.


So what to do?  Rather than use batch recovery in this instance, I would recommend using the batch stuck scripts available in this KB article:


https://support.microsoft.com/en-us/kb/850289


Make sure to use the "Let Me Fix It Myself" option of actually running the scripts to reset the batch status and make it available for continued review/editing.

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.

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;

}



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