Pass parameter to BAQ from configurator

Can I do that in a client side UD method for a configurator? Seems like there is more freedom to do stuff like this in customizations…

I have managed to get the BAQ results from code using this method, but I don’t know how to pass my parameters:

var queryID = "ConfiguratorScreenPackageType";
object[] @params = new object[]
{
	queryID,
	0
};

System.Data.DataTable dataTable2 = ProcessCaller.InvokeAdapterMethod(((InputControlValueBound<EpiUltraCombo, string>)Inputs["BoxType"].Value).Control.EpiTransaction, "DynamicQueryAdapter", "GetQuerySearchResults", @params) as System.Data.DataTable;

There is a “BAQ Criteria” tab on the Dynamic List… I am fairly sure that this criteria is used as part of the BAQ Criteria BEFORE it is sent to the database to be retrieved.
image

I have traced this before and it does actually pull the full dataset without filtering. Below is an example using TranGLC - for maximum presentation. The part that has #1 shows the BAQ being executed with a filter from the Dynamic List. #2 shows the same BAQ being executed with the filter from the BAQ designer. In theory, if filtered, both would be about the same.

ok… I stand corrected.
Last time I used BAQs for configurators (other than a demo/training class) was a very long time ago. I normally create my own Linq queries for Combo boxes… I also typically create a direct query to Lookup tables so that I can create filtered lists that only return what I need.
SOOO… because I was wrong… I will give you a little bonus… here is the C# Code to use to lookup date from a Lookup Table. Because lookup table are really multiple rows for each column, it takes a rather complex query to get at them. but this can easily be converted to a query to get data from any epicor table.

/*Advanced ComboQuestionLookupFiltered - will examine the lookup table, find all the options for the defined question, and pass back the value and description of the option for use in a combo box.*/
/*
	the basic format if the lookup table should be at least for columns
		Question column		- This is the question id.. this groups all available answers for the question
		Answer column		- this is the RESULT that you want to be returned into the "Value" of the combo
		Description column	- this is the description that will be displayed in the combo box
		Sort Column			- this sorts the answers in the order that you specify
		Filter Column		- comma delimited field that holds items to be filtered on... This allows the list to be further filtered by options available for the question/model

	There are three parameters that are passed to this:
		LookupTblID - this is which lookup table should be used... this allows for the lookup table to be dynamic in the configurator
		Question - this is the "question" value that will be found in the QuestionCol column that is in the lookup table
		Filter - this is the value of the filter. IF this is blank, then it is not used. If it is NOT blank, then it will search in the filter column for this value using "Contains" feature

	Sample Table:
		Question	Answer	Description	Sort	Filter
		Color		Red		Red color	B		Regular, Red
		Color		LRed	Light Red	A		Light, Red
		Color		DRed	Dark Red	C		Dark, Red
		Color		Blue	Blue color	E		Regular, Blue
		Color		LBlue	Light Blue	D		Light, Blue
		Color		DBlue	Dark Blue	F		Dark, Blue
		Color		Green	Green Color	H		Regular, Green
		Color		LGreen	Light Green	G		Light, Green
		Color		DGreen	Dark Green	I		Dark, Green
		Size		Small	< 5" long	A		None
		Size		Med		>=5" <10" 	B		None
		Size		Large	>= 10"		C		None

	in the above table,
		If I pass "Color" as the question and the following FILTER:
			"Dark" as a filter, and only receive the three dark colors.
			"Red" to the filter, and only get the red colors.
			"Red,Dark" to the filter, then I would only receive the DRed - Dark Red option.
		If I pass "Size as the question, and dont pass any filter:
			receive the three sizes

	This will RETURN a value that is used by the combo field... usign Question = Color, Filter = Red, the following would be returned (sorted correctly:
		"LRed`Light Red~Red`Red Color~DRed~Dark Red"

/*Change these to the 5 Column names that are in your table.*/
string LT_QuestionCol = "QUESTION";	/*this Col is the group of options... equal to the passed ComboName from above*/
string LT_AnswerCol = "ANSWER";			/* this Col contains the actual resulting option ID*/
string LT_DescCol = "DESCRIPTION";		/* this Col contains the description of the Answer*/
string LT_SortByCol = "SORT";				/* we sort by this Col.*/
string LT_FilterCol = "FILTER";				/* we will further filter by this column.*/

