BPM Code to add Parent/Child UD1XX/UD1XXA data

JimF · June 27, 2019, 4:32pm

I am trying to create (insert) records as a parent/child relationship. For the specifics: I am trying to trap the save from the Inspection Processing screen (as a pre-process BPM), and write the appropriate data to UD105/UD105A. UD105 will contain the inspection data, and UD105A will contain the associated serial numbers of the parts that have been inspected. I am sure there is a “more correct” way to do this that won’t produce an error, but when I trigger the below code, I get a SQL constraint error: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_UD105A_UD105”.

It seems that the line:
Db.UD105A.Insert(newUD105ARow);
is causing the error.

Thanks in advance!

int counter = 0;
int InspectionBatchNumber = 0;
string SerialLotTKLotNumber = string.Empty;
string StepName = string.Empty;
string UserID = string.Empty;
List<string> SerialNos = new List<string>() { };
try
{
  using (var txScope = IceContext.CreateDefaultTransactionScope())
  {
    StepName = "Update UD11";
    // Get next PutAway Batch Number
    foreach (var UD11 in (from UD11Row in Db.UD11.With(LockHint.UpdLock) where UD11Row.Key1 == "PutAway" select UD11Row))
    {
      InspectionBatchNumber = System.Convert.ToInt32(UD11.Number01);
      InspectionBatchNumber++;
      UD11.Number01 = System.Convert.ToDecimal(InspectionBatchNumber);
      Db.Validate();
    }
    StepName = "Get Serial Numbers";
    SerialNos.Clear();
    foreach (var ttSN in (from row in ttSelectedSerialNumbers select row))
    {
      SerialNos.Add(ttSN.SerialNumber);
    }
    StepName = "Update UD105 Inspection";
    SerialLotTKLotNumber = string.Empty;
    counter= 0;
    foreach (var ttIR in (from row in ttInspRcpt where row.RowMod == "U" select row))
    {
      if (counter == 0)
      {
        if (SerialNos.Count > 0)
          SerialLotTKLotNumber = SerialNos[0];
        else if (ttIR.LotNum.ToString() != "")
          SerialLotTKLotNumber = ttIR.LotNum.ToString();
        else if (ttIR.UDField<string>("TK_LotNumber_c") != "")
          SerialLotTKLotNumber = ttIR.UDField<string>("TK_LotNumber_c");
        counter = 1;
      }
      UD105 newUD105Row = new UD105();
      newUD105Row.Company = Session.CompanyID;
      newUD105Row.Key1 = InspectionBatchNumber.ToString();
      newUD105Row.Key2 = string.Empty;
      newUD105Row.Key3 = string.Empty;
      newUD105Row.Key4 = string.Empty;
      newUD105Row.Key5 = string.Empty;
      newUD105Row.UserID_c = Session.UserID == null ? string.Empty : Session.UserID;
      newUD105Row.PartNumber_c = ttIR.PartNum.ToString();
      newUD105Row.PackSlip_c = ttIR.PackSlip.ToString();
      newUD105Row.PONum_c = ttIR.PONum;
      newUD105Row.POLine_c = ttIR.POLine;
      newUD105Row.InspectedBy_c = ttIR.InspectorIDName;
      newUD105Row.InspectionDate_c = ttIR.InspectedDate;
      newUD105Row.PORelNum_c = ttIR.PORelNum;
      newUD105Row.ReceiptDate_c = ttIR.ReceiptDate;
      newUD105Row.PackSlipLine_c = ttIR.PackLine;
      newUD105Row.Revision_c = ttIR.RevisionNum;
      newUD105Row.PartDescription_c = ttIR.PartDescription;
      newUD105Row.VendorName_c = ttIR.VendorNumName;
      newUD105Row.VendorNumber_c = ttIR.VendorNum;
      newUD105Row.ReceivedQty_c = System.Convert.ToInt32(ttIR.OurQty);
      newUD105Row.QtyInspected_c = ttIR.UDField<int>("QuantityInspected_c");
      newUD105Row.QtyPassed_c = System.Convert.ToInt32(ttIR.PassedQty);
      newUD105Row.QtyRejected_c = System.Convert.ToInt32(ttIR.FailedQty);
      newUD105Row.SerialLotNumber_c = SerialLotTKLotNumber;
      newUD105Row.FileName_c = string.Format("{0} SN{1} PL{2} {3}", ttIR.PartNum, SerialLotTKLotNumber, ttIR.PackSlip, ttIR.PONum.ToString());
      Db.UD105.Insert(newUD105Row);
      Db.Validate();
    }
    StepName = "Update UD105A Serial Numbers";
    foreach (string sn in SerialNos)
    {
      UD105A newUD105ARow = new UD105A();
      newUD105ARow.Company = Session.CompanyID;
      newUD105ARow.Key1 = InspectionBatchNumber.ToString();
      newUD105ARow.Key2 = string.Empty;
      newUD105ARow.Key3 = string.Empty;
      newUD105ARow.Key4 = string.Empty;
      newUD105ARow.Key5 = string.Empty;
      newUD105ARow.ChildKey1 = sn;
      newUD105ARow.ChildKey2 = string.Empty;
      newUD105ARow.ChildKey3 = string.Empty;
      newUD105ARow.ChildKey4 = string.Empty;
      newUD105ARow.ChildKey5 = string.Empty;
      Db.UD105A.Insert(newUD105ARow);
      Db.Validate();
    }
    txScope.Complete();
  }
}
catch (Exception ex)
{
  string msg = string.Format("Error during {0} step of LoadPutAwayBatch Pre-Processing directive.\r\nError Message: {1}.", StepName, ex.Message);
  this.PublishInfoMessage(msg,Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual,"",""); 
}

