Call BAQ from BPM with more than one parameter

livingstonmarc · February 1, 2022, 1:54pm

I am trying to call a BAQ that has 2 parameters from BPM custom code

Below is my sample. It is returning 0 rows, though when I run the BAQ manually with the parameters it returns 1 row.

if (tQuery != null)
{    
  Ice.Tablesets.DynamicQueryTableset dsQuery = tQuery.GetByID("KNI-LastCompletedTask");    
  if (dsQuery != null)                    
  {                          
    Ice.Tablesets.QueryExecutionTableset dsBAQ = tQuery.GetQueryExecutionParameters(dsQuery);                
    dsBAQ.ExecutionParameter[0].ParameterID = "Project";  
    dsBAQ.ExecutionParameter[0].IsEmpty = false;
    dsBAQ.ExecutionParameter[0].ParameterValue = vttprojecttask.ProjectID.ToString() ;          
    dsBAQ.ExecutionParameter[0].ParameterID = "Task";  
    dsBAQ.ExecutionParameter[0].IsEmpty = false;
    dsBAQ.ExecutionParameter[0].ParameterValue = vttprojecttask.TaskID.ToString();      
    DataSet results = tQuery.Execute(dsQuery, dsBAQ); 
    if (results != null && results.Tables.Count > 0 && results.Tables[0].Rows.Count > 0)
    {
      DataRow row = results.Tables[0].Rows[0];          
      vStart = DateTime.Parse(row["ProjectTask_DateComplete"].ToString()) ; 
      this.PublishInfoMessage(row["ProjectTask_TaskID"].ToString(), Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "FirstVar","SecondVar");
      vstarttime= Int32.Parse(row["ProjectTask_TimeCompleted_c"].ToString());
    }                        
    dsBAQ = null;                    
  }
    
dsQuery = null;                      

}

CSmith · February 1, 2022, 2:02pm

livingstonmarc:

