New Solution Setting up controlled Customer/Vendor ID's

Epicor ERP 10
,
1

I have been working on a way to control both the Customer and Vendor ID as this is what we do in our legacy system. After looking at various methods on here to achieve this I have finally come up with the attached coding which achieves all my requirements.

To explain, I wanted a method to generate unique ID’s based solely on the customer name entered made in the format AAA999 where AAA denotes the first few characters of the Customer name and the 999 is an incremental number starting at 001 for every AAA entered.After research I decided to use the UDCodes table to hold an index of all the AAA values and an associated “Next” 999 number (Held in the Description field of the UDCodes table). All the UDCodes are held under a CodeType field of “NEXT” with a code of either “CUST_” or “VEND_” depending on the ID field being generated. An example of how the UDCodes table looks is as follows:
image.
This shows that there are 3 existing customers with ID’s starting with “ACT” i.e ACT001, ACT002, ACT003 and 4 Vendors with ID’s AIR001, …AIR003.

It took me quite a while to work out the coding to achieve my aim but it consists of 2 x Method directives for the Customer Table and 2 similar directives for the Vendor table.

The directives use the following usings and references:
image

image
image861×296 15.2 KB

The first Post Processing Directive above is based on the GetNewCustomer BO simply fills the ID field in a newly inserted record with the text “AUTO” and the second Pre-Processing directive with custom C# code below is based on the Customer.Update BO does all the heavy lifting of allocating and updating the Customer ID once the Customer name has been entered.

In order to make a sensible set of Prefixes for each customer e.g “AIR001”, the prefix being “AIR” I have set the coding to remove any non standard Characters such as punctuation, *, & etc… which may well exist in the customer name as entered.

Anyway, here is the code in the two Directives:

GetNewCustomer Post Processing:

image
image825×525 38.3 KB

The code below goes in the BO Customer.Update Pre-Processing directive as C# code:

/*   Auto Customer ID. Working Coding to add NEW record into UDCodes table with CdeType = "NEXT"
    Deals with adding new UDCodes as wellas updating any one that exists already
    Als deals with Customers that exist on file already but which DO NOT have a UDCode entry.
 
  Written by: R.D.Crozier
 
  Updates:
      17/06/2021 initial Writing
      21/06/2021 Add in Code to deal with Existing Customers who were not entered 
                  this way but throught DMT                  
  Comments:
    Initial idea taken from Post 4 of 5 in: 
      https://www.epiusers.help/t/e10-create-a-new-ud11-row-from-within-a-bpms-custom-code/33816/4 
*/

// Set up Working Variables
string cPrefix = "CUST_" ;			// Prefix for type of ID Cust ->Customer, VEND ->Vendor
int nNext_Account_Num = 1;			// Default value for Next Account ID
string cUserCodes_Type = "NEXT";	// USerCode Tyle Used to store the ID's
     
