Hi @Stuart
Thank you very much.
Hope not attracting too much spams.
email: cecilia.chiu@yahoo.com
Regards,
Ceci
Hi Stuart,
I have a similar query.
I want values either from a look up table, or a drop down list.
if check box = true, then use the (dynamic?) list (user input)
If not, read value from look-up table
can you share the example you can Cecilia?
after test per your code provided, it looks great.
however, as record size growth, will the lookup table becomes bottleneck of the queries?
sorry 1 more question. I have looked around the manual, is it only be maintainable in Lookup Table function, or can be developed updatable BAQ/Dashboard like other tables so that endusers can paste insert or import into specific lookup table/columns?
Hi Cecilia,
We use thousands of small lookup tables rather than 1 large lookup table so its hard to say for your experience. All the data is stored in 1 table in Epicor and we have had no performance issues despite having a large number of tables.
Regards
Stuart
1 Like
Hi Cecilia,
I am also a new user of Epicor and would like to know how to access the lookup table. Would you mind sharing the codes of your example? Thank you so much!
i am still studying. I think Stuart should be the right person you ask to.
Iām not sure if this has already been hashed out but I primarily use Lookup Tables for my configurators and use user defined methods to grab the data. It has worked well for me so far with some limitations. Mainly that I cannot figure a good way to write to the lookup table in the configurator only read the data. The UD Method I use to extract the data is a little more code than what I used to use for UD Tables but I think the ability to modify the table on the fly out weights the code required to get it.
okā¦ so I did figure out how to do write C# queries that will access a lookup table. It is not for the faint of heartā¦ i did this for dynamic queries. my dynamic queries allow for some fancy filters to be applied to the data and return the correct values. Below is a sample of the code with a sample of the data that is found in the lookup table.
The biggest challenge in the lookup tables is the fact that every value is represented in a separate row in the lookup table. so if you have a 10 column/50 row table, you will have 500 records that need to be joined to get the data. that is why there are so many joins in this query.
ALSO, if your individual lookup tableās have 1000s of rows, you might also think about indexing the two fields DataValue and ColName since they are so heavily used in these lookups.
I have given this codeset to multiple customers and to user groups / insightsā¦ modify, test and use as you desire.
/*ComboQuestionLookup4Filters - 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 these columns (order of these fields does not need to be this order, but this is nice)
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
Filter1 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
Filter2 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
Filter3 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
Filter4 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
Filter1 - this is the value of the filter1. IF this is blank, then it is ignored. If it is NOT blank, then it will search in the filter1 column using "Contains" feature
Filter2 - this is the value of the filter2. IF this is blank, then it is ignored. If it is NOT blank, then it will search in the filter2 column using "Contains" feature
Filter3 - this is the value of the filter3. IF this is blank, then it is ignored. If it is NOT blank, then it will search in the filter3 column using "Contains" feature
Filter4 - this is the value of the filter4. IF this is blank, then it is ignored. If it is NOT blank, then it will search in the filter4 column using "Contains" feature
Sample Table:
Question Answer Description Sequence Filter1 Filter2 Filter3 Filter4
Color Red Red color B Red Medium * *
Color LRed Light Red A Red Light * *
Color DRed Dark Red C Red Dark * *
Color Blue Blue color E Blue Medium * *
Color LBlue Light Blue D Blue Light * *
Color DBlue Dark Blue F Blue Dark * *
Color Green Green Color H Green Regular * *
Color LGreen Light Green G Green Light * *
Color Teal Teal H Blue, Green Medium * *
Color Turq Turquoise I Blue, Green Medium * *
Color DGreen Dark Green J Green Dark * *
Size Small < 5" long A * * * *
Size Med >=5" <10" B * * * *
Size Large >= 10" C * * * *
in the above table,
If I pass "Color" as the question and the following FILTER:
"Dark" as a filter2, and only receive the three dark colors.
"Red" to the filter1, and only get the red colors.
"Red" to the filter1, and Dark to Filter2, 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 = "SEQUENCE"; /* we sort by this Col.*/
string LT_FilterCol1 = "FILTER1"; /* we will further filter by this column.*/
string LT_FilterCol2 = "FILTER2"; /* we will further filter by this column.*/
string LT_FilterCol3 = "FILTER3"; /* we will further filter by this column.*/
string LT_FilterCol4 = "FILTER4"; /* 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 Sort in Db.PcLookupTblValues on new { Ques.Company, Ques.RowNum, Ques.LookupTblID } equals new { Sort.Company, Sort.RowNum, Sort.LookupTblID }
join Flt1 in Db.PcLookupTblValues on new { Ques.Company, Ques.RowNum, Ques.LookupTblID } equals new { Flt1.Company, Flt1.RowNum, Flt1.LookupTblID }
join Flt2 in Db.PcLookupTblValues on new { Ques.Company, Ques.RowNum, Ques.LookupTblID } equals new { Flt2.Company, Flt2.RowNum, Flt2.LookupTblID }
join Flt3 in Db.PcLookupTblValues on new { Ques.Company, Ques.RowNum, Ques.LookupTblID } equals new { Flt3.Company, Flt3.RowNum, Flt3.LookupTblID }
join Flt4 in Db.PcLookupTblValues on new { Ques.Company, Ques.RowNum, Ques.LookupTblID } equals new { Flt4.Company, Flt4.RowNum, Flt4.LookupTblID }
where Ques.LookupTblID == LookupTblID &&
Ques.ColName == LT_QuestionCol && Ques.DataValue == Question &&
Answ.ColName == LT_AnswerCol &&
Desc.ColName == LT_DescCol &&
Sort.ColName == LT_SortByCol &&
Flt1.ColName == LT_FilterCol1 && (Filter1 == "" || (Flt1.DataValue.Contains(Filter1))) &&
Flt2.ColName == LT_FilterCol2 && (Filter2 == "" || (Flt2.DataValue.Contains(Filter2))) &&
Flt3.ColName == LT_FilterCol3 && (Filter3 == "" || (Flt3.DataValue.Contains(Filter3))) &&
Flt4.ColName == LT_FilterCol4 && (Filter4 == "" || (Flt4.DataValue.Contains(Filter4))) orderby Sort.DataValue select new { Answer = Answ.DataValue, Description = Desc.DataValue }).ToList()
.Select(r => string.Join("`", r.Answer, r.Description))) + "";
3 Likes
Hi Tim,
Iāve been trying to work with this bit of code, Iām new to writing these types of queries and configurators in general. I know this is an old thread, so are you still around in this game and available to peg with questions?
Thanks
If you arenāt using PC Lookup tables already I encourage people not to use them. If you arenāt too far along it is worth considering other approaches. PC lookup tables are easy to get started with but can run out of steam if relationships between choices get involved.
With that said happy to answer any questions about them.
1 Like
Hi Matt,
I havenāt seen this thread before, but I have a lot of similar lookup tables and queries based on them. What are you trying to do?
We are just mocking things up really, so weāre open to other suggestions. Configurator definitely feels a bit rough. Even with the tech ref guide there is a lack of real examples in code.
What method do you prefer as opposed to Lookup tables? Weāre potentially going to add most of the lookup table data as part attributes as well.
Thanks Kevin, weāre trying to build a part configurator where multiple inputs effect the selection of a subsequent input. Weād also like to have that flow backwards so users can answer in any order.
The flowing backwards is where you will have issues with the Epicor configurators. I was able to pretty efficiently use lookup tables and filter based on previous inputs but flowing backwards was an issue and it didnāt matter where the table set I was using came from.
If I have time I can see if I can put something quick together on what I did.
I use the standard user defined tables, they have multiple keys so you can easily build relationships between inputs. For example if you sell shoes in different sizes and colors you can used Key1 for shoe size and then Key2 for color. If you sell size 10 in red, blue or yellow we have three records. If Size 11 is only available in blue we have one record. Then in one of the short char fields we put the part number for that size/color combination. Character 01 has the description of size , Character 2 has the description for color.
The first combo uses Key1 for its value showing the description from character 01. We then filter the color combo based on the selection in the size combo so we only see the colors available for that size.
We could easily do this for style/size/color using three keys.
I can write ONE dynamic list lookup user defined routine to handle any combination of keys. The query is very simple and easily understood
All of this is possible with PC Lookup tables but the code gets ugly fast.
Here is a sample using 8 different combo boxes for data selection and each choice will narrow the lists down dynamically. Offering 2 different selection methods for boat trailer selection either boat mfg fit (mostly OEMs) or internal params used to provide a base trailer configuration. Maybe this will help give you ideas.
All my combo boxes are dynamic lists based on this code:
// Enter valid C# code and do not forget this method returns delimited lists through out parameters.
var DynDS = ((
from row in Db.PcConData
where
row.Company == Context.CompanyID
&& (row.PartNum == Context.ConfigurationID || row.PartNum.ToUpper() == "BOATFITS")
&& (row.TypeCode == sTrailerType || sTrailerType == "")
&& (row.Key1 == sTrailerModel || sTrailerModel == "")
&& (row.Key2 == sFinish || sFinish == "")
&& (row.Key3 == sDriveType || sDriveType == "")
&& (row.Key4 == sCapacity || sCapacity == "")
&& (row.Key5 == sYear || sYear == "")
&& (row.Key6 == sBoatMfg || sBoatMfg == "")
&& (row.Key7 == sBoatModel || sBoatModel == "")
// Year, Finish, ConfigSet, TrailerType, TrailerModel
orderby row.Key5, row.Key2, row.PartNum, row.TypeCode, row.Key1
select new
{
ConfigSet = row.PartNum.ToUpper(),
TrailerType = row.TypeCode,
TrailerModel = row.Key1,
TrailerFinish = row.Key2,
BoatDriveType = row.Key3,
TrailerCapacity = row.Key4,
Year = row.Key5,
BoatMfg = row.Key6,
BoatModel = row.Key7
}));
// Return Data requested.
switch(sListName){
case "TrailerType":return string.Join("~", DynDS.Where(r=> r.BoatMfg==sBoatMfg || r.BoatMfg=="").OrderBy(r => r.TrailerType).Select(r => r.TrailerType).Distinct());break;
case "TrailerModel":return string.Join("~", DynDS.Where(r=> r.BoatMfg==sBoatMfg || r.BoatMfg=="").OrderBy(r => r.TrailerModel).Select(r => r.TrailerModel).Distinct());break;
case "TrailerFinish":return string.Join("~", DynDS.OrderBy(r => r.TrailerFinish).Select(r => r.TrailerFinish).Distinct());break;
case "TrailerCapacity":return string.Join("~", DynDS.OrderBy(r => r.TrailerCapacity).Select(r => r.TrailerCapacity).Distinct());break;
// BoatFit Data
case "Year":return string.Join("~", DynDS.OrderBy(r => r.Year).Select(r => r.Year).Distinct());break;
case "BoatDriveType":return string.Join("~", DynDS.OrderBy(r => r.BoatDriveType).Select(r => r.BoatDriveType).Distinct());break;
case "BoatModel":return string.Join("~", DynDS.OrderBy(r => r.BoatModel).Where(r =>r.BoatMfg != "" ).Select(r => r.BoatModel).Distinct());break;
case "BoatMfg":return string.Join("~", DynDS.OrderBy(r => r.BoatMfg).Where(r =>r.BoatMfg != "" ).Select(r => r.BoatMfg).Distinct());break;
}
return "";
I use a generic UD method that can handle a range of filtering requirements. This is using PcConData table where you have to add fields but has the advantage of having 9 keys vs the 5 keys from standard UD tables. TypeCode handles the ability to store different types of data in the same table. TypeCode = āENGINEā or TYPECODE = āDEPTHFINDERā. The display member and value member are dynamic too.
// Enter valid C# code and do not forget this method returns a string value.
String valueMember = String.Empty;
StringBuilder MyList = new StringBuilder ();
Boolean ok = false;
var query = (from r in Db.PcConData where r.Company == Context.CompanyID
&& r.PartNum == PartNum
&& r.TypeCode == TypeCode select r);
var filterValues = new List<string> { String.Empty, Key1, Key2, Key3, Key4, Key5, Key6 };
for (int i = 1; i < 7; i++)
// start with 1 to reduce confusion or add to confusion.
{
if (!string.IsNullOrEmpty (filterValues[i]))
{
switch (i)
{
case 1:
query = query.Where (u => u.Key1 == Key1);
query = query.OrderByDescending (r => r.Key1);
// assume sort by key value
break;
case 2:
query = query.Where (u => u.Key2 == Key2);
query = query.OrderByDescending (r => r.Key2);
break;
case 3:
query = query.Where (u => u.Key3 == Key3);
query = query.OrderByDescending (r => r.Key3);
break;
case 4:
query = query.Where (u => u.Key4 == Key4);
query = query.OrderByDescending (r => r.Key4);
break;
case 5:
query = query.Where (u => u.Key5 == Key5);
query = query.OrderByDescending (r => r.Key5);
break;
case 6:
query = query.Where (u => u.Key6 == Key6);
query = query.OrderByDescending (r => r.Key6);
break;
}
}
}
switch (SortOrder)
{
case "N":
case "N1":
query = query.OrderBy (r => r.SortOrder_c); // these allow me to do a custom sort of items.
break;
case "N2":
query = query.OrderBy (r => r.SortOrder02_c);
break;
case "N3":
query = query.OrderBy (r => r.SortOrder03_c);
break;
}
valueMember = ValueMember;
ok = true;
if (ok)
{
foreach (var line in query)
{
String test = line[valueMember] + "`" + line[DisplayMember] + "~";
if (!MyList.ToString ().Contains (test)) // there is a clearer way to do this but haven't circled back to do it.
{
MyList.Append (test);
}
}
}
return ((MyList.ToString ()).Length > 0) ? (MyList.ToString ()).Substring (0, (MyList.ToString ()).Length - 1) : ("N`None");
1 Like