Increment Key for UD table in BPM

Epicor ERP 10
11

I always keep a “Numeric” Sequence in Number20 or so, easier to work with in BPMs and in Dashboards so I don’t have to CAST and I can use .Max()

var ud = (from u in ttUD100A
	where u.Company == Session.CompanyID
	select u).FirstOrDefault();

if (ud != null)
{
	int maxSeq = (int)(from u in Db.UD100A.With(LockHint.NoLock)
		where u.Company == Session.CompanyID && u.Key2 == "REPORT" && u.Key3 == "MSO"
		select u.Number20).DefaultIfEmpty(1000000).Max() + 1;

	ud.Number20 = maxSeq; // Stores our Counter in Decimal
	ud.ChildKey1 = Convert.ToString((int) ud.Number20); // Assigns Counter to Key1

}

You could also then use Count() and Max() together in a way so that if user goes out of sequence, you remain in-sequence

	// TODO Later Refactor Ran out of time
	int nextSeq = (from u in Db.UD100.With(LockHint.NoLock)
		where u.Company == Session.CompanyID && u.Key2 == "REPORT" && u.Key3 == "MSO"
		select u).Count() + 1;

	int maxSeq = (int)(from u in Db.UD100.With(LockHint.NoLock)
		where u.Company == Session.CompanyID && u.Key2 == "REPORT" && u.Key3 == "MSO"
		select u.Number20).DefaultIfEmpty(0).Max() + 1;

	ud.Number20 = maxSeq >= nextSeq ? maxSeq : nextSeq; // Stores our Counter in Decimal
	ud.Key1 = Convert.ToString((int) ud.Number20); // Assigns Counter to Key1
5 Likes