Passing Parameter/Filter in Dynamic Query

Epicor ERP 10
16

Just to clarify, the method I was outlining is a lot of code, but it does do exactly what you want. We’ve standardised a lot of it to do what we need and now build most of our dashboards this way because it turns out to be more flexible and maintainable in the long run.

Step 1. Build and deploy a dashboard with a near-empty dummy BAQ, no grid view and one full-screen tracker view with no fields. That gives you a blank canvas.

Step 2. Create a customization for the blank dashboard. Use the standard tools to put an EpiUltraGrid where the grid would have been if you’d built the dashboard normally, and whatever other controls (eg an EpiTextBox) you want to put the parameters into. Add Assembly references for DynamicQuery.

Step 3. In the Script, put some variant of the following code:

		DynamicQueryAdapter yourbaq = new DynamicQueryAdapter(this.oTrans);
                DataTable results;
		yourbaq.BOConnect();
		string baqname = "BAQNAME";
		Ice.BO.QueryExecutionDataSet dsBAQ = yourbaq.GetQueryExecutionParametersByID(baqname);
		dsBAQ.ExecutionParameter[0].ParameterID = "YOURPARAMETERNAME";
		dsBAQ.ExecutionParameter[0].IsEmpty = false;
		dsBAQ.ExecutionParameter[0].ParameterValue = VALUE FROM YOUR CONTROL AS A STRING;
		dsBAQ.AcceptChanges();
		yourbaq.ExecuteByID(baqname, dsBAQ);
		if (yourbaq.QueryResults != null && yourbaq.QueryResults.Tables.Count > 0)
		{
			results = yourbaq.QueryResults.Tables["Results"];
		}
		else
		{
			results = new DataTable();
		}
		EpiDataView edv = (EpiDataView)oTrans.EpiDataViews[baqName];
		if (!(edv != null))
		{
			edv = new EpiDataView();
			oTrans.Add(baqName, edv);
		}
		edv.dataView = results.DefaultView;

Step 4. You can use the new EpiDataView to bind to your grid. If you close and open the screen (assuming you’ve put the above somewhere that runs on opening) then you’ll even find the dataview is available in the normal properties to use. I prefer to bind the grid’s DataSource rather than use EpiBinding myself, because it’s more flexible that way, but it’s simpler to use Epicor’s proper system.

Step 5. Either create a “Refresh” button or hook into the menu RefreshTool and make sure the above code runs when the user wants to make changes.

Updateable BAQs need a bit more code than this but can also be made to work very reliably.

6 Likes