Code Help - ConstraintException

I am trying to write a DD on JobOper that will fill a field on save. I know that I am not doing something correctly but cannot figure out what. I keep on getting the following error with the following code.

Any thoughts?

Server Side Exception

BPM runtime caught an unexpected exception of ‘ConstraintException’ type.
See more info in the Inner Exception section of Exception Details.

Exception caught in: Epicor.ServiceModel

Error Detail

Correlation ID: 647ccd91-98ae-4c2f-ad73-913eb48e0675
Description: BPM runtime caught an unexpected exception of ‘ConstraintException’ type.
See more info in the Inner Exception section of Exception Details.
Program: EntityFramework.dll
Method: ThrowPropertyIsNotNullable
Original Exception Type: ConstraintException
Framework Method: set_CommentText
Framework Line Number: 1626809
Framework Column Number: 21
Framework Source: set_CommentText at offset 108 in file:line:column C:_Releases\ERP\RL10.2.500.0\Source\Server\Db\Erp.Data.910100\ErpContext.Model.cs:1626809:21

Client Stack Trace

at Epicor.ServiceModel.Channels.ImplBase`1.ShouldRethrowNonRetryableException(Exception ex, DataSet dataSets)
at Erp.Proxy.BO.OrderJobWizImpl.CreateJobs(OrderJobWizDataSet ds, String& pErrorMessages)
at Erp.Adapters.OrderJobWizAdapter.CreateJobs(String& pErrorMessages)
at Erp.UI.App.OrderJobWizEntry.Transaction.CreateJobs()

Inner Exception

This property cannot be set to a null value.

var oper = ttJobOper.Where(jo=>jo.Company == CompanyID && jo.Added())
                    .Select(jo=>jo).FirstOrDefault();

if (oper != null)
{
  if (oper.OprSeq != 10)
  {
    var ops = Db.JobOper.Where(jo=>jo.Company == oper.Company &&
                                jo.JobNum == oper.JobNum &&
                                jo.AssemblySeq == oper.AssemblySeq &&
                                jo.OprSeq != oper.OprSeq)
                        .Select(jo=>jo);
    
    if (ops != null)
    {
      int maxOp = ops.Max(x=>x.OprSeq);
      
      this.PublishInfoMessage(maxOp.ToString(), Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
      
      var priorRow = Db.JobOper.Where(jo=>jo.Company == oper.Company &&
                                      jo.JobNum == oper.JobNum &&
                                      jo.AssemblySeq == oper.AssemblySeq &&
                                      jo.OprSeq == maxOp)
                                .Select(jo=>jo).FirstOrDefault();

Can you share the entire bpm code? Seems cutoff.

Yeah, I know the error is happening where I cut it off. Sorry I forgot to mention. Here it is, I have still been trying things so the code has changed a little.

var oper = ttJobOper.Where(jo=>jo.Company == CompanyID && jo.Added())
                    .Select(jo=>jo).FirstOrDefault();

if (oper != null)
{
  if (oper.OprSeq != 10)
  {
    var ops = Db.JobOper.Where(jo=>jo.Company == oper.Company &&
                                jo.JobNum == oper.JobNum &&
                                jo.AssemblySeq == oper.AssemblySeq /*&&
                                jo.OprSeq != oper.OprSeq*/)
                        .Select(jo=>jo.OprSeq);
    
    if (ops != null)
    {
      int maxOp = Convert.ToInt32(ops.OrderByDescending(o=>o).Select(o=>o).Skip(1))
                      /*orderby os.OprSeq descending
                      select os.OprSeq).Distinct().Skip(1).First()*/;
      
      this.PublishInfoMessage(maxOp.ToString(), Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
      
      var priorRow = Db.JobOper.Where(jo=>jo.Company == oper.Company &&
                                      jo.JobNum == oper.JobNum &&
                                      jo.AssemblySeq == oper.AssemblySeq &&
                                      jo.OprSeq == maxOp)
                                .Select(jo=>jo).FirstOrDefault();

      if (priorRow != null)
      {
        var prOpDtl = Db.JobOpDtl.Where(jod=>jod.Company == priorRow.Company &&
                                        jod.JobNum == priorRow.JobNum &&
                                        jod.AssemblySeq == priorRow.AssemblySeq &&
                                        jod.OprSeq == priorRow.OprSeq &&
                                        jod.OpDtlSeq == priorRow.PrimaryProdOpDtl)
                                .Select(jod=>jod).FirstOrDefault();
                                
        if (prOpDtl != null)
        {
          if (prOpDtl.CapabilityID != null)
          {
            var cap = Db.Capability.Where(c=>c.Company == prOpDtl.Company &&
                                          c.CapabilityID == prOpDtl.CapabilityID)
                                  .Select(c=>c).FirstOrDefault();
            if (cap != null)
            {
              priorCell = cap.PrimaryResourceGrpID;
            }
          }
          else
          {
            if (prOpDtl.ResourceID != null)
            {
              var res = Db.Resource.Where(r=>r.Company == prOpDtl.Company &&
                                          r.ResourceID == prOpDtl.ResourceID)
                                    .Select(r=>r).FirstOrDefault();
              if (res != null)
              {
                priorCell = res.ResourceGrpID;
              }
            }
            else
            {
              priorCell = "X";
            }
          }
        }
        else { priorCell = "X"; }
      }
      else { priorCell = "X"; }
    }
    else { priorCell = "X"; }
  }
  else { priorCell = "X"; }
}
else { priorCell = "X"; }

Ok. I know the following works. Caveat is that it does not do what I want. I thought that the code would pull the Max OprSeq not including the current one that is being added. Instead, the Max OprSeq is actually the OprSeq that is currently being added.

var oper = ttJobOper.Where(jo=>jo.Company == CompanyID && jo.Added())
                    .Select(jo=>jo).FirstOrDefault();

if (oper != null)
{
  if (oper.OprSeq != 10)
  {
    var ops = Db.JobOper.Where(jo=>jo.Company == oper.Company &&
                                jo.JobNum == oper.JobNum &&
                                jo.AssemblySeq == oper.AssemblySeq)
                        .Select(jo=>jo.OprSeq);
    
    if (ops != null)
    {
      int maxOp = ops.Max();
      
      this.PublishInfoMessage(maxOp.ToString(), Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
      
      var priorRow = Db.JobOper.Where(jo=>jo.Company == oper.Company &&
                                      jo.JobNum == oper.JobNum &&
                                      jo.AssemblySeq == oper.AssemblySeq &&
                                      jo.OprSeq == maxOp)
                                .Select(jo=>jo).FirstOrDefault();

      if (priorRow != null)
      {
        var prOpDtl = Db.JobOpDtl.Where(jod=>jod.Company == priorRow.Company &&
                                        jod.JobNum == priorRow.JobNum &&
                                        jod.AssemblySeq == priorRow.AssemblySeq &&
                                        jod.OprSeq == priorRow.OprSeq &&
                                        jod.OpDtlSeq == priorRow.PrimaryProdOpDtl)
                                .Select(jod=>jod).FirstOrDefault();
                                
        if (prOpDtl != null)
        {
          if (prOpDtl.CapabilityID != null)
          {
            var cap = Db.Capability.Where(c=>c.Company == prOpDtl.Company &&
                                          c.CapabilityID == prOpDtl.CapabilityID)
                                  .Select(c=>c).FirstOrDefault();
            if (cap != null)
            {
              priorCell = cap.PrimaryResourceGrpID;
            }
          }
          else
          {
            if (prOpDtl.ResourceID != null)
            {
              var res = Db.Resource.Where(r=>r.Company == prOpDtl.Company &&
                                          r.ResourceID == prOpDtl.ResourceID)
                                    .Select(r=>r).FirstOrDefault();
              if (res != null)
              {
                priorCell = res.ResourceGrpID;
              }
            }
            else
            {
              priorCell = "X";
            }
          }
        }
        else { priorCell = "X"; }
      }
      else { priorCell = "X"; }
    }
    else { priorCell = "X"; }
  }
  else { priorCell = "X"; }
}
else { priorCell = "X"; }

I get pop ups of each operation being added except for op 10.

image

Now, if I change the following line, I get the ConstraintException message.

 int maxOp = ops.Max();

to

 int maxOp = ops.Max() - 10;

This is definitely above my head.

@jkane Is it possible you are looking too early? I use this query to get the last production step in JobEntry.Update without issues. If you are doing this in transaction would your code work in standard?

 LastProductionStep =  (from jo in Db.JobOper
                                              join jod in Db.JobOpDtl
                                              on new  { jo.Company, jo.JobNum, jo.AssemblySeq, jo.OprSeq } equals new { jod.Company, jod.JobNum, jod.AssemblySeq, jod.OprSeq }   
                                              where 
                                              jo.Company == ttJobHeadRow.Company && jo.JobNum == ttJobHeadRow.JobNum &&
                                              jod.Company == ttJobHeadRow.Company && jod.JobNum == ttJobHeadRow.JobNum && 
                                              jod.ResourceGrpID == rg
                                              orderby jo.OprSeq descending                                            
                                              select jo.OprSeq).FirstOrDefault();

I think the logic is too complicated. Try this:

var oper = ttJobOper.Where(jo=> jo.Added()).FirstOrDefault();

var priorRow = Db.JobOper.Where(jo=>jo.Company == oper.Company &&
                    jo.JobNum == oper.JobNum &&
                    jo.AssemblySeq == oper.AssemblySeq &&
                    jo.OprSeq != oper.OprSeq).OrderBy(jo=>jo.OprSeq)
                    .FirstOrDefault();
1 Like

Anytime I am trying to track down a null value, I start by placing message boxes everywhere to help debug my code. In this case I would try a message box before you assign a value to maxOp. something like:

MessageBox.Show(ops.Max());

@gpayne, I am using the Order Job Wizard to create these jobs and the JobEntry.Update method does not fire.

@Jason_Woods, I get where you are coming from and have tried a couple of things and it still is not working. I did figure out it does not like the following line. When I comment it out the code runs, but does not with it in it.

jo.OprSeq != thisOp
var oper = ttJobOper.Where(jo=>jo.Company == CompanyID && jo.Added())
                    .Select(jo=>jo).FirstOrDefault();

if (oper != null)
{
  int thisOp = oper.OprSeq;
  if (oper.OprSeq != 10)
  {
    var priorRow = Db.JobOper.Where(jo=>jo.Company == oper.Company &&
                                jo.JobNum == oper.JobNum &&
                                jo.AssemblySeq == oper.AssemblySeq )
                        .OrderByDescending(jo=>jo.OprSeq)
                        .Select(jo=>jo).FirstOrDefault();

      if (priorRow != null)
      {
       this.PublishInfoMessage(priorRow.OprSeq.ToString(), Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
        var prOpDtl = Db.JobOpDtl.Where(jod=>jod.Company == priorRow.Company &&
                                        jod.JobNum == priorRow.JobNum &&
                                        jod.AssemblySeq == priorRow.AssemblySeq &&
                                        jod.OprSeq == priorRow.OprSeq &&
                                        jod.OpDtlSeq == priorRow.PrimaryProdOpDtl)
                                .Select(jod=>jod).FirstOrDefault();
                                
        if (prOpDtl != null)
        {
          if (prOpDtl.CapabilityID != null)
          {
            var cap = Db.Capability.Where(c=>c.Company == prOpDtl.Company &&
                                          c.CapabilityID == prOpDtl.CapabilityID)
                                  .Select(c=>c).FirstOrDefault();
            if (cap != null)
            {
              priorCell = cap.PrimaryResourceGrpID;
            }
          }
          else
          {
            if (prOpDtl.ResourceID != null)
            {
              var res = Db.Resource.Where(r=>r.Company == prOpDtl.Company &&
                                          r.ResourceID == prOpDtl.ResourceID)
                                    .Select(r=>r).FirstOrDefault();
              if (res != null)
              {
                priorCell = res.ResourceGrpID;
              }
            }
            else
            {
              priorCell = "X";
            }
          }
        }
        else { priorCell = "X"; }
      }
      else { priorCell = "X"; }
  }
  else { priorCell = "X"; }
}
else { priorCell = "X"; }

@NateS, I do not believe the error is because of a null value, instead it is because it thinks there is a property that could be set as a null. At least that is my interpretation. I am able to get the maxOp to show in a message, which is how I know that it is pulling the current op being added. I need to figure out how to get the op prior.

I have a feeling that this is C# syntax related. As in I am not doing things correctly.

@josecgomez , @hkeric.wci can one of you take a quick look? The best that I can guess is that the results of the lambda query are not compatible with int and vice versa? So whenever I try to alter the OprSeq, it cannot be used in the Where clause. Or something like that. Any help is GREATLY appreciated.

@jkane ojw uses OrderJobWizard.CreateJobs post processing you have the job completely created. You don’t get JobOpDtl records until after scheduling so if you need the resource group the job has to have already been scheduled.

I was able to run my snipit to get the last operations there, so you should be able to loop thru the operations and set your values.

@gpayne Thank you for pointing out that the job is completely created in Post of the method. I’m going to play around with the Pre now.

Unless I am missing something, there are JobOpDtl records in the table just after creating an operation.

@jkane You are correct that JobOpDtl records are there. I was looking at the resources tab.

@gpayne There is no job data (other than JWJobHead) in the OrderJobWiz.CreateJobs dataset. That is why I went to the DD because there was nothing in the client trace that I could grab hold of and do anything with.

@jkane here is my test. once you get the JobNum from ttJWJobHead you should be able to get to your Ops to update them.



foreach (var tt in ttJWJobHead)

{

var rg = "INSP"; 

 var LastProductionStep =  (from jo in Db.JobOper
                                              join jod in Db.JobOpDtl
                                              on new  { jo.Company, jo.JobNum, jo.AssemblySeq, jo.OprSeq } equals new { jod.Company, jod.JobNum, jod.AssemblySeq, jod.OprSeq }   
                                              where 
                                              jo.Company == tt.Company && jo.JobNum == tt.JobNum &&
                                              jod.Company == tt.Company && jod.JobNum == tt.JobNum && 
                                              jod.ResourceGrpID == rg
                                              orderby jo.OprSeq descending                                            
                                              select jo.OprSeq).FirstOrDefault();


    
    string InfoMsg = string.Empty;
    InfoMsg = " last step is" + LastProductionStep.ToString();
    this.PublishInfoMessage(InfoMsg, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "FirstVar","SecondVar");




}

Gotcha, didn’t understand you the first time. Will try now

That is not going to work because I need to alter the JobOpDtl records based on the prior cell. Need to keep operations in the cell it started in pending other variables.

@jkane I took @Jason_Wood’s query and put it in a DD for JobOpDtl and what I found was amazing and also not useful. The JobOper and JobOpDtl records are added quickly and random which makes sense since they have no ordering until you schedule them. After that I made another test in post processing of ojw that was able to get and assign the prior OPs resource group. Mine were all the first Op since I do not have the capabilities in my system to check.



foreach (var tt in ttJWJobHead)

{
foreach (var JobOpDtlRow in Db.JobOpDtl.Where(JobOpDtl_Row => JobOpDtl_Row.JobNum == tt.JobNum  && (JobOpDtl_Row.ResourceGrpID ?? "") != "").OrderBy(jo => jo.OprSeq)) 

{
Ice.Diagnostics.Log.WriteEntry($"JobOpDtl  ttJobOPDTL seq {JobOpDtlRow.OprSeq} orig RG {JobOpDtlRow.ResourceGrpID} ");

var priorRow = Db.JobOpDtl.Where(jo=>jo.Company == JobOpDtlRow.Company &&
                    jo.JobNum == JobOpDtlRow.JobNum &&
                    jo.AssemblySeq == JobOpDtlRow.AssemblySeq &&
                    jo.OprSeq < JobOpDtlRow.OprSeq).OrderByDescending(jo=>jo.OprSeq)
                    .FirstOrDefault();
if(priorRow != null)
{
                    
     Ice.Diagnostics.Log.WriteEntry($"JobOp Update: after ttJobOPDTL orig RG {JobOpDtlRow.ResourceGrpID} prior {priorRow.ResourceGrpID}");
     
JobOpDtlRow.ResourceGrpID = priorRow.ResourceGrpID;
JobOpDtlRow.OpDtlDesc = priorRow.OpDtlDesc;
}
}
Ice.Diagnostics.Log.WriteEntry("Exit get prior jobopdtl ");
}

Things I hate:

  1. Epicor
  2. C#
  3. Myself
if (prOpDtl.ResourceID != null)

is not nullable because it is a string, needs to be

if (prOpDtl.ResourceID != "")