// Scan down all customers added in this transaction (Should only be 1)
foreach(var MyCustomer in 
      (from ThisCustomer in ttCustomer 
      where ThisCustomer.RowMod == IceRow.ROWSTATE_ADDED 
          && ThisCustomer.CustID == "**AUTO**"
      select ThisCustomer))
{

  // -----------------------------------------------
  // ----- Phase 1 - Add New UD Record in
  // -----------------------------------------------

  using (var txScope1 = IceDataContext.CreateDefaultTransactionScope())
  {
    // Set up the Short Customer ID example: IPN or ZZZ ...etc...
    // Taken From: https://stackoverflow.com/questions/19280553/how-do-i-remove-multiple-offending-characters-from-my-string

	// Get a customer name and remove all non std characters from it
	// We need Alpha/numeric as 1st 3 characters ideally
    string cLong_Name = MyCustomer.Name.ToUpper();   
    var cRreplacements = new[] { " ", "-", "_", ".", ",", "^", "?", "!", "/", "\"", "'", "\\", "*", ";", ":", ">", "<", "&", "%", "£", "(", ")", "@", "#", "~", "|", "$", "^", "[", "]", "`", "¬" };    
    //
    foreach (var cChar in cRreplacements)
      cLong_Name = cLong_Name.Replace(cChar, "");
	  
    // take 1st 3 significant digits from the parsed name to use as the UDCodes index.   
    cShort_Id = cLong_Name.Trim().Substring(0,3);
 
 
//this.PublishInfoMessage(cLong_Name+"\n<"+cLong_Name+">", Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
    
	// Phase 1 - Check to see if the UDCode record is already there
	// in which case we can use it to set the next Account ID
	// If it doesn't exist then create it  and set the Next_Account_Num field to 1
	// so we can use it in Phase 1 as it will exist!
	//
    // Variable to check if UDCode record exists already!
    bool lUDCode_Found = true;        

    // Prepare for adding a NEW UDCodes Record
    // Set up Business Object Service Contract to deal with UserCodes tablesets
    var oBO_UserCodes = Ice.Assemblies.ServiceRenderer.GetService<UserCodesSvcContract>(Db);
    
    // Define a tableset to hold new UserCode data    
    Ice.Tablesets.UserCodesTableset oUserCodesTableset = new Ice.Tablesets.UserCodesTableset();
    
    // Default to not found so we force an insert
    lUDCode_Found = false;
	
	// Scan for the UDCode we need
    foreach(var MyUDCodes in 
      (from ThisUDCodes in Db.UDCodes.With(LockHint.UpdLock)
              where ThisUDCodes.Company == Session.CompanyID
              && ThisUDCodes.CodeTypeID =="NEXT"
              && ThisUDCodes.CodeID == cPrefix + cShort_Id
              select ThisUDCodes))
    {
      // Having iterated through table we find a matching record!!!
      // So use this indicator toe NOT add a new UDCode
      lUDCode_Found = true;
    }
  
     
	// Did we find an existing UDCode record to take next ID from?
    if (lUDCode_Found == false)
    {
      // UDCode NOT found so we can add it in.
        
      // POint the Business Object to the tableset that we use to hold data 
      // Question: Does this tableset contain all existing OLD data or is it only used to add the new data?
      //
      // Prepare tableset to add new records in!!!
      oBO_UserCodes.GetNewUDCodes(ref oUserCodesTableset, cUserCodes_Type);      
    
      var oNewRow = oUserCodesTableset.UDCodes.NewRow();
    
      // Update the fields in the Added row in the USerCodes Tableset
      // Default the Next back to 1 so Phase II picks it up and increments it!
      //
      oUserCodesTableset.UDCodes[0].CodeID = cPrefix+cShort_Id;
      oUserCodesTableset.UDCodes[0].CodeTypeID = cUserCodes_Type;
	  // Set the last ID to be 0 so when adding a record it will be 
      oUserCodesTableset.UDCodes[0].CodeDesc = string.Format("{0}", nNext_Account_Num);
    
    
// Debug MessageBox
//this.PublishInfoMessage("Entering Try Catch for Add New UDCodes", Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
    
      // Commit changes to the Tableset via the Business Object
      // Note that this code MUST be here to action correctly!!!
      oBO_UserCodes.Update(ref oUserCodesTableset);
             
// Debug MessageBox
//this.PublishInfoMessage("Finished trying to Update/Add: "+ cCargo_01, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");

      txScope1.Complete();
    }
  }


// Find the maximum Index that already exists!!!
// i.e if IPN001 and IPN002  Exist then set next index to be 3
// so as to aadd a correct record!
// Set lOverride_Default_Cust_Num to true so we know what ID to write back into the UDCodes table
//
int nMax = 1;
bool lOverride_Default_Cust_Num = false;

foreach(var MyShortCustomer in 
  (from curCustomer in Db.Customer.With(LockHint.UpdLock)
        where curCustomer.Company == Session.CompanyID
              && curCustomer.CustID.Substring(0,3) == cShort_Id
  select curCustomer))
{  
      string cIndex = MyShortCustomer.CustID.Trim().Substring(3);  
      int nIndex = Int32.Parse(cIndex);
      
	  // we have found an existing record using the same UDCode ID which at this stage may or may not have been added
	  // Set indicator that we need to set up the NEW UDCode record with a mnual Next ID value
	  //  
      if (nIndex>=nMax)
      {
        lOverride_Default_Cust_Num = true;
        nMax = nIndex ;
      }
}

//Debug MessageBox
//this.PublishInfoMessage(string.Format("Max Existing Index: {0}", nMax), Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
    
  // -----------------------------------------------
  // ----- BREAK IN CODE FOLLOWED BY PHASE II
  //       PHASE Deals with getting the Next ID 
  //       From the UDCODES File then it updates it for
  //       the next addition.
  // -----------------------------------------------
  {
    {              
		using (var txScope2 = IceContext.CreateDefaultTransactionScope())
		{
   
			// Get the record from the UDCodes which will hold the next Sequence number
			// i.e <NEXT> <CUST_IPN> as an example
			foreach(var MyUDCodes in 
			(from ThisUDCodes in Db.UDCodes.With(LockHint.UpdLock)
				where ThisUDCodes.Company == Session.CompanyID 
				&& ThisUDCodes.CodeTypeID == "NEXT" 
				&& ThisUDCodes.CodeID == cPrefix+cShort_Id
				select ThisUDCodes))
			{
			// We have found an existing UDCode record so update it

			// Retrieve the next number from the CodeDesc field
			int.TryParse(MyUDCodes.CodeDesc, out nNext_Account_Num); 
			// Generate the Next_ID to write back to customer
			//
			if(lOverride_Default_Cust_Num)
				// We are setting up our own Last_ID field here as older ID's were found that weren't added 
				// using this method  (May have been DMT's into the system) 
				// increment it before writing it back
				nNext_Account_Num = nMax+1;
				
			// take next ID to be what is the value in the USCodes table + 1
			MyUDCodes.CodeDesc = (nNext_Account_Num+1).ToString();
        
        }
            
		// Close the transaction
        txScope2.Complete();
        //
      }
	  
	  // Write back the generated ID back to the customer table
      cAllocated_Id = cShort_Id + string.Format("{0,3:D3}", nNext_Account_Num);
      MyCustomer.CustID = cAllocated_Id; 
    }
  }
  Db.Validate();
}