josecgomez · June 27, 2019, 5:05pm

You are bypassing the business objects
Use the business objects whenever possible.

josecgomez · June 27, 2019, 5:07pm

There are several examples in the forum I’ll try to find one but start searching for GetaNewUD and you should see some.

Not at my pc

JimF · June 27, 2019, 5:08pm

I didn’t think that the UD tables were as much of a concern, but I understand your point. Thanks.

JimF · June 27, 2019, 5:09pm

Will do. Thank you Jose.

josecgomez · June 27, 2019, 5:16pm

Sure but you are bypassing the BPM engine you never know

knash · June 27, 2019, 5:59pm

Did you try to do this via widgets vs Custom code? I only ask as we were able to automate a bunch of steps using only widgets.

josecgomez · June 27, 2019, 7:50pm

Creating a new UDXX Recprd

using(var UD02svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.UD02SvcContract>(Db))
{
    UD02Tableset ds = new UD02Tableset();
	UD02svc.GetaNewUD02(ref ds);
	ds.UD02[0].Key1 = table;
	ds.UD02[0].Key2 = action;
	ds.UD02[0].Key3 = custNum;
	UD02svc.Update(ref ds);
}

JimF · June 27, 2019, 8:47pm

Thanks Jose. I have already started using similar code after you suggested looking for GetaNewUD. The problem is still not as much writing to the Parent (UD105) as it is writing the records to the associated child (UD105A). I am getting there, but if you have any samples for that, you could shortcut me tremendously.

I appreciate your time and help!

josecgomez · June 27, 2019, 8:51pm

The code is the same except you use

  ud100Svc.GetaNewUD100A(ref ud100NewDS,"JOB",job.JobNum,string.Empty,string.Empty, ud100HeadNew.Key5 );
   var ud100ADtlNew = ud100NewDS.UD100A.Where(r=>r.RowMod=="A").FirstOrDefault();
   ud100ADtlNew.ChildKey1="Larry";

Where you pass in the data-set and the 5 Parent Keys

JimF · June 27, 2019, 9:03pm

Perfect!

One last question: Per your advice on another thread, I created UD Fields to be more descriptive on the UD105 table (see my code above). I can read from them, but don’t have code yet to write to them from the BPM. They don’t fit the ds.UD02[0].XXXX = data model as the base dataset doesn’t recognize the UD Fields.

josecgomez · June 27, 2019, 9:04pm

ds.UD02[0]["CustomField_ud"]

JimF · June 27, 2019, 9:13pm

OK, you didn’t have to make it look that easy.

Thank you, sir. You and your site are a Godsend to the Epicor development community.

As a sidenote, I will post my final code (that works) here for future seekers. Lots of good stuff in there and hopefully it will shortcut others as much as it has me.

JimF · July 1, 2019, 3:47pm

As promised, here is the final code. As a sidenote, it is not working 100% (Required field exception), but I don’t believe it is due to the code structure, so I add it here for others to glean things I searched to find.

EDIT - below is the final code I am using and it all works. If you find the code useful in any way, hit Like at the bottom to encourage me and others to share more helpful source code here.

