Passing Parameter/Filter in Dynamic Query

True, you are correct in that. And if you don’t have any sub-queries with aggregating data, works very well.

The issue comes in the use case where you are aggregating some data and want to limit what is being aggregated. This is where you need parameters in your BAQ to do this, because otherwise you are only filtering the final table that is presented.

For example, I can’t run a CTE that get a multilevel BOM without a parameter to anchor the parent part number.

If you BAQ doesn’t need a parameter, then filtering works the same as a parameter. But sometimes you need a parameter (like when passing a number to calculate a markup percentage) where you can’t just filter the final table.

3 Likes

Ah, I just ran it 2 different ways with the trace on and got back the same dataset in the trace. I did not know that, thanks for pointing it out.

So, it goes back to my original idea. Can’t you hijack the method and insert a where clause in the actual query text?

Many have tried, none have succeeded. If you do, you will be lauded a hero among paupers!

2 Likes

Well, that is not going to happen. I did not even understand what people were saying until you explained it to me :rofl:

You’re not the first, you won’t be the last,

I like your radio button fix though, and I will probably use that in the future!

I have a couple of “light code” options for you - I am short on time so will post second option separately.

Option 1

Add a Text Box to allow user to enter a filter value. Add a Button that will execute the BAQ and apply the value entered in the TextBox as a filter against one of the Columns in the BAQ. Filter applied is in addition to any criteria defined on the BAQ.

Pros - not much code; Simple Setup; allows BAQ level where clause filtering without pop-up prompt
Cons - Only Supports filter condition against one column; Filter condition is always “Equal”
Pro and Con (depending on need) - Allows standard DBD Refresh to execute without filter applied

My Specific setup - use as example for your use case:
BAQ called “DemandSearch” created with Customer CustID as one of the display columns.
DBD created with DemandSearch BAQ. Tracker Panel added - No Fields set as Prompt
DBD AppBuilt and added to menu

Run DBD from Menu in Customization Mode. Customize:
Add TextBox control - leave it unbound
Add Button control - Set Text to “Refresh”
On Event Wizard - Add “Click” Event for Refresh Button

Add Code via Script Editor:
TextBox reference to Custom TextBox
Dispose Custom TextBox reference
Line of C# added for Click event - OnSearch has three parameters (we will look at the third one in Option 2 sample). First Param is the Column you want to filter. Second is the Value to use as the Filter. This one is applying Filter to the Customer CustID column.


// Code Sample Pulled from Script Editor

public class Script
{
// ** Wizard Insert Location - Do Not Remove ‘Begin/End Wizard Added Module Level Variables’ Comments! **
// Begin Wizard Added Module Level Variables **

// End Wizard Added Module Level Variables **

// Add Custom Module Level Variables Here **

private EpiTextBox epiTB1;

public void InitializeCustomCode()
{
	// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
	// Begin Wizard Added Variable Initialization
    this.epiTB1 = (EpiTextBox)csm.GetNativeControlReference("your TextBox EpiGUID here");

	// End Wizard Added Variable Initialization

	// Begin Wizard Added Custom Method Calls

	this.epiButtonC1.Click += new System.EventHandler(this.epiButtonC1_Click);
	// End Wizard Added Custom Method Calls
}

public void DestroyCustomCode()
{
	// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
	// Begin Wizard Added Object Disposal

	this.epiButtonC1.Click -= new System.EventHandler(this.epiButtonC1_Click);
	// End Wizard Added Object Disposal

	// Begin Custom Code Disposal
    this.epiTB1 = null;

	// End Custom Code Disposal
}

private void epiButtonC1_Click(object sender, System.EventArgs args)
{
	// ** Place Event Handling Code Here **

    // DemandSerch is the ID of the BAQ I am using. DemandSearch as in On Demand Search...

  V_DemandSearch_1View_Row.OnSearch("Customer_CustID",epiTB1.Value.ToString());

}

}

3 Likes

A couple of “light code” options for you – continued…

Option 2

Add one or more controls – this example uses two Text Boxes – to allow user to enter filter values. Add a Button that will execute the BAQ and through the magic of BAQ Markup, the filter values will be applied to the BAQ as specified in the BAQ Criteria.

BAQ Markup (in my opinion) is relatively unknown and little understood and yet for some UI specific purposes, it is really powerful. BAQ Markup was introduced in 9.05 for InfoZones and has since expanded to BAQ Combos and optionally to programmatically executed BAQs – which is what is being done here. BAQ Markup is a reference to data in the UI and the referenced data will be “substituted” at runtime for the Markup. In this example, the Markup is referencing a specific Epibinding and the Markup will be replaced with the data in the UI for BAQ execution. BAQ Markup (as a concept) is actually pretty well documented in newer versions.

