Grid with self-referencing and detail tables

[Ken]

I am trying to find the right way to build a hierarchical grid. I will present a case much like what I want, showing all of the cases that my application will need.

Checklist One

|

+- What is your age?

   |

   +- Age : Under 40

   |  |

   |  +- Do you like cake? (Available answers : Yes, no) (Score: 3)

   |  |

   |  +- Do you like pie? (Available answers : Yes, no) (Score: 3)

   |  |

   |  +- Do you like cake more than pie? (Available answers : Yes, no, equally) (Score: 2)

   |

   +- Age : At or Over 40

      |

      +- Do you like cake? (Available answers : Yes, no) (Score: 3)

      |

      +- Do you like pie? (Available answers : Yes, no) (Score: 3)

      |

      +- Do you like cake more than pie? (Available answers : Yes, no, equally) (Score: 2)

      |

      +- Do you diet often?

         |

         +- Answer : More or less, yes.

         |  |

         |  +- Does your love of cake and/or pie interfere with your dieting? (Available answers : Yes, no) (Score: 1)

         |  |

         |  +- Do you wish you loved cake and/or pie less?

         |     |

         |     +- Answer : It would help, yes.

         |     |  |

         |     |  +- Would you rather love cake or pie less? (Available answers : Cake, pie, both) (Score: 1)

         |     |

         |     +- Answer : No. Dieting is the price I pay to enjoy cake and/or pie.

         |     |

         |     +- Answer : Heck, no! You're crazy for even thinking that.

         |        |

         |        +- Are you offended at the thought of loving cake or pie less? (Available answers : Yes, no) (Score: 1)

         |        |

         |        +- Would you like some cake and/or pie right now? (Available answers : Yes, no) (Score: 1)

         |    

         +- Answer : No, dieting is not something I need or want to do.

Checklist Two
|
+- Do you like coffee? (Available answers : Yes, no) (Score: 3)
|
+- Do you like tea? (Available answers : Yes, no) (Score: 3)


In the above view, you can see that there are multiple types of data.

The first is the root node - I show two, titled 'Checklist One' and 'Checklist Two'. As you can see, one is complex, while the other is simple.There will be multiple root nodes that all have multiple questions of varying depth that descend from them. I would like to have the ability to view multiple root nodes and all of the resulting questions, but my requirements only ask to see one at a time.

The next levels are characterized by questions that lead to further questions, or ending questions. You can see that each has a different structure. 

Some questions may have further child questions. These questions have free-form answers possible and nothing else. These will never be at the root of the tree, but a given answer may terminate as a leaf. Furthermore, they can have no limit to how often one question descends from another. If you look at Checklist One, you can see a case where the non-root/non-leaf nodes go three levels deep before terminating in the end (leaf) questions, and how two free-form answers end with no further child questions. I would like to have as many levels of depth as possible, but it is unlikely that I will see more than three levels, as I have illustrated above.

Some questions never have child questions. These questions have a list of possible answers (yes, no, equally, both, cake, pie) and there is also an associated 'score'. This is entirely different from the questions above, so they will need to display their data differently. These questions are leaves in the tree. They will never be the root, but they may be connected to the root directly. (Look at Checklist Two for an example where there is a root node - Checklist Two - and two leaf nodes - the coffee question and the tea question. There are no middle nodes there.)

In my database, the root nodes are held in one table, the leaf questions are held in another, and the non-root/non-leaf questions are held in a looping parent-child structure in a third set of tables. Of course, I will be presenting the grid(s) with data via LINQ so the table structures themselves are not important, as I can just create anonymous objects to carry my data.

How should I build this grid? It seems that there is both self-referencing and multiple detail tables, but I haven't found a way to mix the two correctly in a RadGrid.

[Ken]

I am still looking for assistance with this issue. Is this possible with a RadGrid?

It currently appears that I must manipulate the data such that all forms of data have the same object signature, regardless of the data they hold. I haven't figured out a good way to modify the display per type of data, however.

[Ken]

I am still looking for assistance with this issue.

[Pavlina]

Hi Ken,

To achieve the desired functionality you can try using Telerik AJAX TreeList control which is the perfect fit for scenarios in which you will need to combine treeview and grid in one control. Its hybrid structure empowers you with the ability to visualize self-referencing data in a hierarchical view defining primary/foreign key relations and using identical objects for source.

Give it a try and see if it works for you. 

Regards,
Pavlina
the Telerik team

[Ken]

Right now, it's not, and I'm not sure why. The data I'm sending in to the DataSource appears to be correct - I've spot-checked it, although I need to bind it against a RadGrid to be 100% certain. I may not be using the RadTreeView correctly.

Code is below. 

<%@ Page Title="" Language="C#" MasterPageFile="~/App.Master" AutoEventWireup="true" CodeBehind="TestGrid.aspx.cs" Inherits="BankOpsAdmin.DocTracking.TestGrid" %>

