Updating table through bpm code

Hi,
I’m having trouble figuring out how to update a table through bpm code. Any ideas on how to do this?

Do you already have a copy of the Epicor ICE Tools User Guide?
The Business Process Management section might help with the basics.

Can you be more specific?

1 Like

Be careful. My server event logs are flooded with database deadlocks and transaction aborted errors, and I believe it has something to do with trying to update the database in BPMs. I can change one type of error to another by changing how the BPM tries to update the database, but I haven’t been able to get rid of them. I’m actually in the middle of reviewing all my BPMs right now. I’m planning to convert method directives into data directives whenever possible, and use the set field widgets instead of custom code.

In pre-processing method directives, you don’t need to save changes. That’s the point of “pre”. You can set fields in the temp tables before the default functionality ever sees the data. My predecessor taught me to use Db.Validate() and/or Db.SaveChanges() in post directives. We actually have a couple BPMs that do this, written in 2010 by someone with an epicor.com email address. But recently, knowledgeable people on this forum have told me this is the wrong way to do it. The docs say to make your changes in a transaction scope created by IceDataContext.CreateDefaultTransactionScope(), calling Complete() on the scope when you’re finished. But experimenting with this has only changed my deadlocks into transaction aborted errors.

I end up just calling the appropriate business objects in a post, seems to be the most clean and “kosher” way of updating data.

1 Like

one really good way to add/update tables is to use the process’s UpdateExt method. It allows you to create a tableset for an entire order, and then call SalesOrder.UpdateExt, and the order will be added/updated.

Here is some sample code from a BPM that will automatically add/update to UD12.

    using (var svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.UD12SvcContract>())
      {
      
      
      //Build and add the UD12 record
      var UD12 = new Ice.Tablesets.UD12Row
        {
          Company = ordRel.Company,
          Key1 = ordRel.OrderNum.ToString(),
          Key2 = "1",
          Key3 = "",
          Key4 = "",
          Key5 = "",
          ShortChar01 = "This is a base field",
        };
        //now the ud fields
        UD12.SetUDField<System.String>("MyUDField1_c", "some data");
        UD12.SetUDField<System.String>("MyOtherField_c, "more data");
       
        //add the record to our dataset
        ds.UD12.Add(UD12);
        
        BOUpdErrorTableset boUpdateErrors = svc.UpdateExt(ref ds, false,true, out errorOccurred);
        if (errorOccurred) {do something}; else {do something else};
           
      }
7 Likes

Any functional advantage to using the SetUDField methods versus just setting the row via name?

You actually need both the transaction scope and Db.Validate();

Yes, I forgot about that. However, I knew it when I wrote the BPM. :slight_smile: It does call Validate() as shown in the docs, but the transactions still intermittently abort.

Oh, so that’s what UpdateExt is for. You say we can do this with an order? No more building it line by line? :astonished:

I was able to get it to update. But do I have to include Db.Validate() in this case?

MemoTableset memoTS = new MemoTableset();
	vh_Memo.GetNewMemo(ref memoTS, "Erp", "OrderHed", orderNum.ToString(), "", "", DateTime.Now);
	vh_Memo.Update(ref memoTS);

	var OrderHed_xRow =
		(from OrderHed_Row in Db.OrderHed
		where OrderHed_Row.OrderNum == orderNum
		select OrderHed_Row).FirstOrDefault();

	if (OrderHed_xRow != null) {

		var sysRowID = new Guid((OrderHed_xRow.SysRowID).ToString());
		string orderNumString = orderNum.ToString();

		var Memo_xRow =
			(from Memo_Row in Db.Memo
			where Memo_Row.Key1 == orderNumString
			orderby Memo_Row.MemoDate descending
			select Memo_Row).FirstOrDefault();

		if( Memo_xRow != null) { 
			Memo_xRow.RelatedToSysRowID = sysRowID;
			this.PublishInfoMessage(orderNumString + " " + (Memo_xRow.RelatedToSysRowID).ToString() + "  " + (Memo_xRow.MemoDate).ToString(), Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
		}

	}
	vh_Memo.Update(ref memoTS);

I confirmed that calling Db.SaveChanges() was causing the deadlock errors I warned about. Db is an EF DbContext, and calling SaveChanges() is a normal part of working with a DbContext in other environments. The guy who wrote most of our BPMs was experienced with using EF in other environments. But BPM custom code is called within a transaction, and calling SaveChanges() apparently corrupts the transaction. Only call Validate(), which as I understand it, basically causes the DbContext to notice that the record changed.

1 Like

UpdateEXT is (as I look at it) a wrapper that results in calling the main Update program… so if you use Part.UpdateExt, it results in calling part.Update. but the UpdateExt version also populates any required fields. This allows you to populate a minimum number of fields and then call the UpdateExt version. WARNING, this does NOT always populate everything needed…
But Yes, you can create/update an entire order with one UpdateEXT call. You can also update an entire list of records with one call… so if you want to modify a list of JobHead records, you can populate the required fields, and it will update for you. It 1. read the record (if available) and then 2. updates it.

1 Like

I have this code how can I update AutoPrint column? I am going by reading object but get error these columns are read only.

 var invcgrp = (from invc in Db.InvcGrp                                
                                where invc.Company == Company && invc.GroupID.ToUpper() == GroupID
                                select new
                                {
                                    Company = invc.Company,                                   
                                    GroupID = invc.GroupID,
                                    AutoPrint = invc.AutoPrintReady
                                }).ToList();
                
                  foreach (var item in invcgrp)
                {
                item.AutoPrint = true;
                Db.Validate();
                Db.SaveChanges();
                }