Pros - not much code; Pretty simple setup; allows BAQ level where clause filtering without pop-up prompt and with full control of Criteria condition (Equal, Greater than, Begins, etc.) and also full control of And / Or condition linking

Cons – Requires that All BAQ Markup applied filters have a Filter value entered in the UI; Standard DBD Refresh will return no records (you might want to remove the Toolbar Tool or hi-jack the click event)

My Specific setup - use as example for your use case:
BAQ called “DemandSearch” created against the Customer table with BAQ Markup applied in the Table Criteria against the CustID and City columns (not particularly useful except for as a simple example)

On the “Table Criteria” Sheet of the BAQ Designer, create two criteria lines:

Field: “CustID” Operation: “Begins” Filter value: “specified Constant” for the specified part enter: [Epibinding: CallContextBpmData.Character01]
And
Field: “City” Operation: “Begins” Filter value: “specified Constant” for the specified part enter: [Epibinding: CallContextBpmData.Character02]

An image of my sample BAQ is below.

DBD created with DemandSearch BAQ. Tracker Panel added - No Fields set as Prompt
DBD AppBuilt and added to menu

Run DBD from Menu in Customization Mode. Customize:
Add TextBox control for CustID filter – bind to CallContextBpmData.Character01
Add TextBox control for City filter – bind to CallContextBpmData.Character02

Add Button control - Set Text to “Refresh”
On Event Wizard - Add “Click” Event for Refresh Button

Add Code via Script Editor:
Line of C# added for Click event. As referenced in Option 1, OnSearch has three parameters leave the first two parameters set to an empty string and set the third parameter to true. The third parameter tells the OnSearch method to handle Markup substitution. While this Option uses Markup and the first Option used Criteria Injection, the two are not mutually exclusive and you could use both techniques together.

Not added for this Sample but something you would want to do – Add Row rule to Disable the “Refresh” button until all Markup referenced filters have a value – Bind the Button to something like BpmData.Character10 and enable and disable via that data reference.

Good to know:
If there is no data in the Markup Referenced field, the Markup itself is sent as the Where Clause Criteria – clearly not useful…
You cannot Test Markup in the BAQ Designer - it just executes the BAQ with the Markup as the criteria. Test your BAQ without Markup and when happy, add the Markup criteria.


// Code Sample Pulled from Script Editor

public class Script
{
// ** Wizard Insert Location - Do Not Remove ‘Begin/End Wizard Added Module Level Variables’ Comments! **
// Begin Wizard Added Module Level Variables **

        // End Wizard Added Module Level Variables **

        // Add Custom Module Level Variables Here **

        public void InitializeCustomCode()
        {
                    // ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
                    // Begin Wizard Added Variable Initialization

                    // End Wizard Added Variable Initialization

                    // Begin Wizard Added Custom Method Calls

                    this.epiButtonC1.Click += new System.EventHandler(this.epiButtonC1_Click);
                    // End Wizard Added Custom Method Calls
        }

        public void DestroyCustomCode()
        {
                    // ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
                    // Begin Wizard Added Object Disposal

                    this.epiButtonC1.Click -= new System.EventHandler(this.epiButtonC1_Click);
                    // End Wizard Added Object Disposal

                    // Begin Custom Code Disposal

                    // End Custom Code Disposal
        }

        private void epiButtonC1_Click(object sender, System.EventArgs args)
        {
                    // ** Place Event Handling Code Here **

                    // DemandSerch is the ID of the BAQ I am using. DemandSearch as in On Demand Search...

                    V_DemandSearch_1View_Row.OnSearch("","",true);

        }

}

7 Likes

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

This will be a perfect solution for a use case where I need to calculate a markup percentage. Currently I do it with parameters. I can make a text box that always has a number in it. Nice!

Edit: had to think for a second, that’s only filters. Can I use the same technique to pass in a value to a calculated field?

If you are configuring the Parameter on the Table Criteria section, you can use the Markup feature instead - instead of using the “specified Parameter” option use the “specified Constant” and configure the value as the Markup.

TableCriterria

But can I use it in a calculated field? That’s what I’m wondering.

I have spent the morning testing this using the BAQ Markup and it works fine given the above example. However, I need to do this on a Date field. I have added two EpiDateTimeEditor fields and bound them to CallContextBpmData.Date01 and Date02. My Table Criteria are

image

This returns no results, looking at SQL Profiler it looks like the BAQ Markup inserted the date but did not put quotes around the date.
’ where [SalesDetail].[ShipDate] >= 2018-07-01 12:00:00 AM And [SalesDetail].[ShipDate] <= 2018-07-31 12:00:00 AM ’

Any ideas?

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?

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.

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.

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

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.

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!

1 Like

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

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