Loop through rows from BAQ in BPM

Epicor ERP 10
, , ,
1

Hi all!
I’m trying to do a BPM in which the ‘PickList Comment’ and two custom fields (‘L’ & ‘H’) of the Parent (of the kit) go on the childs.
I did a custom code on the BPM in which i use 3 BAQs for:

  • Recover all the info for the father (PartNum, Comment, L, H);
  • With the FatherPartNum I get the childs;
  • Finally I go in the order and for every child i copy Comment, L, H from the father.

And it should loop through the Fathers first and for every one of them loop between the childs and copy the info.

The problem is that I can’t seem to be able to loop every result of the BAQ. (none of those).

Ice.Contracts.DynamicQuerySvcContract boDynamicQuery = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(Db);

Ice.Tablesets.QueryExecutionTableset dsQueryExecution = new QueryExecutionTableset();

this.PublishInfoMessage(“OrderNum: " + OrderNum,Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual,”“,”");

ExecutionParameterRow drRow = new ExecutionParameterRow();
drRow.ParameterID = “OrderNum”; // name of parameter from BAQ
drRow.ParameterValue = OrderNum.ToString();
drRow.ValueType = “int”;
drRow.IsEmpty = false;
drRow.SysRowID = Guid.NewGuid();
drRow.RowMod = “A”;
dsQueryExecution.ExecutionParameter.Add(drRow);

DataSet GetFields = boDynamicQuery.ExecuteByID(“GetFatherInfoForChilds”, dsQueryExecution);
try
{

if (!String.IsNullOrEmpty(GetFields.Tables[0].Rows[0][0].ToString()))
{
for (int i = 0; i < GetFields.Tables[0].Rows.Count; i++)
{
PartNumFather = GetFields.Tables[0].Rows[i][1].ToString();
CommentFather = GetFields.Tables[0].Rows[i][3].ToString();
HFather = Convert.ToInt32(GetFields.Tables[0].Rows[i][5]);
LFather = Convert.ToInt32(GetFields.Tables[0].Rows[i][4]);

  this.PublishInfoMessage("PartNumFather: " + PartNumFather + "\nCommentFather: " + CommentFather + "\nHFather: " + HFather + "\nLFather: " + LFather + "\nRow Numbers: " + GetFields.Tables[0].Rows.Count,Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual,"","");
  
  Ice.Tablesets.QueryExecutionTableset dsQueryExecution1 = new QueryExecutionTableset();

  ExecutionParameterRow drRow1 = new ExecutionParameterRow();
    drRow1.ParameterID = "PartNum";  // name of parameter from BAQ
    drRow1.ParameterValue = PartNumFather;
    drRow1.ValueType = "nvarchar";
    drRow1.IsEmpty = false;
    drRow1.SysRowID = Guid.NewGuid();
    drRow1.RowMod = "A";
  dsQueryExecution1.ExecutionParameter.Add(drRow1);

  DataSet GetFields1 = boDynamicQuery.ExecuteByID("GetChildKitLine", dsQueryExecution1);
  
  if (!String.IsNullOrEmpty(GetFields1.Tables[0].Rows[0][0].ToString()))
  {
  
      this.PublishInfoMessage("Rev: " + Convert.ToInt32(GetFields1.Tables[0].Rows[0][1]) + "\nPartNumChild: " + Convert.ToInt32(GetFields1.Tables[0].Rows[0][0]) + "\nRow Numbers: " + GetFields1.Tables[0].Rows.Count,Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual,"","");
      
      int Rev = 0;
      for (int j = 0; j < GetFields1.Tables[0].Rows.Count; j++) 
      {
        if (j == 0)
        {
          Rev = Convert.ToInt32(GetFields1.Tables[0].Rows[j][1]);
        }
        PartNumChild = Convert.ToInt32(GetFields1.Tables[0].Rows[j][0]);
        RevNum = Convert.ToInt32(GetFields1.Tables[0].Rows[j][1]);
        if (RevNum == Rev)
        {
          this.PublishInfoMessage("Before last BAQ",Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual,"","");
          
          var jobrow1 = (from k in Db.OrderDtl where k.Company == Session.CompanyID && k.PartNum == PartNumChild.ToString() && k.OrderNum == OrderNum select k).FirstOrDefault();
          if(jobrow1 != null)
          {
            this.PublishInfoMessage("Comment: " + jobrow1.PickListComment + "\nLenght: " + jobrow1.Part_Lenght_c + "\nBase: " + jobrow1.Part_Base_c + "\nPart: " + jobrow1.PartNum,Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual,"","");
            
            jobrow1.PickListComment = CommentFather;
            jobrow1.Part_Lenght_c = LFather;
            jobrow1.Part_Base_c = HFather;
          }
        }
      }
  }  
}

}
}
catch
{

}

Can someone help me do the loop for the BAQs please?

2

When you say the “Father” are you referring to info from the Father’s Part record, or the Father’s OrderDtl record?

If you are talking about Sales Kits, each hidden line (the children of the kit) have a field that tells it which parent line the belong to.

Line  PartNum    KitParent
====  =======    =======
1     NK-101     1      // Non-kit part
2     KIT-101    2      // Sales kit part
3     CHLD101    2      // 1st child part of SK-101
4     CHLD123    2      // 2nd child part of SK-101
5     CHLD321    2      // 3rd child part of SK-101
6     NK-321     6      // Non-kit part

You could make a BPM that copies the Comment, L , and H values from lines where the KitParent <> the OrderLine

In the example above, Lines 3, 4, and 5 would have their Comment L and H values set to that of line 2.

This would require two BPMs. The first to set C, L, & H on the line of the parent part. And another to set them for the component parts.

3

I’d try to loop like this:

DataSet GetFields = boDynamicQuery.ExecuteByID(“GetFatherInfoForChilds”, dsQueryExecution);
try
{

if (GetFields.Tables[0] != null)
{
foreach (DataRow row in GetFields.Tables[0].Rows)
{
PartNumFather = row["TableName_FieldName"].ToString();  //i.e. OrderHed_OrderNum, etc - whats on top level of BAQ results
CommentFather = row["TableName_FieldName"].ToString();
}

Ultimately - I'd remove the try with empty catch block as you'll never know whats failing...
1 Like
4

Thanks all! I finally managed to get the loop right!

1 Like