You will need a variable cShort_Id set up as String in the Directive. It was used to show the generated Customer Code in an additional Widget, but is not used now so you coulod always simply declare cShort_Id as a string variable at the start of the program code.

There is a small section which deals with any live record CustID’s that are already present in the Customer table. IN this case, the procedure calculates what the nest ID should be and adds a new entry into the UDCodes table with the correct Type and Code. I needed this as we had already uploaded lots of customers with ID’s in the in the correct format and didn’t want to have to regenerate them or manually update the UDCodes table with new entries.

Please use the coding and principles held there for your own use without any problems. Thank you for the help and assistance given by others to me in this group and now I hope I can repay some of the debt I owe certain members for their help in the past.

Dave

2

You could eliminate the entire UDCode section just by looking for the next available number… the way I did it below was a “brute force” method… simply looking for one that doesn’t exist yet… benefit here is that it will fill in the blanks as it goes.
so the entire code block is much smaller… doesn’t need any scoping… doesn’t do any database updates or creates. It only does the one query against the customer table to find a list of all the customers with the same prefix… then it scrolls to find one that is not used.

/*   Auto Customer ID. Working Coding to add NEW record into UDCodes table with CdeType = "NEXT"
    Deals with adding new UDCodes as wellas updating any one that exists already
    Als deals with Customers that exist on file already but which DO NOT have a UDCode entry.
 
  Written by: R.D.Crozier
 
  Updates:
      17/06/2021 initial Writing
      21/06/2021 Add in Code to deal with Existing Customers who were not entered 
                  this way but throught DMT                  
  Comments:
    Initial idea taken from Post 4 of 5 in: 
      https://www.epiusers.help/t/e10-create-a-new-ud11-row-from-within-a-bpms-custom-code/33816/4 
*/

// Set up Working Variables
string cPrefix = "CUST_"; // Prefix for type of ID Cust ->Customer, VEND ->Vendor
int nNext_Account_Num = 1; // Default value for Next Account ID
string cUserCodes_Type = "NEXT"; // USerCode Tyle Used to store the ID's

