10.2.200.13 -- Method Directive "Fill Table by Query" for Order Lines referencing Parts

tfenwick11 · October 25, 2018, 2:21pm

Task:
-Upon users entering a new line for an order. Users wish to see a note field on part entry be carried over to the order line screen for reference.

Steps Taken:

Created a new UD field on ERP.PART_UD and regenerated database
Added UD field to part entry comments tab
Referenced one part and added a value for the new UD field
Established ERP.ORDERDTL_UD has a field “Character03” I can reference for the data insert
Created a pre-process method directive on ERP.SalesOrder
Applied condition to method directive for added row in order detail table
Utilized “Fill Table by Query” to associate ttOrderDtl to ERP.PART
Added message notifications to see whether directive was passing/failing

With the directive enabled and creating a new order and associating a new line with the part that has the new UD field “CSNOTES_c” populated and hitting tab or clicking save. It seems the directive is not firing as I am not receiving notifications?

tfenwick11 · October 25, 2018, 2:24pm

@Chris_Conn I did leave out the “Fill by Query” and utlized a notification for true/false and seems no output is displayed. Maybe I’m using the wrong method for this? Should I try the “ChangePartNumMaster” instead of “ChangePartNum”?

Chris_Conn · October 25, 2018, 2:26pm

You’ll want to run a trace and perform the actions you want to be the trigger, then look at the calls being made.

tfenwick11 · October 25, 2018, 2:31pm

With trying the method “ChangePartNumMaster”, it seems I am now receiving the pass notification after my fillbyquery task. But I do not see the field being updated, tracing the outcome it seems “CSNOTES_C” is not referenced in the log and CHARACTER03 is empty. I rechecked my query and binding and everything looks OK. Should I use “Set by Query” or “Update Table by Query” instead?

Erp.Proxy.BO.SalesOrderImpl
ChangePartNumMaster

   <RowMod></RowMod>
      <Character01></Character01>
      <Character02></Character02>
      <Character03></Character03>


<changedValue tableName="OrderDtl" rowState="Added" rowNum="0" colName="RowMod"><![CDATA[A]]></changedValue>
  <changedValue tableName="OrderDtl" rowState="Added" rowNum="0" colName="Character01"><![CDATA[]]></changedValue>
  <changedValue tableName="OrderDtl" rowState="Added" rowNum="0" colName="Character02"><![CDATA[]]></changedValue>
  <changedValue tableName="OrderDtl" rowState="Added" rowNum="0" colName="Character03"><![CDATA[]]></changedValue>

tfenwick11 · October 25, 2018, 7:08pm

@Chris_Conn @Charles.Dingas Thanks for the help so far, with reworking Charles’s code the syntax passes correctly and with the BPM enabled. I receive the notification after the custom code as seen below. But it seems data is not being inserted into the “CSNotes_c” field.

Blockquote
Erp.Tables.Part Part;
foreach(var ttOrderDtl_iterator in (from ttOrderDtl_Row in ttOrderDtl
where string.Equals(ttOrderDtl_Row.RowMod, IceRow.ROWSTATE_ADDED, StringComparison.OrdinalIgnoreCase) || string.Equals(ttOrderDtl_Row.RowMod, IceRow.ROWSTATE_UPDATED, StringComparison.OrdinalIgnoreCase)
select ttOrderDtl_Row))
{
var ttOrderDtlRow = ttOrderDtl_iterator;
{
/*this.PublishInfoMessage(“FoundPartDtl”, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, “”, “”);
*/
foreach (var Part_iterator in (from Part_Row in Db.Part
where string.Compare(Part_Row.Company, ttOrderDtlRow.Company, true) == 0 &&
Part_Row.PartNum == ttOrderDtlRow.PartNum
select Part_Row))
if (Part_iterator != null)
{
if(Part_iterator.CSNotes_c != null)
{
/*this.PublishInfoMessage(“FoundOrderDtl”, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, “”, “”);
*/
Part = Part_iterator;
ttOrderDtlRow[“Character03”] = Part.CSNotes_c;
}
}
}
}