<asp:Content ID="Content1" ContentPlaceHolderID="MidlandContentPlaceHolder" runat="server">
       <telerik:RadTreeList runat="server" ID="radTreeList" AllowLoadOnDemand="false" AllowPaging="false" AllowSorting="false"
              DataKeyNames="Ref" ParentDataKeyNames="Parent_Ref" AutoGenerateColumns="false">
              <Columns>
                     <telerik:TreeListBoundColumn DataField="DisplayText"></telerik:TreeListBoundColumn>
              </Columns>
       </telerik:RadTreeList>
</asp:Content>


       public partial class TestGrid : BasePage
       {
              public TestGrid()
              {
                     Name = "TEST PAGE";
                     Description = "TEST PAGE";
                     EnableSecurity = false;
              }

              protected override void WireupEvents()
              {
                     radTreeList.NeedDataSource += radTreeList_NeedDataSource;
              }

              void radTreeList_NeedDataSource(object sender, TreeListNeedDataSourceEventArgs e)
              {
                     if (radTreeList.DataSource == null)
                     {
                           DocTrackingBLL.FillChecklistTables();

                           var headList = from head in DocTrackingBLL.AppDataSet.tbl_Checklist_Head
                                                   select new
                                                   {
                                                          Parent_Ref = "none",
                                                          Ref = "head",
                                                          DisplayText = head.Checklist_Title
                                                   };
                           var headCheckList = from check in DocTrackingBLL.AppDataSet.tbl_Checklist
                                                              join head in DocTrackingBLL.AppDataSet.tbl_Checklist_Head
                                                              on check.Checklist_Ref equals head.Checklist_Ref
                                                              select new
                                                              {
                                                                     Parent_Ref = "head",
                                                                     Ref = string.Format("{0}check", check.Checklist_Ref),
                                                                     DisplayText = check.IsChecklist_Child_QuestionNull() ? string.Empty : check.Checklist_Child_Question
                                                              };
                           var checkListToChild = from check in DocTrackingBLL.AppDataSet.tbl_Checklist
                                                                 join pc in DocTrackingBLL.AppDataSet.tbl_Checklist_Parent_Child
                                                                 on check.Checklist_Ref equals pc.Checklist_Child_Ref
                                                                 select new
                                                                 {
                                                                        Parent_Ref = string.Format("{0}rel", pc.Checklist_PC_Ref),
                                                                        Ref = string.Format("{0}check", pc.Checklist_Child_Ref),
                                                                        DisplayText = check.IsChecklist_Child_QuestionNull() ? string.Empty : check.Checklist_Child_Question
                                                                 };
                           var checkListParentToChild = from check in DocTrackingBLL.AppDataSet.tbl_Checklist
                                                                           join pc in DocTrackingBLL.AppDataSet.tbl_Checklist_Parent_Child
                                                                           on check.Checklist_Ref equals pc.Checklist_Child_Ref
                                                                           select new
                                                                           {
                                                                                  Parent_Ref = string.Format("{0}check", check.Checklist_Ref),
                                                                                  Ref = string.Format("{0}rel", pc.Checklist_PC_Ref),
                                                                                  DisplayText = pc.Checklist_Child_Answer
                                                                           };
                           var questionList = from question in DocTrackingBLL.AppDataSet.tbl_Checklist_Questions
                                                          select new
                                                          {
                                                                 Parent_Ref = string.Format("{0}check", question.Checklist_Ref),
                                                                 Ref = string.Format("{0}question", question.Checklist_Question_Ref),
                                                                 DisplayText = string.Format("{0} (Possible answers : {1}) (Reason Code : {2})",
                                                                 question.Checklist_Question, GetAnswers(question.Checklist_Question_Ref),
                                                                 DocTrackingBLL.AppDataSet.tbl_Reason_Code.FindByReason_Code_Ref(question.Checklist_Question_Reason_Code_Ref).Reason_Code)
                                                          };
                           var topDataSource =
                                  headList.Union(headCheckList).Union(checkListToChild).Union(checkListParentToChild).Union(questionList);
                           radTreeList.DataSource = topDataSource;
                     }
              }

              private string GetAnswers(int checklist_question)
              {
                     return string.Join(", ", (from answer in DocTrackingBLL.AppDataSet.tbl_Checklist_Answers
                                                                join qa in DocTrackingBLL.AppDataSet.tbl_Checklist_QA
                                                                on answer.Checklist_Answer_Ref equals qa.Checklist_Answer_Ref
                                                                where qa.Checklist_Question_Ref == checklist_question
                                                                select answer.Checklist_Answer).ToArray());
              }
       }

[Ken]

By the way, you'll notice that I hid some of the complexity of my data in my original question. I figured that the fact that I had three different types of data would be enough of a problem.

Regardless, I appreciate the assistance that you are lending me at this time.

[Ken]

I've solved my issue. For the record, the parent nodes must have a nullable property set as the parent key.

Using the TreeView is not the best solution for my problem, but it may be the only workable solution. Thank you.