Pass BAQ Parameters to Dashboard for Combo Boxes

Epicor ERP 10
1

Good morning,
I have a custom dashboard with a handful of combo boxes for things like part number, revision, and job number. My goal is to use my BAQs to populate the combo boxes. For example, I have a combo box for revision. The BAQ takes in a part number parameter, then returns all the revisions (with open jobs) for the part. That way the user can only pick a revision that applies to the part, and has an open job. To expand on this working example, here is the BAQ SQL:

select 
	[PartRev].[RevisionNum] as [PartRev_RevisionNum],
	[PartRev].[RevShortDesc] as [PartRev_RevShortDesc]
from Erp.JobHead as JobHead
left outer join Erp.PartRev as PartRev on 
	JobHead.PartNum = PartRev.PartNum
where (JobHead.JobClosed = false  and JobHead.PartNum = @part)

Inside the customization, I wait for the user to enter a value in the part number text box, then leave the text box before updating the revision combo box with this code:

	private void MyPartNum_Leave(object sender, System.EventArgs args)
	{
		// ** Place Event Handling Code Here **
		if (MyPartNum.Text!="")  
		{
		getRevs();
		cmbRevs.ForceRefreshList();
		}
	}


private void getRevs()
	{
		cmbRevs = (Ice.Lib.Framework.EpiUltraCombo)csm.GetNativeControlReference("a03dd350-74a0-4382-b1cb-251c5e41ed9a");

		DynamicQueryAdapter dqa = new DynamicQueryAdapter(oTrans);
		dqa.BOConnect();		
		QueryExecutionDataSet qeds = dqa.GetQueryExecutionParametersByID("getPartRev");
		qeds.ExecutionParameter.Clear();

		qeds.ExecutionParameter.AddExecutionParameterRow("part", MyPartNum.Text, "nvarchar", false, Guid.NewGuid(), "A");

		dqa.ExecuteByID("getPartRev", qeds);
		if (dqa.QueryResults.Tables["Results"].Rows.Count > 0)
		{
			cmbRevs.DataSource = dqa.QueryResults.Tables["Results"];
			cmbRevs.DisplayMember = "PartRev_RevShortDesc";
			cmbRevs.ValueMember = "PartRev_RevShortDesc";
			oTrans.NotifyAll();
		}
		else
		{
		cmbRevs.DataSource = "";
		cmbRevs.DisplayMember = "";
		cmbRevs.ValueMember = "";
		oTrans.NotifyAll();
		MessageBox.Show("Part Not Found!");
		}
	}

To reiterate, the above works great! The BAQ populates the combo box without popping up a parameter window. However, when I try the same thing with another combo box and BAQ, the parameter popup keeps appearing. Here is the BAQ for finding open jobs based on the part number and revision chosen:

select 
	[JobHead].[JobNum] as [JobHead_JobNum]
from Erp.JobHead as JobHead
left outer join Erp.PartRev as PartRev on 
	PartRev.PartNum = JobHead.PartNum
	and ( PartRev.RevisionNum = @rev  )

where (JobHead.JobClosed = false  and JobHead.PartNum = @part)

Inside the customization, I update the jobs combo box after I leave the revision field like this:

	private void epiRevs_Leave(object sender, System.EventArgs args)
	{
		// ** Place Event Handling Code Here **
		if (epiRevs.Value.ToString()!="")  
		{
		getJobs();
		cmbJobs.ForceRefreshList();
		//MessageBox.Show("Getting Jobs for part: " + MyPartNum.Text + " rev: " + epiRevs.Value.ToString());
		}
	}


	private void getJobs()
	{
		cmbJobs = (Ice.Lib.Framework.EpiUltraCombo)csm.GetNativeControlReference("ab3e6a13-bcc2-45bb-8f27-e93f11951026");

		DynamicQueryAdapter dqa = new DynamicQueryAdapter(oTrans);
		dqa.BOConnect();		
		QueryExecutionDataSet qeds = dqa.GetQueryExecutionParametersByID("Jobs");
		qeds.ExecutionParameter.Clear();

		qeds.ExecutionParameter.AddExecutionParameterRow("part", MyPartNum.Text, "nvarchar", false, Guid.NewGuid(), "A");
		qeds.ExecutionParameter.AddExecutionParameterRow("rev", epiRevs.Value.ToString(), "nvarchar", false, Guid.NewGuid(), "A");

		dqa.ExecuteByID("Jobs", qeds);
		if (dqa.QueryResults.Tables["Results"].Rows.Count > 0)
		{
			cmbJobs.DataSource = dqa.QueryResults.Tables["Results"];
			cmbJobs.DisplayMember = "JobHead_JobNum";
			cmbJobs.ValueMember = "JobHead_JobNum";
			oTrans.NotifyAll();
		}
		else
		{
		MessageBox.Show("No Open Jobs!");
		}
	}