Chris_Conn · October 25, 2018, 7:21pm

Just to make it more clear, I’d write it like this I think.

Erp.Tables.Part Part;
foreach(var ttOrderDtlRow in ttOrderDtl.Where(d => d.Added() || d.Updated()))
{

var Part = Db.Part.Where(p => p.Company == ttOrderDtlRow.Company &&
p.PartNum == ttOrderDtlRow.PartNum).FirstOrDefault();

if (Part != null)
 {
  if(Part.CSNotes_c != null)
  {
  /*this.PublishInfoMessage(“FoundOrderDtl”, Ice.Common.BusinessObjectMessageType.Information, 
  Ice.Bpm.InfoMessageDisplayMode.Individual, “”, “”);
 */

  ttOrderDtlRow[“Character03”] = Part.CSNotes_c;
 }
}
}

Also, since this method doesnt explictly save a record, you’ll need to:
Get and modify the base detail record
Call Db.Validate() after you mod the record to commit it

To get the proper record, something like this is probaby the quickest (just looking for the SysRow unique id):

var myDtl = Db.OrderDtl.Where(d => d.SysRowID == ttOrderDtlRow.SysRowID).FirstOrDefault();
if(myDtl != null)
{
  myDtl.Character03 = Part.CSNotes_c;
  Db.Validate();
}

As an afterthought, I wonder if the record actually already existing the DB at this point? I guess you’ll find out if this doesnt work lol. We can cross that bridge if we get to it.

tfenwick11 · October 25, 2018, 7:55pm

Each time I have been testing iv made sure its a new order with a new line based on the part with the note. Fixed the syntax error, had to rename “var Part” as “var myPart”

Chris_Conn · October 25, 2018, 7:57pm

Ah yea I dont know why that top line is there. Remove it.

tfenwick11 · October 25, 2018, 7:59pm

I thought you had to declare the ERP table prehand to using “DB”.table

Chris_Conn · October 25, 2018, 8:00pm

Also, I’d move your var myDtl stuff within the brackets that check for data to be written (just below ttOrderDtlRow[“Character03”] = myPart.CSNotes_c)

Nope.

bmgarver · October 25, 2018, 8:02pm

Part is defined as both a table and a table row…

Charles.Dingas · October 25, 2018, 8:29pm

Are you doing it as a post directive?

Charles

tfenwick11 · October 26, 2018, 11:41am

@Charles.Dingas pre process, did you go with post?

Charles.Dingas · October 26, 2018, 11:49am

Post process…

tfenwick11 · October 26, 2018, 11:52am

Switched it to post, got it working. Thanks a bunch!

Here is the finalized code:

Blockquote
Erp.Tables.Part Part;
foreach(var ttOrderDtl_iterator in (from ttOrderDtl_Row in ttOrderDtl
where string.Equals(ttOrderDtl_Row.RowMod, IceRow.ROWSTATE_ADDED, StringComparison.OrdinalIgnoreCase) || string.Equals(ttOrderDtl_Row.RowMod, IceRow.ROWSTATE_UPDATED, StringComparison.OrdinalIgnoreCase)
select ttOrderDtl_Row))
{
var ttOrderDtlRow = ttOrderDtl_iterator;
{
/*this.PublishInfoMessage(“FoundPartDtl”, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, “”, “”);
*/
foreach (var Part_iterator in (from Part_Row in Db.Part
where string.Compare(Part_Row.Company, ttOrderDtlRow.Company, true) == 0 &&
Part_Row.PartNum == ttOrderDtlRow.PartNum
select Part_Row))
if (Part_iterator != null)
{
if(Part_iterator.CSNotes_c != null)
{
/*this.PublishInfoMessage(“FoundOrderDtl”, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, “”, “”);
*/
Part = Part_iterator;
ttOrderDtlRow["Character03"] = Part.CSNotes_c;
}
}
}
}

Chris_Conn · October 26, 2018, 1:15pm

Ah, I bet there are some fields that arent populated proper until after the method runs… namely PartNum

Charles.Dingas · October 30, 2018, 6:40pm

@tfenwick11, glad it worked.