Passing Parameter/Filter in Dynamic Query

[mchernecki (Mike Chernecki)]

@dhewi

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.

I tried your method and I like it. Very powerful, I can do what I need without needing to worry about Epicor’s limitations. However, when I close the Dashboard, I get a “Save Confirmation” dialog popup. Not sure where this is coming from or how to suppress it. Did you have the same problem and how did you resolve it?

[dhewi (Daryl Hewison)]

I’m glad to have someone else looking at this method, because it does open up a lot of possibilities.

The pop-up save on close is something we experience too, in most cases but oddly not all. I haven’t made a priority of getting to the bottom of it because it doesn’t seem to trouble any of our users, only me! They just click whatever they like and carry on without a further thought.

I assume the generic Epicor form has some kind of “dirty data” flag that activates for any EpiDataView and defaults to true, but I haven’t found it. If I do track down what’s going on then I’ll drop a note back, otherwise I’ll be grateful to know what you find.

[dhewi (Daryl Hewison)]

I should add, by the way, that if you tick “Don’t show this again” when dismissing that Save Confirmation, you only need to deal with it once, so although it remains a niggle for those of us creating the dashboards that something isn’t quite right, it doesn’t affect anything much in practice.

[mchernecki (Mike Chernecki)]

Again, Thanks for the suggestion. I will update if I find anything more on eliminating the popup.

[A.Baeisa (Al)]

great code, i want to use it in Normal UI Form (PartRev) , hooked to ‘click’ button, with two ‘And’ parameters PartNum and PartRev, but the problem is that all PartRev existed in the ds, any idea in how can i hook to the current PartRev parameter to be able to filter my dynamicquery to it as well as PartNum.

[dhewi (Daryl Hewison)]

I’m not familiar with that particular form, sorry.

But my general approach is to find which EpiDataView in the form contains the field data you want to work with, which is easily done in Customization mode by looking at the EpiBinding property of something that shows it. Then you can access that view with

EpiDataView edv = (EpiDataView)oTrans.EpiDataViews["viewname"];

It may have several rows, but the current one will be indexed with .Row, so you can get the field data for whatever is current with

edv.dataView[edv.Row]["columnname"]

It’s advisable to test for edv.Row > -1 to avoid errors with empty views, and if you’re passing the result to a DynamicQuery parameter then adding .ToString() is also sensible.

Apologies if that’s a bit “ABC” in terms of instructions, but you never know who else might want to know the same thing later!

[danbedwards (Dan Edwards)]

You could also use edv.CurrentDataRow[“columnname”]

@dhewi

so you can get the field data for whatever is current with

[hkeric.wci (Haso Keric)]

Since we are on this topic, upvote: Dashboard Pass Silent Parameters to BAQ - Feature Requests and Suggestions - Epicor User Help Forum