Is it possible to pass parameters to a BAQ from a product configurator page? (Cannot use serverside methods, as I am a SAAS user). I know you can filter the results after in a combobox, but it would be considerably better to not return thousands of rows just to filter it down to 1 or 2 values when the BAQ could do that for me, if I could pass a parameter.
1 Like
Evan â
There is a way to send the EpiBinding to the object calling the BAQ. Iâm not sure if this will handle your needs: https://www.youtube.com/watch?v=O6VTAZ2sVyg This was a presentation I believe for the Epicor Minnesota User Group.
You could also use the .Net or REST services to call the BAQ with the parameter youâre looking to add to the condition.
Hope this helps,
Calvin Dekker
(630) 672-7688 x1484
<img border="0" width="130" height="38" style="width:1.3541in;height:.3958in" id="Picture_x0020_7" src="/uploads/default/original/2X/a/a18cd3baa22719a50368d500fd0954dceb81ecc5.jpg" alt="ESP2">
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.
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⌠
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
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! 
Best regards,
Allan
1 Like
Really glad to hear that! 
@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