// Scan down all customers added in this transaction (Should only be 1)
foreach (var MyCustomer in
        (from ThisCustomer in ttCustomer where ThisCustomer.RowMod == IceRow.ROWSTATE_ADDED &&
            ThisCustomer.CustID == "**AUTO**"
            select ThisCustomer)) {

    // -----------------------------------------------
    // ----- Phase 1 - Add New UD Record in
    // -----------------------------------------------

    // Set up the Short Customer ID example: IPN or ZZZ ...etc...
    // Taken From: https://stackoverflow.com/questions/19280553/how-do-i-remove-multiple-offending-characters-from-my-string

    // Get a customer name and remove all non std characters from it
    // We need Alpha/numeric as 1st 3 characters ideally
    string cLong_Name = MyCustomer.Name.ToUpper();
    var cRreplacements = new [] { " ", "-", "_", ".", ",", "^", "?", "!", "/", "\"", "'", "\\", "*", ";", ":", ">", "<", "&", "%", "£", "(", ")", "@", "#", "~", "|", "$", "^", "[", "]", "`", "¬" };
    //
    foreach (var cChar in cRreplacements)
        cLong_Name = cLong_Name.Replace(cChar, "");

    // take 1st 3 significant digits from the parsed name to use as the UDCodes index.   
    cShort_Id = cLong_Name.Trim().Substring(0, 3);

    //BRUTE-FORCE LOOKUP and use next available:
    //first we get a list of customer ids that begin with our ShortID
    var custIDList = Db.Customer.Where(x => x.Customer == Session.CompanyID && x.CustID.StartsWith(cShort_Id)).OrderBy(x=>x.CustID).Select(x => x.CustID);
    for (nNext_Account_Num = 1; nNext_Account_Num < 1000; nNext_Account_Num++) {
        cAllocated_Id = cShort_Id + string.Format("{0,3:D3}", nNext_Account_Num);
        //if there are NOT ANY, then use it.
        //is this one used?
        if (!custIDList.Any(x => x.CustID == cAllocated_Id)) {
            //we found an unused ID... lets use it!
            MyCustomer.CustID = cAllocated_Id;
            Break;
        }
    }
}
3

Tim,
As you are one of the people who has helped me in the past, and I snaffled some of your code as the basis for my work, thanks very much for your input.

I am just trying to get my head around the code logic at the moment though as I am not really a Linq expert by any stretch of the imagination! Still, my version did work and I’ll certainly implement your code as it does seem to offer some distinct advantages if I understand correctly, like filling in any missing ID’s in the sequence for each UDCodes entry.

Thanks again
Dave

4

Tim,
I had a couple of syntax errors in the Brute force code you sent, mainly the Company ID in the Customer table and the break command having the wrong case which I have fixed, but I can’t see where the only error left is coming from and what the error message “string” does not contain a definition for CustID and no accessible extension method CustID … . Any ideas please? As I said, my Linq skills definitely need some polishing up!

image
image1234×341 25.3 KB

5

Sorry about the typos… The code all came out of my head, and I never validated it with an editor…

Try changing this line, adding the “NEW” statement to fix your string error.

var custIDList = Db.Customer.Where(x => x.Customer == Session.CompanyID && x.CustID.StartsWith(cShort_Id)).OrderBy(x=>new{x.CustID}).Select(x => x.CustID);
6

Tim,
Sorry but same error and I can’t figure out why.

7

OK. the reason was I gave the wrong solution… I just put this into a BPM, and found the errors. Here is the completed code.
Note that I also added the “CST_” at the beginning of the ID also. I did some additional clean up and optimizing.

/*   Auto Customer ID. Working Coding to add NEW record into UDCodes table with CdeType = "NEXT"
    Deals with adding new UDCodes as wellas updating any one that exists already
    Als deals with Customers that exist on file already but which DO NOT have a UDCode entry.
 
  Written by: R.D.Crozier
 
  Updates:
      17/06/2021 initial Writing
      21/06/2021 Add in Code to deal with Existing Customers who were not entered 
                  this way but throught DMT                  
  Comments:
    Initial idea taken from Post 4 of 5 in: 
      https://www.epiusers.help/t/e10-create-a-new-ud11-row-from-within-a-bpms-custom-code/33816/4 
*/

