Dynamic Query in EFX

Epicor ERP 10
,
1

So i am trying to add a parameter list in my dynamic query. but i cant seem to get the context right i keep getting this error:

Here is my current code right now:

var context = Ice.Services.ContextFactory.CreateContext<ErpContext>(); 

using (var svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(context)) 
{ 
  Ice.Tablesets.DynamicQueryTableset dsQuery = svc.GetByID("API_MassPrintJobDrawingsPDF"); 

    if (dsQuery != null) 
    { 
        Ice.Tablesets.QueryExecutionTableset dsBAQ = svc.GetQueryExecutionParameters(dsQuery);
        foreach ( var jobNum in jobNumber)
        {
          dsBAQ.ExecutionParameter.Clear();
          dsBAQ.ExecutionParameter[0].ParameterID = "JobNumber"; 
          dsBAQ.ExecutionParameter[0].IsEmpty = false; 
          dsBAQ.ExecutionParameter[0].ParameterValue = jobNum.ToString();
          dsBAQ.ExecutionParameter[0].RowMod = "A";
        }
        DataSet ListOfPDFS = svc.Execute(dsQuery,dsBAQ); 
    } 
}

System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
at System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
at EFx.PrintJobDrawings.Implementation.CreateXMLForJobDrawingsImpl.A001_CustomCodeAction()
at EFx.PrintJobDrawings.Implementation.CreateXMLForJobDrawingsImpl.RunStep(Int32 workflowStep)
at Epicor.Functions.FunctionBase3.Run() in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\Epicor.Functions.Core\FunctionBase.cs:line 90 at Epicor.Functions.FunctionBase3.Run(TInput input) in C:_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\Epicor.Functions.Core\FunctionBase.cs:line 75
at Epicor.Functions.FunctionRestAdapter2.Run(IFunctionRestHost host, JObject input) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\Lib\Epicor.Functions.Core\FunctionRestAdapter.cs:line 46 at Epicor.RESTApi.Functions.Controllers.EpicorFunctionController.Post(Boolean production, String company, String library, String function, JObject data) in C:\_Releases\ICE\ICE3.2.700.0\Source\Server\Internal\RESTApi.Plugins\Epicor.RESTApi.EFxPlugin\Controllers\EpicorFunctionController.cs:line 69 at lambda_method(Closure , Object , Object[] ) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_2.<GetExecutor>b__2(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary2 arguments, CancellationToken cancellationToken)
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ApiControllerActionInvoker.d__1.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.d__6.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.d__6.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.d__6.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.d__6.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ActionFilterResult.d__5.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Dispatcher.HttpControllerDispatcher.d__15.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Dispatcher.HttpControllerDispatcher.d__15.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

2

For one, this clears the whole table every time the loop runs
image

Though that’s likely not your only issue. (still looking)

Also the fact that you cleared the table means that now index 0 doesn’t exist and that’s where you out of range exception is coming from.

To add a List you need to insert the same parameter multiple times so instead of ExecutionParameter.Clear() you are going to want to add a NewRow and use BufferCopy (or something like it) to clone that row multiple times changing only the ParameterValye and RowMod.

3

yeah lol i was testing alot of things forgot that was still in there you are correct that is not my only issue.

4

after your suggestions this is what i have now but by baq is not returnning any data. I do know the jobs exist as i use them as parameters in bag and get rows. would i have to put a new guid in for each new row? or will the system handle that as well?

var context = Ice.Services.ContextFactory.CreateContext<ErpContext>(); 

using (var svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(context)) 
{ 
  Ice.Tablesets.DynamicQueryTableset dsQuery = svc.GetByID("API_MassPrintJobDrawingsPDF");
  Ice.Tablesets.QueryExecutionTableset dsBAQ = svc.GetQueryExecutionParameters(dsQuery);

    if (dsQuery != null) 
    { 
        
        foreach ( var jobNum in jobNumber)
        {
          ExecutionParameterRow dsBAQRow = new ExecutionParameterRow();
          dsBAQRow.ParameterID  = "JobNumber"; 
          dsBAQRow.IsEmpty = false; 
          dsBAQRow.ParameterValue = jobNum.ToString();
          dsBAQRow.RowMod = "A";
          dsBAQ.ExecutionParameter.Add(dsBAQRow);
        }
        DataSet ListOfPDFS = svc.Execute(dsQuery,dsBAQ); 
    } 
}
5

I usually generate a new GUID , also since you are getting executionquery params you may already have at least 1 instance of said row in dsBAQ

6

Thanks for you help i found my mistake lol for the line
DataSet ListOfPDFS = svc.Execute(dsQuery,dsBAQ); i had the variable called as my response parameters and it was already a dataset so i removed DataSet and i have values lol

:innocent: