Updating or adding new CustCnt with CustCnAttr

aarong · February 24, 2023, 11:10am

Hi All,

Anyone able to help me with this one?

I’m trying to update CustCn and the CustCnAttr table from a UBAQ dataset.

I’m getting a few errors. In the BAQ dataset we are getting the Attr as a comma delimited list

using(var txscope = IceContext.CreateDefaultTransactionScope()) {
            var vCustCnt = Db.CustCnt.Where(p =>
                p.Company == Session.CompanyID &&
                p.WebUserUID_c == row.UD02_WebUserUID_c
            ).FirstOrDefault();
        
            if (vCustCnt != null) {
                vCustCnt.FirstName = row.UD02_FirstName_c;
                vCustCnt.LastName = row.UD02_LastName_c;
                vCustCnt.EMailAddress = row.UD02_Email_c;
        
                // Split the comma-separated JobRoles_c string into an array of strings
                string[] jobRoles = row.JobRoles_c.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
        
                foreach (string jobRole in jobRoles) {
                    // Check if the job role already exists for the CustCnAttr
                    var custCnAttr = vCustCnt.CustCnAttr.FirstOrDefault(c =>
                        c.ConNum == row.UD02_Contact_c && c.JobRole_c == jobRole);
        
                    if (custCnAttr != null) {
                        // If the job role already exists, update it
                        custCnAttr.ModifiedByID = Session.UserID;
                        custCnAttr. = DateTime.Now;
                    }
                    else {
                        // If the job role does not exist, add it
                        vCustCnt.CustCnAttr.Add(new CustCnAttr {
                            CustNum = vCustCnt.CustNum,
                            ConNum = (Db.Customer.Where(r => r.Company == Session.CompanyID && r.CustID == row.UD02_CustID_c).Select(r => r.CustNum).DefaultIfEmpty(0).Max());,
                            JobRole_c = jobRole,
                            CreatedByID = Session.UserID,
                            CreatedDate = DateTime.Now
                        });
                    }
                
            }
            Db.Validate();
            txscope.Complete();
        }

aarong · February 24, 2023, 1:15pm

Changed up the code and tried a different angle but no luck…

              using (var custcntclient = Ice.Assemblies.ServiceRenderer.GetService<CustCntSvcContract>(Db))
                    {
                        CustCntTableset custcnt = new CustCntTableset();
                        var cnt = Db.CustCnt.Where(r => r.Company == Session.CompanyID && r.WebUserUID_c == row.UD02_WebUserUID_c).FirstOrDefault();
                    
                        if (cnt != null)
                        {
                            cnt.FirstName = row.UD02_FirstName_c;
                            cnt.LastName = row.UD02_LastName_c;
                            cnt.PhoneNum = row.UD02_Telephone_c;
                            cnt.AttrCodeList = row.UD02_JobRole_c.Trim(); // Trim the job roles and remove any blank spaces
                            cnt.RowMod = "U";
                    
                            custcnt.CustCnt.Add(cnt);
                    
                            custcntclient.Update(ref custcnt);
                        }

Any ideas?

aarong · February 26, 2023, 9:23pm

Anyone?

klincecum · February 26, 2023, 9:58pm

I’ll take a stab at it later if I can.

What kind of errors are you experiencing?

Edit–>Nevermind, I see them in the picture.

klincecum · February 26, 2023, 10:03pm

pull that cnt from the BO, not the Db context.

pass an actual CustCntRow to custcnt.CustCnt.Add();

The row types are not the same.

aarong · February 26, 2023, 10:03pm

Would love to see how you would approach this… its quite simple on paper.

I just want to update CustCnt and CustCnAttr from a ubaq… cant get it to work. Makes me angry…

klincecum · February 26, 2023, 10:03pm

look up

aarong · February 26, 2023, 10:04pm

Ill give that a smash tomorrow…

aarong · February 26, 2023, 10:09pm

Actually able to give me a rough code snippet… I want to make sure I understand what you mean

klincecum · February 27, 2023, 1:34am

I’ll try, I’m a few in, we’ll see what comes out.

klincecum · February 27, 2023, 2:40am

Ok, I’m about half drunk and stuck.

I got the first part working until I got to CustCnAttr.

That does not exist in the Db context or the Dataset from the BO.

I’m not sure if that has been moved to RoleCodes or Person/Contact.

Need fresh eyes or fresh head.

That being said, where did this code or approach you started with come from?

klincecum · February 27, 2023, 2:48am

I think I found the answer in this thread.

I’ll post some code in the morning if someone doesn’t beat me to it.

aarong · February 27, 2023, 10:40am

Working code if anyone is interested.

            using(Erp.Contracts.CustCntSvcContract svcCust = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.CustCntSvcContract>(Db))
              { 
               int vConNum = (Db.CustCnt.Where(r => r.Company == Session.CompanyID && r.WebUserUID_c == row.UD02_WebUserUID_c).Select(r => r.ConNum).DefaultIfEmpty(0).Max());
                Erp.Tablesets.CustCntTableset ccntTs = new Erp.Tablesets.CustCntTableset();
                ccntTs = svcCust.GetByID(row.UD02_CustNum_c, "", vConNum);
                if (ccntTs != null)
                {
                  foreach (var custCnt in ccntTs.CustCnt)
                  {
                    if (custCnt.Company == Session.CompanyID);    //callContextClient.CurrentCompany)
                    {
                      if (custCnt.ConNum == vConNum);   
                      {
                        //custCnt.AttrCodeList = row.UD02_JobRole_c.Trim();
                        custCnt.Inactive = true;
                        custCnt.NoContact = true;
                        /*The customer tableset doesn’t directly include CustAttr. Instead the entries in the table are in Customer.AttrCodeList. Apparently the Update method thinks that anything that is in this field should be added to erp.CustAttr. So, my solution is to set that field to "" when I update dsCustomer.*/
                        custCnt.AttrCodeList = "";
                        custCnt.RowMod = "U";
                        svcCust.Update(ref ccntTs);
                      }  
                    }  
                  }  
                }

aarong · February 27, 2023, 12:06pm

I thought I had it!

We make a change to the AttrCodeList… CustCnAttr already contains the previous AttrCode and won’t allow it to be added again. My JobRole_c contains that existing

Is there anyway to query the table and only add the AttrCode of which is missing?

        case "UPDATE":
            // Code to execute when switchVar equals "Update"
            Ice.Diagnostics.Log.WriteEntry("Processing update...");
            using (Erp.Contracts.CustCntSvcContract svcCust = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.CustCntSvcContract>(Db))
            {
                int vConNum = (Db.CustCnt.Where(r => r.Company == Session.CompanyID && r.WebUserUID_c == row.UD02_WebUserUID_c).Select(r => r.ConNum).DefaultIfEmpty(0).Max());
                Erp.Tablesets.CustCntTableset ccntTs = svcCust.GetByID(row.UD02_CustNum_c, "", vConNum);
                if (ccntTs != null)
                {
                    foreach (var custCnt in ccntTs.CustCnt)
                    {
                        if (custCnt.Company == Session.CompanyID)
                        {
                            if (custCnt.ConNum == vConNum)
                            {
                            
                                    custCnt.AttrCodeList = ""; // Add the attribute to AttrCodeList
                                    custCnt.FirstName = row.UD02_FirstName_c;
                                    custCnt.LastName = row.UD02_LastName_c;
                                    custCnt.EMailAddress = row.UD02_Email_c;
                                    custCnt.PhoneNum = row.UD02_Telephone_c;
                                    custCnt.RowMod = "U";
                                    svcCust.Update(ref ccntTs);
                                
                            }
                            if (custCnt.ConNum == vConNum && string.IsNullOrEmpty(custCnt.AttrCodeList))
                            {
                                custCnt.AttrCodeList = row.UD02_JobRole_c.Trim();
                                custCnt.RowMod = "U";
                                svcCust.Update(ref ccntTs);
                            }

                        }
                    }
                }

aarong · February 27, 2023, 1:58pm

Keep getting

System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_CustCnAttr'. Cannot insert duplicate key in object 'Erp.CustCnAttr'. The duplicate key value is (TEAGLE, 15394, , 4, OR).
The statement has been terminated.

aarong · February 27, 2023, 2:24pm

No luck with this method…

    // Code to execute when switchVar equals "Update"
            Ice.Diagnostics.Log.WriteEntry("Processing update...");
              string[] jobRoles = row.UD02_JobRole_c.Trim().Split('~'); // Split the job roles by tilde
              
              int vConNumj = (Db.CustCnt.Where(r => r.Company == Session.CompanyID && r.WebUserUID_c == row.UD02_WebUserUID_c).Select(r => r.ConNum).DefaultIfEmpty(0).Max());
              
              // Find the job roles that do not exist in CustCntAttr
              Ice.Diagnostics.Log.WriteEntry("Job Role checking - exisiting ");
                var jobRolesToAdd = jobRoles.Where(jobRole =>
                {
                    using(var txscope = IceContext.CreateDefaultTransactionScope())
                    {
                        var vCustAttr = Db.CustCnAttr.Where(x =>
                            x.Company == Session.CompanyID &&
                            x.CustNum == row.UD02_CustNum_c && 
                            x.ConNum == vConNumj && 
                            x.AttrCode == jobRole
                        ).FirstOrDefault();
                        
                        return vCustAttr == null && !row.UD02_JobRole_c.Contains(jobRole);
                       
                
                    }
                    
                });
                 continue;
              
              if (jobRolesToAdd.Any())
              {
                  using (Erp.Contracts.CustCntSvcContract svcCust = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.CustCntSvcContract>(Db))
                  {
                      int vConNum = (Db.CustCnt.Where(r => r.Company == Session.CompanyID && r.WebUserUID_c == row.UD02_WebUserUID_c).Select(r => r.ConNum).DefaultIfEmpty(0).Max());
                      Erp.Tablesets.CustCntTableset ccntTs = svcCust.GetByID(row.UD02_CustNum_c, "", vConNum);
                      if (ccntTs != null)
                      {
                          foreach (var custCnt in ccntTs.CustCnt)
                          {
                              if (custCnt.Company == Session.CompanyID && custCnt.ConNum == vConNum)
                              {
                              Ice.Diagnostics.Log.WriteEntry("Updating customer contact..");
                                 string vAttr = string.Join("~", jobRolesToAdd.Concat(custCnt.AttrCodeList.Split('~')).Distinct());
                                 
                                  // Append the job roles that do not exist in CustCntAttr to the existing AttrCodeList
                                  custCnt.AttrCodeList = string.Join("~", jobRolesToAdd.Concat(custCnt.AttrCodeList.Split('~')).Distinct());
                                  custCnt.FirstName = row.UD02_FirstName_c;
                                  custCnt.LastName = row.UD02_LastName_c;
                                  custCnt.EMailAddress = row.UD02_Email_c;
                                  custCnt.PhoneNum = row.UD02_Telephone_c;
                                  custCnt.RowMod = "U";
                                  svcCust.Update(ref ccntTs);
                                  Ice.Diagnostics.Log.WriteEntry("finished update... AttrCode List: {vAttr}");
                              }
                          }
                      }
                  
              }

aarong · February 27, 2023, 3:47pm

anyone?

klincecum · February 27, 2023, 4:27pm

Yes, Erp.CustAttr

compare to that

Unfortunately my help is limited, as I don’t have any data in my DB to
process against. I guess we don’t use it.

aarong · February 27, 2023, 5:13pm

Thanks for your help Kevin

We are using the using the Customer Contacts which uses CustCnAttr and CustAttr is for the top level Customer. I believe.

Hope someone who has experienced this before replies.

klincecum · February 27, 2023, 5:49pm

Sorry, jumped the gun.

There is also the Erp.CustCnAttr table you can do your compare on.

Topic		Replies	Views
WCF Update of CustCnt throws exception Epicor ERP 10	5	1629	November 1, 2016
BPM runtime caught an unexpected exception of 'UpdateException' type. Violation of PRIMARY KEY constraint 'PK_CustAttr' Kinetic ERP bpm	2	1381	January 26, 2023
Violation of PRIMARY KEY constraint 'PK_CustCnAttr' Epicor ERP 10	10	3755	April 27, 2018
Update of CustCnt Throws Exception in Data Directive 10.2.700 Epicor ERP 10 bpm	0	324	June 27, 2022
Can not a contact for a customer Epicor ERP 10 e10	1	553	June 20, 2019

Updating or adding new CustCnt with CustCnAttr

Related Topics