Yes, here is one example of code that returns a list of valid answers to populate a combo box… this code allows changing of the column names in the linq query. Note that becuase the lookup table is not a traditional table, you must join each separate column.
/*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))) + "";