First, I’ve been referencing this post on this topic, but I have a twist which is breaking my brain in two ways: Auto Sequence Customer ID
First the Customer ID’s have a two digit prefix, so I need to find the last customer ID that begins with ‘CC’ and the rest of the string are numbers (if they aren’t numbers I don’t want them and they would mess up my attempt to find the largest number).
I’ve tried to query this several different ways but each method I attempt throws an error of 'BPM runtime caught an unexpected exception of ‘InvalidOperationException’.
Here is one example:
I wanted to try RegEx, but that’s not supported… apparently.
I tripped across TryParse() & Parse(). I’d like to do TryParse() since it would return a boolean and I can handle if the Parse result would not be all Integers.
But I would rather use something that would ignore\skip\not select any records where the last 8 digits are not numbers.
Also, since there are prefixes and we would like the sequence to have as few gaps as possible I don’t think I can use the .GetNexSequence approach… if I’m wrong, please correct me and share how it works.
Once I determine the next sequence I need it to fill the CustID so it would be something like XX00000009
Second, we would like to try to fill gaps in the sequencing if they exist, which at this point seems like it’s miles away.
Not sure if this helps, but I did something similar with our part numbers where I needed to start the number with an alpha prefix based on what the user selected for the part type. (We were generating these in a separate Access DB at the time). I created a few different sequences, and then since we are on-premises I was able to cheat by seeding the Ice.SysSequence table with the highest value already used for each of the part types.
This won’t help fill your gaps, but maybe it helps going forward?
So…I’ve done something similar for my rev control system. I use a “/” to separate customer and internal revisions, but you can use StartsWith() if you know the prefix.
Key steps are
Split the user-inputted string into prefix and sequence.
Parse the sequence.
Compare it to existing records and update to largets known seq.
increment sequence by 1.
pad sequence with zeros, then append to prefix.
There is a lot to this since I wanted it to handle a lot of garbage. CustomerRev is entirely uncontrolled by our org.
string customerRev = "";
string strInternalRev = "";
int intInternalRev = -1;
bool result = false;
//Pulls Last Revision by EffectiveDate if no rev is provided.
if (BaseRev.Length == 0 || BaseRev == "(new)")
{
var lastRev = Db.PartRev.Where(pr => pr.PartNum == PartNum &&
pr.AltMethod == "")
.OrderByDescending(pr => pr.EffectiveDate)
.ThenByDescending(pr => pr.RevisionNum)
.ToList()
.Select(pr => pr.RevisionNum);
if (lastRev.Count() > 0)
{
BaseRev = lastRev.First();
}
else
{
BaseRev = "-";
}
}
//Splits the baseRev into a customer and PLN (internal) revs delimited by a slash.
if (BaseRev.LastIndexOf('/') == BaseRev.Length -3 &&
char.IsDigit(BaseRev[BaseRev.Length -2]) &&
char.IsDigit(BaseRev[BaseRev.Length -1]))
{
customerRev = BaseRev.Substring(0, BaseRev.LastIndexOf('/'));
strInternalRev = strInternalRev = BaseRev.Substring(BaseRev.LastIndexOf("/") + 1);
result = int.TryParse(strInternalRev, out intInternalRev);
}
else
{
result = true;
customerRev = BaseRev;
}
//Gets latest revision corresponding to the customer rev. If found, it updates internal rev so that the system increments from the last rev.
var rev = Db.PartRev.Where(pr => pr.PartNum == PartNum &&
pr.RevisionNum.StartsWith(customerRev + "/") &&
pr.AltMethod == "")
.OrderByDescending(pr => pr.RevisionNum)
.Select(pr => pr.RevisionNum)
.FirstOrDefault();
if (rev != null)
{
int intRev;
string strRev = rev.Substring(rev.LastIndexOf("/") + 1);
int.TryParse(strRev, out intRev);
if (intRev > intInternalRev)
{
intInternalRev = intRev;
}
}
//Increments internal rev and appends it to customer rev to produce new rev.
if (result)
{
intInternalRev += 1;
if (intInternalRev < 100)
{
string newInternalRev = "00" + intInternalRev.ToString();
NewRev = customerRev +
"/" +
newInternalRev.Substring(newInternalRev.Length - 2);
}
else //It's unlikely that we'd see 100 internal revs on one CustRev, but...
{ throw new ArgumentOutOfRangeException("Next Internal Revision"); }
}
I don’t think I’ll ever understand the business case for this. Except for Intercompany, one can change these at any time to something else. I get auto-sequencing to prevent the user from having to have to pick a number as a convenience. I would just grab CUSTNUM and format it in there. But no gaps and ten digits long? I just don’t get it.
And yes, you’re just the messenger so this isn’t against you my friend. I’d love to know the value it adds to the organization. It seems like a holdover from the paper system…
EDIT:
And what do they do when the customer name changes from say something like Perceptron to Atlas Copco? Reissue PER00001243 to ATL000002234?
If there is paperwork with the old customer number and now that “gap” was filled, will that cause issues?
At Big Corp we just incremented the IDs by +1, even Part Numbers.
Anyways something along these lines in BPM:
string custIdPrefix = "CC";
// The reason I Grab 10 is so if someone deletes a sequence, I can fill the gap but you can go with Take(1)
var filteredRows = Db.Customer
.Where(x => x.Company == Session.CompanyID && x.CustID.StartsWith(custIdPrefix))
.OrderByDescending(x => x.CustID)
.Take(10)
.ToList();
var maxSequence = filteredRows
.AsEnumerable() // Switch to client-side processing
.Select(s => Regex.Match(s.CustID, @"(?<=\D)\d+").Value)
.Where(s => !string.IsNullOrEmpty(s)) // Ensure we have a match
.Select(s => Convert.ToInt32(s))
.DefaultIfEmpty(0) // In case of no sequences found, default to 0
.Max();
int nextSequence = maxSequence + 1;
On Client Side (Classic) it would be something along these lines:
string whereClause = string.Format("Company = '{0}' and PartNum LIKE '{1}-{2}[0-9][0-9][0-9][0-9][0-9]%' BY PartNum DESC", ((Ice.Core.Session)oTrans.Session).CompanyID, prefix, sDashPrefix);
DataSet ds = bor.GetListWithPaging("Erp:BO:Part", whereClause, 100, string.Empty);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
var list = ds.Tables[0].AsEnumerable().Select(s => Convert.ToInt32(Regex.Match(s.Field<string>("PartNum"), @"(?<=\D)\d+").ToString() ));
int missingSequence = Enumerable.Range(list.Min(), list.Count()).Except(list).FirstOrDefault();
// Use Missing Sequence found or Max
if (missingSequence >= 1)
{
return string.Format("{0}-{1}{2}", prefix, sDashPrefix, missingSequence.ToString().PadLeft(5, '0'));
}
nextSeq = list.Max();
}
There is none, but it’s prompted some nice code shares. I’ve already made a couple tweaks to my submission because I saw something here or I just because I looked at it again.
While I have been involved in created this solution in the past for multiple customers, I also am a person who heavily argues AGAINST auto sequence of customers and suppliers… Epicor allows users to choose their own customer id because customer ID can be something easy to remember…
CalPanel = California Panel company
RFPaint = RF Paint
ABCM = ABC Metals
People can typically remember alpha codes, but it is much harder for them to remember 19283 for California Panel.
BUT WHAT IF California Panel becomes Arizona Panal? Change the ID to AZPanel
WHAT IF there is a CalPanel north and CalPanel south? Well… make it CalPanel1 and CalPanel2
Anyway, I will jump off my soapbox… yes, I know that different companies have different “rules” but I just dont see the reason for forcing a sequential numeric customer ID that is meaningless.
Thanks to everyone for their help and insight, it’s all been very helpful!
The context is a BPM that will allow users to quickly create Credit Card only Customers.
These users are normally not permitted to create customers due to credit check requirements, but if it’s a Credit Card order (and using the Credit Card module) the Sold To needs to line up with the Credit Card Address. (this is better for future conversion of the CC customer from a one-off customer to a regular customer, at which point they would update the CustID to be something more memorable).
So the idea is that if the ‘restricted user’ hits New Customer in Customer Entry, we prompt with a BPM Form for the Customer Address and a pre-generated CustomerID (so they don’t have to think about it and can be fast - they can override if they want). The rest of the customer profile, since it’s Credit Card, is all predefined and the BPM sets all that data and returns with a confirmation. So basically they enter and address and the Customer is created.
They want to track individual Customers because in their old system they are certain they were losing track of conversion of first-time customers to regular customers and since they were trying to just change the address of a single CC customer account, they didn’t have a good way to market to those customers again.
So the reasoning for the sequential numbering is really for quick entry, no real rules. I did consider @Mark_Wonsil suggestion to just shove the CustNum in there but I wanted to maximize the number of possible customer ID’s.
As for the final solution (all low code). I used two BPM Variables to find the last CustID, increment it and assign it to the ClientContextData for the BPM Form: @klincecum got me very close, but LINQ to SQL seems to have limitations, but with @klincecum suggestion ChatGPT directed me to something similar that works:
This cool SQL statement will work, until some evil user enters a new customer ID “CC99999999”… I would recommend that you create a BPM that disallows any new customer ID starting with “CC” to be manually entered by a user.
Is there a process to transfer the customer to a non-CC customer later? Will they go through the regular credit checking process then rename the ID? Just curious.