return string.Join("~",(from Ques in Db.PcLookupTblValues
		join Desc in Db.PcLookupTblValues          	on new {Ques.Company, Ques.RowNum, Ques.LookupTblID} equals new {Desc.Company, Desc.RowNum, Desc.LookupTblID }
		join Answ in Db.PcLookupTblValues		on new {Ques.Company, Ques.RowNum, Ques.LookupTblID} equals new {Answ.Company, Answ.RowNum, Answ.LookupTblID}
		join Seq in Db.PcLookupTblValues		on new {Ques.Company, Ques.RowNum, Ques.LookupTblID} equals new {Seq.Company, Seq.RowNum, Seq.LookupTblID}
		join Filt in Db.PcLookupTblValues		on new {Ques.Company, Ques.RowNum, Ques.LookupTblID} equals new {Filt.Company, Filt.RowNum, Filt.LookupTblID}
		where Ques.LookupTblID == LookupTblID
		&& Ques.ColName == LT_QuestionCol
		&& Ques.DataValue== Question
		&& Answ.ColName == LT_AnswerCol
		&& Desc.ColName == LT_DescCol
		&& Seq.ColName == LT_SortByCol
		&& Filt.ColName == LT_FilterCol
		&& (Filter == "" || (Filt.DataValue.Contains(Filter)))
		orderby Seq.DataValue
		select new {Answer = Answ.DataValue,Description = Desc.DataValue}).ToList()
			.Select(r => string.Join("`",r.Answer,r.Description))) + "";

1 Like

But that would have to be placed in a serverside UD method correct? I can’t really use those as a SASS/cloud user… :frowning:

Yeah, I can’t use your method because the I get the error “The following cannot be used in an Expression: Db”

Which is because we are multi-tenant SaaS users I think.

I feel like there has got to be a way to do it with the InvokeAdapterMethod but I can’t find any examples…

Correct. This must be server side. Sorry.

Is there anyway to adapt the way they are doing in in this post here to work in a configurator?

I can access ProcessCaller.InvokeAdapterMethod but I can’t access the DynamicQueryAdapter BO directly…

I finally managed to figure this out, figured I would post the solution in case anyone searching finds this thread. Was not easy to figure out how to do it. Wish they would allow you to access more of the API or add references in a configurator so I did not have to use reflection.

object[] @BAQID = new object[]
{
	"GetOEMPanelVersion"
};
var OTrans = ((InputControlValueBound<EpiTextBox, string>)Inputs["JobComments"].Value).Control.EpiTransaction;
var BAQ = Ice.Lib.Framework.AdapterHelper.GetAdapterInstance(OTrans as ILaunch, "DynamicQueryAdapter");
var ExeParm = ProcessCaller.InvokeAdapterMethod(OTrans, "DynamicQueryAdapter", "GetQueryExecutionParametersByID", @BAQID);
var ep = ExeParm.GetType().GetProperty("ExecutionParameter").GetValue(ExeParm, null);
var ClearMethod = ep.GetType().GetMethod("Clear");
ClearMethod.Invoke(ep, null);
var AddParamMethod = ep.GetType().GetMethod("AddExecutionParameterRow", new Type[] { typeof(string), typeof(string), typeof(string), typeof(bool), typeof(Guid), typeof(string) });
object[] @params3 = new object[]
{
	"Make",
	Inputs.CmbManufacturer.Value,
	"nvarchar",
	false,
	Guid.NewGuid(),
	"A"
};
object[] @params3B = new object[]
{
	"Model",
	Inputs.CmbModel.Value,
	"nvarchar",
	false,
	Guid.NewGuid(),
	"A"
};
object[] @params3C = new object[]
{
	"Deck",
	Inputs.CmbDeck.Value,
	"nvarchar",
	false,
	Guid.NewGuid(),
	"A"
};
object[] @params3D = new object[]
{
	"Panel",
	Inputs.CmbPanel.Value,
	"nvarchar",
	false,
	Guid.NewGuid(),
	"A"
};
AddParamMethod.Invoke(ep, @params3);
AddParamMethod.Invoke(ep, @params3B);
AddParamMethod.Invoke(ep, @params3C);
AddParamMethod.Invoke(ep, @params3D);
object[] @params4 = new object[]
{
	"GetOEMPanelVersion",
	ExeParm
};
var ExecuteMethod = BAQ.GetType().GetMethod("ExecuteByID", new Type[] {typeof(string),  ExeParm.GetType()});
ExecuteMethod.Invoke(BAQ, @params4);
var results = BAQ.GetType().GetProperty("QueryResults").GetValue(BAQ, null) as System.Data.DataSet;
var ResultTable = results.Tables["Results"];
4 Likes

