Increment Key for UD table in BPM

Aaron_Moreng · September 7, 2018, 4:54pm

I’m having a little trouble adding a new row inside of a BPM to UD05.
I created a “seed” record in the UD05 table with Key1 = 1. What I am trying to do is increment Key1 every time a new record is added to this table via this BPM. Here is my code:

//Write new record in UD05
using(var UD05Svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.UD05SvcContract>())
  {
    //grab latest record in UD05
    var existingRecs = Db.UD05.Where(r=>r.Company == "JRF").LastOrDefault();
    var lastKey = Convert.ToInt32(existingRecs.Key1);
    var newKey = lastKey++;
    //get new row
    var ds = new Ice.Tablesets.UD05Tableset();
    UD05Svc.GetaNewUD05(ref ds);    
    //modify ds
    var newUD05Row = ds.UD05.FirstOrDefault();
    newUD05Row.Company = "JRF";
    newUD05Row.Key1 = (string)newKey.ToString();
    newUD05Row.Key2 = "update_shipping";
    newUD05Row.Key3 = "";
    newUD05Row.Key4 = "";
    newUD05Row.Key5 = "";
    newUD05Row.Date01 = DateTime.Now;
    newUD05Row.Character01 = json;
    //update 
    UD05Svc.Update(ref ds);
  }

When I run the code, I get this error:

Server Side Exception

BPM runtime caught an unexpected exception of 'NotSupportedException' type.
See more info in the Inner Exception section of Exception Details.

Exception caught in: Epicor.ServiceModel

Error Detail 
============
Description:  BPM runtime caught an unexpected exception of 'NotSupportedException' type.
See more info in the Inner Exception section of Exception Details.
Program:  EntityFramework.dll
Method:  Translate
Original Exception Type:  NotSupportedException
Framework Method:  A003_CustomCodeAction
Framework Line Number:  0
Framework Column Number:  0
Framework Source:  A003_CustomCodeAction at offset 538 in file:line:column <filename unknown>:0:0

Server Trace Stack:     at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert()
   at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
   at Epicor.Customization.Bpm.DB45D7D135CDC4463D832D57762D519A76.PostTranDirective_UpdateTracking_DropShipHead_67848129875E4559885D630E0F86D077.A003_CustomCodeAction()
   at Epicor.Customization.Bpm.DB45D7D135CDC4463D832D57762D519A76.PostTranDirective_UpdateTracking_DropShipHead_67848129875E4559885D630E0F86D077.ExecuteCore()
   at Epicor.Customization.Bpm.DirectiveBase`3.Execute(TParam parameters) in C:\_Releases\ICE\RL3.2.200.0\Source\Server\Internal\Lib\Epicor.Customization.Bpm\DirectiveBase.Generic.cs:line 147



Client Stack Trace 
==================
   at Epicor.ServiceModel.Channels.ImplBase`1.ShouldRethrowNonRetryableException(Exception ex, DataSet[] dataSets)
   at Erp.Proxy.BO.DropShipImpl.Update(DropShipDataSet ds)
   at Erp.Adapters.DropShipAdapter.OnUpdate()
   at Ice.Lib.Framework.EpiBaseAdapter.Update()
   at Erp.UI.App.DropShipmentEntry.Transaction.Update()

Inner Exception 
===============
LINQ to Entities does not recognize the method 'Ice.Tables.UD05 LastOrDefault[UD05](System.Linq.IQueryable`1[Ice.Tables.UD05])' method, and this method cannot be translated into a store expression.

Is there something else I should be thinking of to grab the last row in the table and then increment it?

Banderson · September 7, 2018, 5:00pm

Could it be no quotes around json?

Aaron_Moreng · September 7, 2018, 5:01pm

Seems this is an LINQ to Entities limitation. I got around it by changing the way it’s looking for the record

var existingRecs = Db.UD05.Where(r=>r.Company == "JRF").OrderByDescending(r=> r.Key1).FirstOrDefault();

Now, I just need to work on the logic for the incriminating of Key1

Banderson · September 7, 2018, 5:05pm

Does it have to be incremented numbers ? I was able to make new records using DateTime.Now().ToString() on one of the keys. You would be able to make a new row at 1 per second and get unique rows.

Aaron_Moreng · September 7, 2018, 5:05pm

It doesn’t have to be. I also had it humming with guids as keys, but I decided i hate that and it looks awful. Same thing would go for datetime, but I might have to resort to it

Banderson · September 7, 2018, 5:06pm

At least you can sort by date/time

Can you do a set by query instead of code?

Aaron_Moreng · September 7, 2018, 5:08pm

I find the widgets much more complex for me to understand than code and the portability is low between BPMs.

ckrusen · September 7, 2018, 5:31pm

And have a builtin log of when the record was created.

Aaron_Moreng · September 7, 2018, 5:33pm

I’m already storing a date in a date field, but I see the value in storing as a key too.

Aaron_Moreng · September 7, 2018, 5:36pm

Here’s the working code:

//Write new record in UD05
using(var UD05Svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.UD05SvcContract>())
  {
    //grab latest record in UD05
    var existingRecs = Db.UD05.Where(r=>r.Company == "JRF").OrderByDescending(r=> r.Key1).FirstOrDefault();
    var lastKey = Convert.ToInt32(existingRecs.Key1);    
    var newKey = lastKey+1;     
    //get new row
    var ds = new Ice.Tablesets.UD05Tableset();
    UD05Svc.GetaNewUD05(ref ds);    
    //modify ds
    var newUD05Row = ds.UD05.FirstOrDefault();
    newUD05Row.Company = "JRF";
    newUD05Row.Key1 = (string)newKey.ToString();
    newUD05Row.Key2 = "update_shipping";
    newUD05Row.Key3 = "";
    newUD05Row.Key4 = "";
    newUD05Row.Key5 = "";
    newUD05Row.Date01 = DateTime.Now;
    newUD05Row.Character01 = json;
    //update 
    UD05Svc.Update(ref ds);
  }

hkeric.wci · September 7, 2018, 5:39pm

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

Alice_Elizabeth · July 30, 2020, 5:54pm

Hello,

I recognize this is an old post, but I am trying to accomplish exactly this - a unique key to be generated with each new record created on UD20. If I want to use your method of having the key be DateTime.Now().ToString() , am I using a method directive to set this when a new record is created? And if so, what business object would I be working with? I am unsure about how to use a directive to act on a UD table.

Thanks,

Alice

hkeric.wci · July 30, 2020, 8:46pm

I have mine on Ice.UD03.Update Method Directive (PRE):
I look for Added Rows. You would at that point in BPM just work with ttUD03 and directly assign column.

You could also do this one on Data Directive UD03

But it depends on your case do you need to know the Incremental field in the Customization immediately then maybe you need Ice.UD03.GetaNew…

Arul · July 30, 2020, 10:58pm

I do the same storing sequence number in Number20 as it will be useful in sorting

Alice_Elizabeth · July 31, 2020, 1:15pm

Thank you so much. This worked great for me.

Topic		Replies	Views
Insert UD Table Records from a BPM Epicor ERP 10 bpm	13	15325	October 19, 2020
UD100/UD100A Auto Increment Epicor ERP 10	3	1969	May 1, 2018
Insert records into a UD table Epicor ERP 9	13	2994	April 11, 2020
BPM Code to add Parent/Child UD1XX/UD1XXA data Epicor ERP 10 e10 , bpm	15	3033	October 22, 2021
Auto-Increment Primary Key on UD100 New Row? Epicor ERP 10	2	2023	February 16, 2017

Increment Key for UD table in BPM

Related Topics