// Set up Working Variables
string cPrefix = "CST_"; // Prefix for type of ID Cust ->Customer, VEND ->Vendor

// Scan down all customers added in this transaction (Should only be 1)
foreach (var MyCustomer in
        (from ThisCustomer in ttCustomer where ThisCustomer.RowMod == IceRow.ROWSTATE_ADDED &&
            ThisCustomer.CustID == "**AUTO**"
            select ThisCustomer)) {

    // Get a customer name and remove all non std characters from it
    // We need Alpha/numeric as 1st 3 characters ideally
    string cLong_Name = MyCustomer.Name.ToUpper();
    var cReplacements = new [] { " ", "-", "_", ".", ",", "^", "?", "!", "/", "\"", "'", "\\", "*", ";", ":", ">", "<", "&", "%", "£", "(", ")", "@", "#", "~", "|", "$", "^", "[", "]", "`", "¬" };
    //
    foreach (var cChar in cReplacements) {
        cLong_Name = cLong_Name.Replace(cChar, "");
    }

    // take 1st 3 significant digits from the parsed name to use as the UDCodes index.   we add "ZZZ" to the end to support very short names.
    string cShort_Id = cPrefix + (cLong_Name.Trim() + "ZZZ").Substring(0, 3);

    //BRUTE-FORCE LOOKUP and use next available:
    //first we get a list of customer ids that begin with our ShortID
    var customerWithPrefix = Db.Customer.Where(x => x.Company == Session.CompanyID && x.CustID.StartsWith(cShort_Id)).OrderBy(x => x.CustID).Select(x => new { x.CustID }).ToList();
    for (int nNext_Account_Num = 1; nNext_Account_Num < 1000; nNext_Account_Num++) {
        string cAllocated_Id = cShort_Id + string.Format("{0,3:D3}", nNext_Account_Num);
        //if there are NOT ANY, then use it.
        if (!customerWithPrefix.Any(x => x.CustID == cAllocated_Id)) {
            //we found an unused ID... lets use it!
            MyCustomer.CustID = cAllocated_Id;
            break;
        }
    }
}
8

The main advantage to Tim’s method is that you can use it for new Customers, without having to go into UDCodes and setup a new Customer :slight_smile:

9

Thanks to Tim for the help and Marjorie, yes Tim’s solution does mean that no UDCodes need to be set up as and unused/deleted or deprecated ID’s can be easily recycled. If you noticed, my original code actually creatted the UDCode records automatically so no outside processing was required.

However, I am keeping the UDCodes “master index” as I have additional BPM’s that will use the data held there for other purposes but I have incorporated Tim’s LinQ solution for allocating the ID to the CustID field to get the best of both worlds. I will post the final code if anyone else is interested over the next few days.

Finally, thanks to all the contributors to this forum for their help which certainly outshines any technical reference manuals that Epicor produce (if you can find them!). My understanding of what can now be done inside a BPM has been massively increased in the development of this seemingly simple BPM and hopefully I can use the techniques to develop more complicated solutions to the many bits of add-on code we require in our soon to go live system which has been over 18 months in development.

I do think it would be of great value if someone actually put together a guide to how to do things in BPM’s with as much sample code as possible to address standard interface tasks with BO’s and the Framework itself as I have found the integration of C# into the BPM environment quite challenging. A little like trying to learn the .NET framework from a book!! The fact we are on Public Cloud where debugging code seems to be quite prehistoric after coming from an On-Prem legacy system closely linked to Visual Studio mkes development seem painfully slow at times. A little like going back to GWBasic when the only debug facility was to use the Print or ? command.

Still, we are getting there slowly but surely and you have all helped. My next project is to attack the REST API framework to interface some outside legacy C# code. One thing I will do though is take a much closer look at the LINQ techniques which I have been avoiding for a number of years as old habits certainly do die hard!

Thank you all once again, you guys and Gal’s are great.

1 Like