Increment Key for UD table in BPM

Epicor ERP 10
1

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:
image

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?

2

Could it be no quotes around json?

3

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 :slight_smile:

4

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.

5

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

6

At least you can sort by date/time

Can you do a set by query instead of code?

2 Likes
7

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

1 Like
8

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

9

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

10

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);
  }
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
4 Likes
12

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

13

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…

2 Likes
14

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

15

Thank you so much. This worked great for me.