Over the past decade, I’ve only encountered one instance where a client contemplated using temporary vendor IDs. While I explored the concept, I’m uncertain if they ultimately implemented it.
Recently, I was tasked with creating an eConnect integration to import AP invoices, with each invoice linked to a temporary vendor ID. This required generating a new vendor record with a temporary ID before each invoice import.
Dynamics GP possesses an unusual feature allowing users to delete temporary vendor records while preserving associated transactions.
My initial thought is that this feature might have addressed database size limitations of the past. However, vendor records typically have a negligible impact on database size and server capacity today, making it a less compelling reason to use temporary vendors.
This feature’s peculiarity is amplified by the absence of a corresponding “Temporary Customer” option. Why is it absent? I’m aware of numerous businesses with a vast number of one-time customers, yet relatively few one-time vendors.
In this particular client’s case, each vendor receives a single, small payment, and the likelihood of future payments to these vendors is extremely low. Given the “one-time” nature of these vendors, utilizing the temporary vendor feature is logical.
What distinguishes importing temporary vendors from regular vendors? The distinction lies in the temporary vendor IDs. The Payables Setup Options window includes a “Next Temp. Vendor ID” field to define a dedicated vendor ID series for temporary vendors.
Pressing CTRL + T within the vendor ID field of the Payables Transaction Entry window triggers GP to automatically populate the temporary vendor ID in both the Transaction Entry and Vendor Maintenance windows.
This is convenient, but how would one import temporary vendors?
Similar to regular vendors, the process involves obtaining the next temporary vendor ID. However, eConnect lacks support for this, and it appears Dynamics GP retrieves the next vendor ID programmatically rather than through a stored procedure.
Consequently, I had to develop a custom solution.
Below are my C# functions to retrieve the next temporary vendor ID. The task is made slightly complex by a few factors. Upon retrieving the next temporary ID, I verify its availability as GP doesn’t prevent manual entry, and the next temporary ID can be modified.
Afterward, the ID must be incremented, and the new “next” value stored back in the database. Given the alphanumeric nature of temporary vendor IDs, the prefix needs to be preserved while incrementing the numeric suffix. I couldn’t presume that the vendor’s temporary ID sequence prefix would match my testing environment. While not overly complicated, splitting the string, incrementing the numeric suffix, and recombining them elegantly required some effort. Ultimately, a forum post provided a suitable Regex implementation.
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 based in Los Angeles. He owns Precipio Services, a company that offers Dynamics GP integrations, customizations, and automation solutions.
You can connect with him onGoogle+andTwitter
http://www.precipioservices.com