@Evan_Purdy

Hey Evan
Can this code be used on the On Loaded Event Expression in the configurator?
I need to pull data from one Inspection Plan record based on a checkbox value to another using a BAQ parameter back to the BAQ to get a single Comment field from the inspection results table.

If it will work, I am guessing I would need to limit the number of parameters in this code snippet correct? Not a big C# / LINQ programmer but fumbling through it with help from guys like you!

Thanks for your reply in advance

Mike

It would work fine in On Page Loaded. I haven’t tried the main On Loaded Event

Evan,
Thanks very much for posting this code. This helped me immensely to develop a solution in our configurator. Four stars! :star: :star: :star: :star:
Best regards,
Allan

1 Like

Really glad to hear that! :grin:

@Evan_Purdy
This code you posted should do the trick if i can get past these two compiler errors that have us stumped

The following errors were found during compile:

CS0246 - c:\Users\miker\AppData\Local\Temp\ConfigDump\Client_PoleQCWeldInputEventCollection.cs (64,72) - The type or namespace name ‘ILaunch’ could not be found (are you missing a using directive or an assembly reference?)
CS0103 - c:\Users\miker\AppData\Local\Temp\ConfigDump\Client_PoleQCWeldInputEventCollection.cs (65,15) - The name ‘ProcessCaller’ does not exist in the current context
Total number of Errors: 2, Warnings 0

Any ideas as to how to get around these? BTW I am using this code block on a “On Field Changed” Expression which should fire when the person enters the last parameter that passes to the query.

Hmm, I think it should work fine in that event, but maybe not. Can you try it in a client side UD method just to check?

Might also be an Epicor version issue? @aball did you have to change anything in the code to get it to work?

We had to add Ice.Lib.Framework. in front of EpiTextBox which BTW was not needed in the client side UD Method just now when I tested it

This is the only compile error I get in CS UD method

CS0161 - c:\Users\miker\AppData\Local\Temp\ConfigDump\Client_PoleQCWeldUserFunctionsClientEventCollectionTmpl.cs (44,17) - ‘Erp.UI.Cfg.TEST563a34e62cdf4044b136f9c633cb2c34.UDMethod.NotesReturn()’: not all code paths return a value

Hey Evan sorry I found the error in the client side UD Method.

Just added the return “string”; at the end and it compiled. Not sure why it would not work in the “On Field Change” event expression. No worries I think I can use it like this. Will report if it works

Thanks again.

1 Like

@Evan_Purdy
Can you tell me what this line does so I know what to change “Job Comments” to?
var OTrans = ((InputControlValueBound<EpiTextBox, string>)Inputs[“JobComments”].Value).Control.EpiTransaction;

We are getting this error when I run the UD Method

The given key was not present in the dictionary.

And the Details

Application Error

Exception caught in: Erp.Lib.Configurator

Error Detail

Message: The given key was not present in the dictionary.
Program: Erp.Lib.Configurator.dll
Method: TriggerInputEvent

Client Stack Trace

at Erp.Lib.Configurator.Runtime.ConfigurationController`1.TriggerInputEvent(InputChangedArgs args)
at Erp.UI.Cfg.TEST6d983d6408e046cca662f418b891c018.Controllers._PoleQCWeldController._PoleQCWeldPage2_ColumnChanged(Object sender, DataColumnChangeEventArgs e) in c:\Users\miker\AppData\Local\Temp\ConfigDump\Client_PoleQCWeldController.cs:line 570

I’m just grabbing the current transaction scope from a control on the screen. Pick any one of your texts box inputs, it shouldn’t matter which.

Finally got it working after getting to know what was going on.

This will be a useful tool in the box. Thanks again.

1 Like