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

http://www.precipioservices.com