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))) + "";
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"];
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!
@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.
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
@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