My confusion is with the two BAQs. In the first I require a part number parameter. This works well without showing a parameter popup. The parameter value just gets passed through the c# code. However, in the second BAQ, I require two parameters, one for part and another for revision. This BAQ insists on showing the parameter popup. I want the parameters to pass silently from the C# code to the BAQ.

What is causing this inconsistency? It can’t just be the fact that there are two parameters in the second BAQ, right?

I appreciate any ideas!
Thanks!
Nate

2

Hey @NateS , did you end up finding a solution to this? I am about to embark on something very similar, hoping I don’t run into the same thing…

3

I did get my dashboard working with some compromises. I altered my Jobs BAQ to include the assembly part number. Like this:

select 
	[PartsAndAssys].[JobHead_JobNum] as [JobHead_JobNum],
	[PartsAndAssys].[JobHead_PartNum] as [JobHead_PartNum],
	[PartsAndAssys].[JobAsmbl_PartNum] as [JobAsmbl_PartNum]
from  (select 
	[JobHead].[JobNum] as [JobHead_JobNum],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[JobAsmbl].[PartNum] as [JobAsmbl_PartNum]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on 
	JobHead.Company = JobAsmbl.Company
	and JobHead.JobNum = JobAsmbl.JobNum
where (JobHead.JobClosed = false))  as PartsAndAssys
where (PartsAndAssys.JobHead_PartNum = @part  or PartsAndAssys.JobAsmbl_PartNum = @part)

I think I kept the part for getJobs the same:

	private void getJobs()
	{
		cmbJobs = (Ice.Lib.Framework.EpiUltraCombo)csm.GetNativeControlReference("ab3e6a13-bcc2-45bb-8f27-e93f11951026");

		DynamicQueryAdapter dqa = new DynamicQueryAdapter(oTrans);
		dqa.BOConnect();		
		QueryExecutionDataSet qeds = dqa.GetQueryExecutionParametersByID("Jobs");
		qeds.ExecutionParameter.Clear();

		qeds.ExecutionParameter.AddExecutionParameterRow("part", MyPartNum.Text, "nvarchar", false, Guid.NewGuid(), "A");
		qeds.ExecutionParameter.AddExecutionParameterRow("rev", epiRevs.Value.ToString(), "nvarchar", false, Guid.NewGuid(), "A");

		dqa.ExecuteByID("Jobs", qeds);
		if (dqa.QueryResults.Tables["Results"].Rows.Count > 0)
		{
			cmbJobs.DataSource = dqa.QueryResults.Tables["Results"];
			cmbJobs.DisplayMember = "JobHead_JobNum";
			cmbJobs.ValueMember = "JobHead_JobNum";
			oTrans.NotifyAll();
		}
		else
		{
		MessageBox.Show("No Open Jobs!");
		}
	}

It is interesting that even though I didn’t use rev as a parameter in the BAQ, I am still able to add it using the code. Having said that, I don’t think the rev is actually doing anything since there is no rev in the Jobs BAQ.

My form is working OK. I can enter a part number and the revision combo shows a list of revisions for that part. Once I choose a revision, the job combo fills in with open jobs for that part.

I would love to figure out a definitive way to filter a BAQ into a combo box by passing more than one parameter. If you get anywhere with it, please let us know!

Thanks, and good luck!
Nate

2 Likes
4

Thanks Nate!

I was not actually in a dashboard for my project, I just had a chain of combo boxes where a selection on the first one would need to become a parameter for the BAQ populating the next one, and so on. I did not run into any prompts so I imagine that is just a quirk of Dashboards?

I unfortunately can’t share my code because I don’t own the IP on this one. But wanted to come here and thank you for sharing yours… it was very helpful!

1 Like