dsBAQ.ExecutionParameter[0].ParameterID = "Task";  
    dsBAQ.ExecutionParameter[0].IsEmpty = false;
    dsBAQ.ExecutionParameter[0].ParameterValue = vttprojecttask.TaskID.ToString();      
    DataSet results = tQuery.Execute(dsQuery, dsBAQ); 
    if (results != null && results.Tables.Count > 0 && results.Tables[0].Rows.Count > 0)
    {
      DataRow row = results.Tables[0].Rows[0];          
      vStart = DateTime.Parse(row["ProjectTask_DateComplete"].ToString()) ; 
      this.PublishInfoMessage(row["Pro

Change these to parameter[1] so you don’t overwrite your first one.

livingstonmarc · February 1, 2022, 2:05pm

That was actually how I first had it, but that wasn’t working either

if (tQuery != null)
{    
  Ice.Tablesets.DynamicQueryTableset dsQuery = tQuery.GetByID("KNI-LastCompletedTask");    
  if (dsQuery != null)                    
  {                          
    Ice.Tablesets.QueryExecutionTableset dsBAQ = tQuery.GetQueryExecutionParameters(dsQuery);                
    dsBAQ.ExecutionParameter[0].ParameterID = "Project";  
    dsBAQ.ExecutionParameter[0].IsEmpty = false;
    dsBAQ.ExecutionParameter[0].ParameterValue = vttprojecttask.ProjectID.ToString() ;          
    dsBAQ.ExecutionParameter[1].ParameterID = "Task";  
    dsBAQ.ExecutionParameter[1].IsEmpty = false;
    dsBAQ.ExecutionParameter[1].ParameterValue = vttprojecttask.TaskID.ToString();      
    DataSet results = tQuery.Execute(dsQuery, dsBAQ); 
    if (results != null && results.Tables.Count > 0 && results.Tables[0].Rows.Count > 0)
    {
      DataRow row = results.Tables[0].Rows[0];          
      vStart = DateTime.Parse(row["ProjectTask_DateComplete"].ToString()) ; 
      this.PublishInfoMessage(row["ProjectTask_TaskID"].ToString(), Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "FirstVar","SecondVar");
      vstarttime= Int32.Parse(row["ProjectTask_TimeCompleted_c"].ToString());
    }                        
    dsBAQ = null;                    
  }
    
dsQuery = null;                      

}

knash · February 1, 2022, 2:32pm

Try and create a parameter variable and add it to the tQuery.

ExecutionParameterRow drRow = new ExecutionParameterRow();
drRow.ParameterID = "PackNum";  // name of parameter from BAQ
drRow.ParameterValue = temp.PackNum.ToString();
drRow.ValueType = "int";
drRow.IsEmpty = false;
drRow.RowMod = "A";
drRow.SysRowID = Guid.Empty;

dsQueryExecution.ExecutionParameter.Add(drRow);

ExecutionParameterRow drRow2 = new ExecutionParameterRow();
drRow2.ParameterID = "OrderNum";  // name of parameter from BAQ
drRow2.ParameterValue = temp.OrderNum.ToString();
drRow2.ValueType = "int";
drRow2.IsEmpty = false;
drRow2.RowMod = "A";
drRow2.SysRowID = Guid.Empty;

dsQueryExecution.ExecutionParameter.Add(drRow2);

DataSet dsResults = boDynamicQuery.ExecuteByID("EmbedTek-ShipmentEDI", dsQueryExecution);

DataTable result_dt = dsResults.Tables["Results"];

livingstonmarc · February 1, 2022, 3:08pm

I am still getting the same results (none) with this method

knash · February 1, 2022, 3:11pm

Did you try and run it by hard coding some values. Just to make sure the call is working?

livingstonmarc · February 1, 2022, 3:17pm

Does not work by setting the parameters to hard coded values in the code. I also checked using the info message to see if the parameters are set, and they are (using hard code and using the datatable values)

do you mean hard coding the BAQ and removing the parameters?
The BAQ call was working and returning data when it was only 1 parameter. It stopped working when I added the second one

knash · February 1, 2022, 3:20pm

What does the BPM code look like now? Please post the whole thing. vs just the param code.

Can you also show the parameters from the BAQ. Something is weird here.

I meant to hard code the BPM code so its using values you know that will return something vs using vttprojecttask.TaskID.ToString()

livingstonmarc · February 1, 2022, 3:23pm

if (tQuery != null)
{    
  Ice.Tablesets.DynamicQueryTableset dsQuery = tQuery.GetByID("KNI-LastCompletedTask");    
  if (dsQuery != null)                    
  {                          
    Ice.Tablesets.QueryExecutionTableset dsBAQ = tQuery.GetQueryExecutionParameters(dsQuery);         


    

    dsBAQ.ExecutionParameter[0].ParameterID = "Project";  
    dsBAQ.ExecutionParameter[0].IsEmpty = false;
    dsBAQ.ExecutionParameter[0].ParameterValue ="855331" ; 
    dsBAQ.ExecutionParameter[1].ParameterID = "Task";  
    dsBAQ.ExecutionParameter[1].IsEmpty = false;
    dsBAQ.ExecutionParameter[1].ParameterValue = "066";  

     DataSet results = tQuery.Execute(dsQuery, dsBAQ); 
    if (results != null && results.Tables.Count > 0 && results.Tables[0].Rows.Count > 0)
    {
      DataRow row = results.Tables[0].Rows[0];          
      vStart = DateTime.Parse(row["ProjectTask_DateComplete"].ToString()) ; 
      this.PublishInfoMessage(row["ProjectTask_TaskID"].ToString(), Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "FirstVar","SecondVar");
      vstarttime= Int32.Parse(row["ProjectTask_TimeCompleted_c"].ToString());
    }                        
    dsBAQ = null;                    
  }
    
dsQuery = null;                      

}

knash · February 1, 2022, 3:24pm

What is tQuery? can you post the whole BPM code please

livingstonmarc · February 1, 2022, 3:25pm

Ok, however it is a bit long as there are several other BAQ calls throughout.

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





double time= 0;
DateTime vComplete = new DateTime();
DateTime vStart = new DateTime();
int vstarttime = 0;
double BusDays =0;
TimeSpan Starttime = new TimeSpan(0, 8, 0, 0);
TimeSpan Endtime = new TimeSpan(0, 20, 0, 0);


foreach(var vttprojecttask in (from xRow in ttProjectTask select xRow))
{
if (tQuery != null)
{    
  Ice.Tablesets.DynamicQueryTableset dsQuery = tQuery.GetByID("KNI-LastCompletedTask");    
  if (dsQuery != null)                    
  {                          
    Ice.Tablesets.QueryExecutionTableset dsBAQ = tQuery.GetQueryExecutionParameters(dsQuery);         


    

    dsBAQ.ExecutionParameter[0].ParameterID = "Project";  
    dsBAQ.ExecutionParameter[0].IsEmpty = false;
    dsBAQ.ExecutionParameter[0].ParameterValue ="855331" ; 
    dsBAQ.ExecutionParameter[1].ParameterID = "Task";  
    dsBAQ.ExecutionParameter[1].IsEmpty = false;
    dsBAQ.ExecutionParameter[1].ParameterValue = "066";  

     DataSet results = tQuery.Execute(dsQuery, dsBAQ); 
    if (results != null && results.Tables.Count > 0 && results.Tables[0].Rows.Count > 0)
    {
      DataRow row = results.Tables[0].Rows[0];          
      vStart = DateTime.Parse(row["ProjectTask_DateComplete"].ToString()) ; 
      this.PublishInfoMessage(row["ProjectTask_TaskID"].ToString(), Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "FirstVar","SecondVar");
      vstarttime= Int32.Parse(row["ProjectTask_TimeCompleted_c"].ToString());
    }                        
    dsBAQ = null;                    
  }
    
dsQuery = null;                      

}


vStart = DateTime.Parse(vStart.ToString()) ; 
vStart= vStart.AddSeconds(vstarttime);
vComplete = DateTime.Parse(vttprojecttask.DateComplete.ToString());
vComplete= vComplete.AddSeconds(vttprojecttask.TimeCompleted_c);



decimal hours = 0;

if (tQuery != null)
{    
  Ice.Tablesets.DynamicQueryTableset dsQuery = tQuery.GetByID("KNI-BusHrs");    
  if (dsQuery != null)                    
  {                          
    Ice.Tablesets.QueryExecutionTableset dsBAQ = tQuery.GetQueryExecutionParameters(dsQuery);                
    dsBAQ.ExecutionParameter[0].ParameterID = "Start";  
    dsBAQ.ExecutionParameter[0].IsEmpty = false;
    dsBAQ.ExecutionParameter[0].ParameterValue = vStart.ToString();  
    dsBAQ.ExecutionParameter[1].ParameterID = "End";  
    dsBAQ.ExecutionParameter[1].IsEmpty = false;
    dsBAQ.ExecutionParameter[1].ParameterValue = vComplete.ToString() ;  
   
    DataSet results = tQuery.Execute(dsQuery, dsBAQ); 
    if (results != null && results.Tables.Count > 0 && results.Tables[0].Rows.Count > 0)
    {
      DataRow row = results.Tables[0].Rows[0];          
        hours =(decimal)(row["Calculated_BusHrs"]) ;     

    }                        
    dsBAQ = null;                    
  }
    
dsQuery = null;                      

}








decimal hours1=0;
decimal hours2=0;
decimal hours3=0;

if (tQuery != null)
{    
  Ice.Tablesets.DynamicQueryTableset dsQuery = tQuery.GetByID("KNI-TaskHoldHours");    
  if (dsQuery != null)                    
  {                          
    Ice.Tablesets.QueryExecutionTableset dsBAQ = tQuery.GetQueryExecutionParameters(dsQuery);                
    dsBAQ.ExecutionParameter[0].ParameterID = "Project";  
    dsBAQ.ExecutionParameter[0].IsEmpty = false;
    dsBAQ.ExecutionParameter[0].ParameterValue = vttprojecttask.ProjectID.ToString() ;  
    dsBAQ.ExecutionParameter[1].ParameterID = "Row";  
    dsBAQ.ExecutionParameter[1].IsEmpty = false;
    dsBAQ.ExecutionParameter[1].ParameterValue = "1" ;  
    dsBAQ.ExecutionParameter[2].ParameterID = "Start";  
    dsBAQ.ExecutionParameter[2].IsEmpty = false;
    dsBAQ.ExecutionParameter[2].ParameterValue = vStart.ToString() ;  
   
    DataSet results = tQuery.Execute(dsQuery, dsBAQ); 
    if (results != null && results.Tables.Count > 0 && results.Tables[0].Rows.Count > 0)
    {
      DataRow row = results.Tables[0].Rows[0];          
      hours1 =(decimal)(row["Calculated_hours"]) ;     

    }                        
    dsBAQ = null;                    
  }
    
dsQuery = null;                      

}
if (tQuery != null)
{    
  Ice.Tablesets.DynamicQueryTableset dsQuery = tQuery.GetByID("KNI-TaskHoldHours");    
  if (dsQuery != null)                    
  {                          
    Ice.Tablesets.QueryExecutionTableset dsBAQ = tQuery.GetQueryExecutionParameters(dsQuery);                
    dsBAQ.ExecutionParameter[0].ParameterID = "Project";  
    dsBAQ.ExecutionParameter[0].IsEmpty = false;
    dsBAQ.ExecutionParameter[0].ParameterValue = vttprojecttask.ProjectID.ToString() ;  
    dsBAQ.ExecutionParameter[1].ParameterID = "Row";  
    dsBAQ.ExecutionParameter[1].IsEmpty = false;
    dsBAQ.ExecutionParameter[1].ParameterValue = "2" ;  
    dsBAQ.ExecutionParameter[2].ParameterID = "Start";  
    dsBAQ.ExecutionParameter[2].IsEmpty = false;
    dsBAQ.ExecutionParameter[2].ParameterValue = vStart.ToString() ;  
   
    DataSet results = tQuery.Execute(dsQuery, dsBAQ); 
    if (results != null && results.Tables.Count > 0 && results.Tables[0].Rows.Count > 0)
    {
      DataRow row = results.Tables[0].Rows[0];          
      hours2 =(decimal)(row["Calculated_hours"]) ;     

    }                        
    dsBAQ = null;                    
  }
    
dsQuery = null;                      

}
if (tQuery != null)
{    
  Ice.Tablesets.DynamicQueryTableset dsQuery = tQuery.GetByID("KNI-TaskHoldHours");    
  if (dsQuery != null)                    
  {                          
    Ice.Tablesets.QueryExecutionTableset dsBAQ = tQuery.GetQueryExecutionParameters(dsQuery);                
    dsBAQ.ExecutionParameter[0].ParameterID = "Project";  
    dsBAQ.ExecutionParameter[0].IsEmpty = false;
    dsBAQ.ExecutionParameter[0].ParameterValue = vttprojecttask.ProjectID.ToString() ;  
    dsBAQ.ExecutionParameter[1].ParameterID = "Row";  
    dsBAQ.ExecutionParameter[1].IsEmpty = false;
    dsBAQ.ExecutionParameter[1].ParameterValue = "3" ;  
    dsBAQ.ExecutionParameter[2].ParameterID = "Start";  
    dsBAQ.ExecutionParameter[2].IsEmpty = false;
    dsBAQ.ExecutionParameter[2].ParameterValue = vStart.ToString() ;  
   
    DataSet results = tQuery.Execute(dsQuery, dsBAQ); 
    if (results != null && results.Tables.Count > 0 && results.Tables[0].Rows.Count > 0)
    {
      DataRow row = results.Tables[0].Rows[0];          
      hours3 =(decimal)(row["Calculated_hours"]) ;     

    }                        
    dsBAQ = null;                    
  }
    
dsQuery = null;                      
tQuery.Dispose();
}

vttprojecttask.HoursSpent_c = (decimal)hours -(hours1 + hours2 + hours3);


}

knash · February 1, 2022, 3:45pm

you can add a bunch of messageboxs to debug. maybe the code isn’t getting into the BAQ call

Epicor.Customization.Bpm.InfoMessage.Publish(“one”);

isolate the BAQ call in a new BPM just for testing, something like this.


Ice.Contracts.DynamicQuerySvcContract tQuery = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(Db);
Ice.Tablesets.DynamicQueryTableset dsQuery = new QueryExecutionTableset();

Epicor.Customization.Bpm.InfoMessage.Publish("start");

ExecutionParameterRow drRow1 = new ExecutionParameterRow();
drRow1.ParameterID = "Project";  // name of parameter from BAQ
drRow1.ParameterValue = "855331";
drRow1.ValueType = "nvarchar";
drRow1.IsEmpty = false;
drRow1.RowMod = "A";
drRow1.SysRowID = Guid.Empty;

dsQuery.ExecutionParameter.Add(drRow1);	
Epicor.Customization.Bpm.InfoMessage.Publish("first");

ExecutionParameterRow drRow2= new ExecutionParameterRow();
drRow2.ParameterID = "Task";  // name of parameter from BAQ
drRow2.ParameterValue = "066";
drRow2.ValueType = "nvarchar";
drRow2.IsEmpty = false;
drRow2.RowMod = "A";
drRow2.SysRowID = Guid.Empty;

dsQuery.ExecutionParameter.Add(drRow2);	
Epicor.Customization.Bpm.InfoMessage.Publish("second");

DataSet dsResults = tQuery.ExecuteByID("KNI-LastCompletedTask", dsQuery);
Epicor.Customization.Bpm.InfoMessage.Publish("aftercall");
DataTable result_dt = dsResults.Tables["Results"];

if (result_dt.Rows.Count > 0)
  {      Epicor.Customization.Bpm.InfoMessage.Publish("call returned stuff");
  }

livingstonmarc · February 1, 2022, 5:06pm

It makes it to this call then nothing happens after. Is my BAQ set up correctly to handle 2 parameters? Have attached it here.KNI-LastCompletedTask.baq (26.7 KB)

Turned on BAQ tracing for the app server log and it is running the BAQ
BAQ QueryID=“KNI-LastCompletedTask” />
BAQ Company=“KNI” />
BAQ QueryPreparationTime=“2.158” />
BAQ SQLExecTime=“22.2528” />
BAQ DataFetchTime=“0.1196” />
BAQ TotalTime=“28.8217” />
BAQ TotalRows=“0” />

Here are the results from manually running and imputing the parameters

knash · February 1, 2022, 5:34pm

The one you showed was task 065 vs 066 if that matters

livingstonmarc · February 1, 2022, 5:37pm

correct, the criteria set was that the task must be less than the parameter. The BAQ should return the last task completed with its time.

livingstonmarc · February 1, 2022, 7:03pm

I got it to work. I moved the criteria for the Task parameter into the subquery itself.