How can I execute a BAQ from code using a list type parameter?

Epicor ERP 10
1

In the BAQ designer I have this option to add values:

image
image1476×464 54.2 KB

How can I do it with code? I’m using this code to execute the BAQ

		// Declare and create an instance of the Adapter.
		DynamicQueryAdapter adapterDynamicQuery = new DynamicQueryAdapter(this.oTrans);
		adapterDynamicQuery.BOConnect();
		
		// Declare and Initialize Variables
		string BAQName = "FiscalPeriod";
		Ice.BO.QueryExecutionDataSet ds = new Ice.BO.QueryExecutionDataSet();
		
		// Add Parameter Rows
		// Definition: AddExecutionParameterRow(string ParameterID, string ParameterValue, string ValueType, bool IsEmpty, Guid SysRowID, string RowMod)
		// Possible ValueTypes: nvarchar, int, decimal, date, datetime, bit, uniqueidentifier, bigint
		// IsEmpty indicates if your passed value Is Empty because if it is, you can define in your params to use a default value if empty.
		// Typically you use string.IsNullOrEmpty(yourValueVariable) but if you are hard coding a value then you can simply set it to false
		ds.ExecutionParameter.AddExecutionParameterRow("FiscalYear", "2016", "int", false, Guid.Empty, "A");
		ds.ExecutionParameter.AddExecutionParameterRow("FiscalPeriod", "10", "int", false, Guid.Empty, "A");
		
		// Call Adapter method
		adapterDynamicQuery.ExecuteByID(BAQName, ds);
		
		// Lets Loop through our results
		if (adapterDynamicQuery.QueryResults.Tables["Results"].Rows.Count > 0)
		{
			dgDatos.DataSource = adapterDynamicQuery.QueryResults.Tables["Results"];
		}
		
		// Cleanup Adapter Reference
		adapterDynamicQuery.Dispose();
		MainController.Cursor = Cursors.Default;
	} catch (System.Exception ex)
	{
		MainController.Cursor = Cursors.Default;
		ExceptionBox.Show(ex);
	}
2

I believe that lists are handled by making a tilde (~) delimited string. You coud confirm this by enabling tracing and running your BAQ in the BAQ designer, then look at the Where clause generated.

3

Thanks for your answer, in the tracing a parameter is added for each value in the list so I solved it this way

image
image861×439 114 KB 

foreach(var item in parameterList)
     ds.ExecutionParameter.AddExecutionParameterRow("Departments", item, "nvarchar", false, Guid.Empty, "A");
2 Likes
4

Hello,

Can you share with us the complete dynamic query code when using the parameter list from baq?

Thank you.

5

Here’s how I’m doing it from a classic screen customization.

private DataTable ExecuteSMIJobs(IEnumerable<string> jobNums)
{
    using (DynamicQueryAdapter dynamicQueryAdapter = new DynamicQueryAdapter(UD03Form))
    {
        dynamicQueryAdapter.BOConnect();

        var queryExecutionDataSet = new QueryExecutionDataSet();

        foreach (var jobNum in jobNums)
        {
            queryExecutionDataSet.ExecutionParameter.AddExecutionParameterRow("JobNums", jobNum, "nvarchar", string.IsNullOrEmpty(jobNum), Guid.Empty, "A");
        }

        dynamicQueryAdapter.ExecuteByID("SMIJobs", queryExecutionDataSet);
        return dynamicQueryAdapter.QueryResults.Tables["Results"];
    }
}
2 Likes