int counter = 0;
int InspectionBatchNumber = 0;
string SerialLotTKLotNumber = string.Empty;
string StepName = string.Empty;
string UserID = string.Empty;
List<string> SerialNos = new List<string>() { };
try
{
  using (var txScope = IceContext.CreateDefaultTransactionScope())
  {
    StepName = "Update UD11";
    // Get next PutAway Batch Number
    foreach (var UD11 in (from UD11Row in Db.UD11.With(LockHint.UpdLock) where UD11Row.Key1 == "PutAway" select UD11Row))
    {
      InspectionBatchNumber = System.Convert.ToInt32(UD11.Number01);
      InspectionBatchNumber++;
      UD11.Number01 = System.Convert.ToDecimal(InspectionBatchNumber);
      Db.Validate();
    }
    StepName = "Get Serial Numbers";
    SerialNos.Clear();
    foreach (var ttSN in (from row in ttSelectedSerialNumbers select row))
    {
      if (!SerialNos.Contains(ttSN.SerialNumber))
        SerialNos.Add(ttSN.SerialNumber);
    }
    StepName = "Update UD105 Inspection";
    SerialLotTKLotNumber = string.Empty;
    counter= 0;
    using (var UD105 = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.UD105SvcContract>(Db))
    {
      foreach (var ttIR in (from row in ttInspRcpt where row.RowMod == "U" select row))
      {
        if (counter == 0)
        {
          if (SerialNos.Count > 0)
            SerialLotTKLotNumber = SerialNos[0];
          else if (ttIR.LotNum.ToString() != "")
            SerialLotTKLotNumber = ttIR.LotNum.ToString();
          else if (ttIR.UDField<string>("TK_LotNumber_c") != "")
            SerialLotTKLotNumber = ttIR.UDField<string>("TK_LotNumber_c");
          counter = 1;
        }
        var tsUD105 = new UD105Tableset();
        UD105.GetaNewUD105(ref tsUD105);
        tsUD105.UD105[0].Company = Session.CompanyID;
        tsUD105.UD105[0].Key1 = InspectionBatchNumber.ToString();
        tsUD105.UD105[0].Key2 = string.Empty;
        tsUD105.UD105[0].Key3 = string.Empty;
        tsUD105.UD105[0].Key4 = string.Empty;
        tsUD105.UD105[0].Key5 = string.Empty;
        tsUD105.UD105[0]["UserID_c"] = Session.UserID == null ? string.Empty : Session.UserID;
        tsUD105.UD105[0]["PartNumber_c"] = ttIR.PartNum.ToString();
        tsUD105.UD105[0]["PackSlip_c"] = ttIR.PackSlip.ToString();
        tsUD105.UD105[0]["PONum_c"] = ttIR.PONum;
        tsUD105.UD105[0]["POLine_c"] = ttIR.POLine;
        tsUD105.UD105[0]["InspectedBy_c"] = ttIR.InspectorIDName;
        tsUD105.UD105[0]["InspectionDate_c"] = ttIR.InspectedDate;
        tsUD105.UD105[0]["PORelNum_c"] = ttIR.PORelNum;
        tsUD105.UD105[0]["ReceiptDate_c"] = ttIR.ReceiptDate;
        tsUD105.UD105[0]["PackSlipLine_c"] = ttIR.PackLine;
        tsUD105.UD105[0]["Revision_c"] = ttIR.RevisionNum;
        tsUD105.UD105[0]["PartDescription_c"] = ttIR.PartDescription;
        tsUD105.UD105[0]["VendorName_c"] = ttIR.VendorNumName;
        tsUD105.UD105[0]["VendorNumber_c"] = ttIR.VendorNum;
        tsUD105.UD105[0]["ReceivedQty_c"] = System.Convert.ToInt32(ttIR.OurQty);
        tsUD105.UD105[0]["QtyInspected_c"] = ttIR.UDField<int>("QuantityInspected_c");
        tsUD105.UD105[0]["QtyPassed_c"] = System.Convert.ToInt32(ttIR.PassedQty);
        tsUD105.UD105[0]["QtyRejected_c"] = System.Convert.ToInt32(ttIR.FailedQty);
        tsUD105.UD105[0]["SerialLotNumber_c"] = SerialLotTKLotNumber;
        tsUD105.UD105[0]["FileName_c"] = string.Format("{0} SN{1} PL{2} {3}", ttIR.PartNum, SerialLotTKLotNumber, ttIR.PackSlip, ttIR.PONum.ToString());
        UD105.Update(ref tsUD105);
      }
      StepName = "Update UD105A Serial Numbers";
      foreach (string sn in SerialNos)
      {
        var tsUD105A = new UD105Tableset();
        UD105.GetaNewUD105A(ref tsUD105A, InspectionBatchNumber.ToString(), string.Empty, string.Empty, string.Empty, string.Empty);
        var UD105ANew = tsUD105A.UD105A.Where(r=>r.RowMod=="A").FirstOrDefault();
        UD105ANew.Company = Session.CompanyID;
        UD105ANew.ChildKey1 = sn;
        UD105ANew.ChildKey2 = string.Empty;
        UD105ANew.ChildKey3 = string.Empty;
        UD105ANew.ChildKey4 = string.Empty;
        UD105ANew.ChildKey5 = string.Empty;
        UD105.Update(ref tsUD105A);
      }
    }
    txScope.Complete();
  }
}
catch (Exception ex)
{
  string msg = string.Format("Error during {0} step of LoadPutAwayBatch Pre-Processing directive.\r\nError Message: {1}.", StepName, ex.Message);
  this.PublishInfoMessage(msg,Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual,"",""); 
}

KevinK · October 22, 2021, 3:44pm

What’s the namespace of UDxxTableset?

josecgomez · October 22, 2021, 4:38pm

Ice.Tablesets.