Direct DB Update, Update UD column, Epicor Function

Kinetic ERP
,
1

Following the lead of @klincecum this is in an Epicor Function and I’ve hit a brick wall with the appropriate Update method, so the only way I see forward is to do a direct DB update.

I need to update a value on CustCnt.SalesforceID_c. Unfortunately, when I attempt to do this with the CustCnt Update method, I get a pkey error for CustCnAttr. (There’s a separate post about this).

Looking over the solution for Direct DB Update, Add Row, Epicor Function - Cannot Add, am I missing something? I see a ray of light. I can set table ERP.CustCnt to Updateable in the function, but technically the table I want to update is ERP.CustCnt_UD.

I know that in SQL if I query erp.CustCnt, UD fields are not returned. dbo.CustCnt does include UD fields, but dbo.CustCnt is not a table that I can add to the function.

Can I directly update a UD column with an Epicor function?

1 Like
2

Should be able to.
You don’t have to do it in a function though, could be done in the BPM.

Before you go this route, did you try my suggestion of deleting the old and adding a new
record to work around your issue?

1 Like
3

Hold up.

I just opened up a BAQ, set it to update, and updated some stuff in CustCnt, no problem.

I think we’ve been overthinking this the whole time.

4

I’d prefer to avoid the deleting and recreating because I would have to hit the Salesforce API a second time after creating a new contact.

When I send a contact to Salesforce for the first time, the information includes the SysRowID so that identifier is available to Salesforce. I then write the SalesforceID number into the UD field so I have the primary identifier for the Salesforce record available for future updates.

If I delete the contact and then recreate it, I will need to send the new SysRowID to Salesforce. It’s workable, but not ideal.

I’m encapsulating all this in a function so that the Data Directive can loop through all the records in ttCustCnt and only call this function for records with RowMod = “U”.

5

Gotcha.

Now since I just learned that CustCnt can be updated just fine in a BAQ, then we should have no problem updating it.

I can show you how to update it direct db, or using the business object.

You should be able to use updateExt on Erp.BO.CustCnt with just the key fields and your UD field.

1 Like
6

I’ll give UpdateExt a try instead of Update to see if I get a more favorable result.

7

Not sure what you’re trying to do exactly, but the code below I tried in a function worked for me. It updated the Func field in a contact.

// test vars
int cstno = 1868;
string shiptono = "";
int contactno = 1;

// call service
Erp.Tablesets.CustCntTableset tsCCnt = new Erp.Tablesets.CustCntTableset();
this.CallService<Erp.Contracts.CustCntSvcContract>(ccc => {

  // get contact
  tsCCnt = ccc.GetByID(cstno, shiptono, contactno);

  // set vals
  tsCCnt.CustCnt[0].Func = "test 123";
  tsCCnt.CustCnt[0].RowMod = "U";

  // save
  ccc.Update(ref tsCCnt);

// end call service
});

LE: if it’s an UD field you’re trying to update, if the below doesn’t work:

tsCCnt.CustCnt[0].SalesforceID_c = "test 123";

try this one

tsCCnt.CustCnt[0]["SalesforceID_c"] = "test 123";
1 Like
8

OK, this is really interesting and I know exactly what you mean now. Looks like the function I posted earlier works only the first time per contact. If I run the function again for the same customer & contact, I get the same error as you. Looks like the update is trying to add a new row in the CustCnAttr table.

Your best bet is UpdateExt:

// test vars
int cstno = 22;
string shiptono = "";
int contactno = 1;
string cmy = "YourCompanyID";

// call service
Erp.Tablesets.UpdExtCustCntTableset tsCCnt = new Erp.Tablesets.UpdExtCustCntTableset(); 
this.CallService<Erp.Contracts.CustCntSvcContract>(ccc => {

  // create new row
  Erp.Tablesets.CustCntRow tsCCRw = new Erp.Tablesets.CustCntRow();
  tsCCRw.Company = cmy;
  tsCCRw.CustNum = cstno;
  tsCCRw.ShipToNum = shiptono;
  tsCCRw.ConNum = contactno;
  tsCCRw.Func = "Test123444";

  // add row to ts
  tsCCnt.CustCnt.Add(tsCCRw);
  
  // save and get result
  Ice.BOUpdErrorTableset tsRez = new Ice.BOUpdErrorTableset(); 
  bool haserr;
  tsRez = ccc.UpdateExt(ref tsCCnt, true, true, out haserr);

// end call service
});
2 Likes
9

Thanks to @klincecum and @Dragos for your input. Using UpdateExt hadn’t occurred to me. I think there is something fundamentally off about CustCnt.Update and its handling of CustCntAttr, but UpdateExt solved my problem, now I can move on to find my next challenge/learning experience.

1 Like