Self Ref Hierarchy How To?!?!

[bradley baker]

Im trying to setup a self ref hierarchy and I dont know what Im doing wrong.

Here is how my file is setup, its a bill of materials so there is lots of part numbers and lots of refing back to each other.  I think there are ~200k records in the file and in theory you should be able to find a BOM starting at any point.  In this example here Part Number  "6101673042" there are 10 diffrent items in the listing.  But off the top of my head I see that on step 4 there is a mfg part that has a ref part number of "400500025201" and the file has the refing compents for that also.  There can be infinite nesting and some parts are mutiple nestings so its could be:
+P1
    -C1
    -C2
    +C3/P2
        -PC2(1)
        +PC2(2)/P3
            -PC3(1)
            -PC3(2)
        -PC2(3)
        -PC2(4)
    -C4
    -C5
    -C6
    -C7

BPROD       BCHLD        BMBOMM  ICOND   BQREQ   IDESC                             ICITYP  BSEQ
6101673042  6101673040      23  1   16.300-16.73 SLV TAPPING W4"BR                      M   1
6101673042  830034001       9   1.865   GSKT STRIP GRADE 33 WATER                       7   2
6101673042  40029606001     23  6   0.750 WSHR CARBON STEEL                             P   3
6101673042  40050025201     23  1   3/4 NPT PLUG SQHD STEEL ECOAT                       M   4
BPROD        BCHLD       BMBOMM  ICOND   BQREQ   IDESC                             ICITYP  BSEQ
40050025201 40050025003     0   1   3/4 NPT PLUG SQ HD A105 FS                          P   1
40050025201 400500252019        23  1   VENDOR'S ECOATING PLUG PIPE                     P   2
6101673042  10547999        11  1   INSTR INSTL STYLE 610                               6   5
6101673042  727010619       0   1   3-1/4 X 5 PKG LABEL                                 6   6
6101673042  40029677202     0   6   0.750 NUT HEX HEAVY E-COAT CS                       P   7
6101673042  40010004250     23  6   0.750 BOLT TRK HD X 9'' L, 5'' THRD,  BARE          P   8
6101673042  918110002       0   2.2 FDA THICK FIL GREY COATG EPOXY                      P   9
6101673042  30008461504     23  1   4.0 GSKT RED RUBBER FLG 1/8"                        P   10

[Radoslav]

Hello Bradley,

Could you provide a bit more information about your data source and the approach which you use?
Are you using a self join or recursive processing?  Are you using only one table?

Working with bill of materials in not easy. A BOM represented by a structure tree, are rarely used in the practice.  However, you could try following approach :

You need one table.  The whole schema is a classic Many to Many access join:

tblMaster
Columns:

     ItemID
     ItemLevel
     ItemParentID
     ....

Now with appropriate joins you could get all parent-child relations. For example:

For a given ItemID and Level, Return all the immediate children.

SELECT ItemName, ItemParentID, ...  from tblMaster WHERE ItemID="SelectedNodeId" and ItemLevel=SelectedNodeLevel +1.

Also if you have 200 000 records I suggest that you load the nodes from the firs level and if user click on expand icon you could load the second level and etc. If you want to display the header of the master table, but hide the headers on detail tables, set the grid's ShowHeader property to True and use the ItemCreated event to hide the headers on detail tables:

protected void RadGrid1_ItemCreated(object sender, GridItemEventArgs e)
{
 if (e.Item is GridHeaderItem && e.Item.OwnerTableView != RadGrid1.MasterTableView)
 {
   e.Item.Style["display"] = "none";
 }
}

In case this does not helps, you could send me a simple project or share your grid declaration and your code behind. You could open a formal support ticket from your Telerik account and attach a ZIP file there. Once I receive it, I will do my best to help you.

Greetings,
Radoslav
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[bradley baker]

Here in a manufacturing facility they use the BOM for almost everything and in every department since Sales would look at it for quoting if the cust wants to replace the bolts from galv. to stainless etc.  You could then replace that component and now you would have the new cost of said change.

Im using 1 table that contains the following

Part Number, Facility, Sequence, Child 

http://mmo-rpg.com/telerik/mbm.png (Screenshot of structure)

http://mmo-rpg.com/telerik/mbm.csv (23MB) ~620k records

The MBM could have mutiple partnumbers but they are uniquie if you combine them on the FN and Facility.  So if you do that then you should only have 1. 

I havent started anything yet just trying to see if its possible.

I think the easiest way would be recursion but in theory a self join would also work.
Basically if the child is found with components they need to be listed then if one of the those components has components they need to be listed etc.  The way the file is setup is from AS400/ERPLX.  And in RPG you basically take the first record then take the child and subquery the file for that PN if there are records it takes the first child of that and requeries and it does this unitl there are no records for the child and then it steps backwards and moves to the next record.  I was able to repoduce the results in PHP using an array and putting all the pn in the array and then querying and outputing the data.

[Radoslav]

Hi Bradley,

If you want to use RadGrid I am afraid you can have only one unique ID which could be used as KeyName in the SelfHierarchySettings. However, you could try to create database View from the self join from your table, and if this View contains two columns as Pranet->Child you could use RadGrid Self-referencing Hierarchy. 
In case you have more than one unique ID for the self-reference hierarchy you could try using the ordinary RadGrid with NestedViewTemplate Relations or DetailsTables as shown in the below demos:

http://demos.telerik.com/aspnet-ajax/grid/examples/hierarchy/declarativerelations/defaultcs.aspx
http://demos.telerik.com/aspnet-ajax/grid/examples/programming/detailtabledatabind/defaultcs.aspx
http://demos.telerik.com/aspnet-ajax/grid/examples/hierarchy/nestedviewtemplatedeclarativerelations/defaultcs.aspx
 
Also, I suggest you to use self join and SQL queries instead of recursion, because the data is too big. With big data and many nested recursive calls to а method, the recursion holds potential danger of overfilling the memory.

I hope this helps.

Sincerely yours,
Radoslav
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[bradley baker]

WITH BOMCTE
AS
(
  SELECT *
  FROM MBM
  WHERE BPROD = '00040000190'
  UNION ALL
  SELECT MBM.*
  FROM MBM
    JOIN BOMCTE
      ON MBM.BPROD = BOMCTE.BCHLD

)
SELECT * FROM BOMCTE

With the above CSV above run this query and let me know if this will work

This is the output but as you can see there are 2 SEQ #1's because *7902 is a MFG Product and there is a BoM for it.  Can this output work for the self hierarchy using the BPROD/BCHLD

RRN400  BID BPROD   BMWHS   BMBOMM  BSEQ    BCHLD   BQREQ   BDEFF   BDDIS   BCLAS   BCLAC   BMSCP   BBUBB   BOPNO   BLLOT   BULOT   BDOFF   BCSTP   BMISS   BUSC    BMREOP  BMBDC   BMOFAC  BMORDT  BMECN   BMLINE  BMRDF   BMCFAC  BMMNDT  BMMNTM  BMMNUS  BMENDT  BMENTM  BMENUS  BMEFTM  BMEFTZ  BMDDTM  BMDDTZ  BMENTZ  BMMNTZ  BMMNPG  BMEXCM  BMAUTO  BMLOT   BMEXCS  BMEXPL  TBUPDT  TBUPTM
301656  BM  00040000190                         010     3   40019036077                         2.000000    20000127    99999999    C   C   1.0000  0   0                                                           0   0.0000  N       0   0                           0   0       20000127    80239   BPCSLCS     20000127    80239   BPCSLCS     0       0                           0   0   0   0   0   20100105    154730
301654  BM  00040000190                         010     1   00040007902                         1.000000    20000127    99999999    C   C   1.0000  0   0                                                           0   0.0000  N       0   0                           0   0       20000127    80230   BPCSLCS     20000127    80230   BPCSLCS     0       0                           0   0   0   0   0   20100105    154730
301657  BM  00040000190                         010     4   00010556999                         1.000000    20000922    99999999    C   S   1.0000  0   0                                                           0   0.0000  N       0   0                           0   0       20000922    91327   BPCSLCS     20000922    91327   BPCSLCS     0       0                           0   0   0   0   0   20100105    154730
301655  BM  00040000190                         010     2   40010656223                         2.000000    20000127    99999999    C   C   1.0000  0   0                                                           0   0.0000  N       0   0                           0   0       20000127    80235   BPCSLCS     20000127    80235   BPCSLCS     0       0                           0   0   0   0   0   20100105    154730
301658  BM  00040000190                         010     5   727091202                           0.200000    20090701    99999999    C   W   1.0000  0   0                                                           0   0.0000  N       0   0                           0   0       20090701    73514   BPCSJEL     20090701    73445   BPCSJEL     0       0                           0   0   0   0   0   20100105    154730
301774  BM  00040007902                         010     1   200400070011                        2.000000    19990523    99999999    C   C   1.0000  0   0                                                           0   0.0000  N       0   0                           0   0       19990523    75901   CONVERT     19990523    75901   CONVERT     0       0                           0   0   0   0   0   20100105    154730
301775  BM  00040007902                         010     2   918110002                           3.500000    20060131    99999999    C   C   1.0000  0   0                                                           0   0.0000  N       0   0                           0   0       20060131    85349   BPCSJEL     20060131    85349   BPCSJEL     0       0                           0   0   0   0   0   20100105    154730

[Radoslav]

Hello Bradley,

From that I saw in the csv file which you sent us I can tell that your relation is many -to-many.  I do not see any parent-child relation, for example there is a 104 rows with BCHLD = P03-N06037-3900  but there is no one row  with BPROD = P03-N06037-3900. So you could not use BPROD-> BCHLD as parent child relation. Also there are 8 rows with [BPROD] = ACS-PC11-230VN4, but all 8 rows are different. In hierarchy one item have many children, not many have many. So in your case you could not use self hierarchy, I suggest you to try using ordinary hierarchy model of  the RadGrid.

Additionally I try to execute the SQL query which you sent us in your last post, but I do not receive any data - I attach a screenshots with the results of execution of the query. I try to remove the  WHERE BPROD = '00040000190', however the result holds up to 300k rows and they are still with relation many-to-many.

In order to achieve the parent-child hierarchy you need to have one column which holds the Primary Key , maybe (RRN400) and another column with Foreign Keys. They have to point to the primary keys. For example: Employees table from Nortwind database. If you have PK and FK you could use Grid with self-referencing hierarchy.

I hope this helps.

Sincerely yours,
Radoslav
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[bradley baker]

I have it sort of working now =)

http://mmo-rpg.com/telerik/self_ref_1.png
http://mmo-rpg.com/telerik/self_ref_2.png

Here are my pages, what am I missing, also how would I make it so info under the 2nd indent would indent further.  Also why do I still have the filter boxes?  Im using the same code provided in the example.  (Im using SQL 2008 SP1 if thats why your query wont run?)

<%@ Page Title="" Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="bom.aspx.vb" Inherits="bradford_zpn_bom" %>

<asp:Content ID="Content1" ContentPlaceHolderID="Header" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="PrimaryContent" Runat="Server">
    <asp:SqlDataSource ID="BOMSQL" runat="server"
        ConnectionString="<%$ ConnectionStrings:ERPLXFConnectionString %>" SelectCommand="WITH BOMCTE
AS
(
  SELECT *
  FROM MBM
  WHERE BPROD = '00040000190'
  UNION ALL
  SELECT MBM.*
  FROM MBM
    JOIN BOMCTE
      ON MBM.BPROD = BOMCTE.BCHLD

)
SELECT cast(BCHLD as varchar(100))as EmpID, cast(BPROD as varchar(100))as ReportTo  FROM BOMCTE
UNION ALL
SELECT cast(ICPROD as varchar(100))as EmpID, cast(NULL as varchar(100))as ReportTo
FROM CIC
WHERE ICFAC = '010' and ICPROD = '00040000190' "></asp:SqlDataSource>

<telerik:RadGrid ID="RadGrid1" runat="server" AllowFilteringByColumn="true"  OnItemCreated="RadGrid1_ItemCreated" OnColumnCreated="RadGrid1_ColumnCreated"
        AllowPaging="true" AllowSorting="True" DataSourceID="BOMSQL" GridLines="None">
<MasterTableView AutoGenerateColumns="False" DataKeyNames="EmpID, ReportTo" HierarchyDefaultExpanded="true" HierarchyLoadMode="Client"
            DataSourceID="BOMSQL">
            <SelfHierarchySettings ParentKeyName="ReportTo" KeyName="EmpID" />
<RowIndicatorColumn>
<HeaderStyle Width="20px"></HeaderStyle>
</RowIndicatorColumn>

<ExpandCollapseColumn>
<HeaderStyle Width="20px"></HeaderStyle>
</ExpandCollapseColumn>
    <Columns>
        <telerik:GridBoundColumn DataField="EmpID" DefaultInsertValue=""
            HeaderText="EmpID" SortExpression="EmpID" UniqueName="EmpID">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="ReportTo" DefaultInsertValue=""
            HeaderText="ReportTo" SortExpression="ReportTo" UniqueName="ReportTo">
        </telerik:GridBoundColumn>
    </Columns>
</MasterTableView>
    </telerik:RadGrid>
</asp:Content>

Imports System
Imports Telerik.Web.UI
Imports System.Reflection

Partial Class bradford_zpn_bom
    Inherits System.Web.UI.Page

    Protected Sub BOMSQL_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles BOMSQL.Selecting
        e.Command.CommandTimeout = 0
    End Sub
    Protected Sub RadGrid1_ColumnCreated(ByVal sender As Object, ByVal e As GridColumnCreatedEventArgs) Handles RadGrid1.ColumnCreated
        If TypeOf e.Column Is GridExpandColumn Then
            e.Column.Visible = False
        ElseIf TypeOf e.Column Is GridBoundColumn Then
            e.Column.HeaderStyle.Width = Unit.Pixel(100)
        End If
    End Sub

    Public Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
        If Assembly.GetAssembly(GetType(ScriptManager)).FullName.IndexOf("3.5") <> -1 Then
            RadGrid1.MasterTableView.FilterExpression = "it[""ReportTo""] = Convert.DBNull"
        Else
            RadGrid1.MasterTableView.FilterExpression = "ReportTo IS NULL"
        End If
    End Sub

    Public Sub Page_PreRenderComplete(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.PreRenderComplete
        HideExpandColumnRecursive(RadGrid1.MasterTableView)
    End Sub

    Public Sub HideExpandColumnRecursive(ByVal tableView As GridTableView)
        Dim nestedViewItems As GridItem() = tableView.GetItems(GridItemType.NestedView)
        For Each nestedViewItem As GridNestedViewItem In nestedViewItems
            For Each nestedView As GridTableView In nestedViewItem.NestedTableViews
                nestedView.Style("border") = "0"

                Dim MyExpandCollapseButton As Button = DirectCast(nestedView.ParentItem.FindControl("MyExpandCollapseButton"), Button)
                If nestedView.Items.Count = 0 Then
                    If Not MyExpandCollapseButton Is Nothing Then
                        MyExpandCollapseButton.Style("visibility") = "hidden"
                    End If
                    nestedViewItem.Visible = False
                Else
                    If Not MyExpandCollapseButton Is Nothing Then
                        MyExpandCollapseButton.Style.Remove("visibility")
                    End If
                End If

                If nestedView.HasDetailTables Then
                    HideExpandColumnRecursive(nestedView)
                End If
            Next
        Next
    End Sub

    Protected Sub RadGrid1_ItemCreated(ByVal sender As Object, ByVal e As GridItemEventArgs) Handles RadGrid1.ItemCreated
        CreateExpandCollapseButton(e.Item, "EmpID")

        If TypeOf e.Item Is GridHeaderItem AndAlso Not e.Item.OwnerTableView Is RadGrid1.MasterTableView Then
            e.Item.Style("display") = "none"
        End If

        If TypeOf e.Item Is GridNestedViewItem Then
            e.Item.Cells(0).Visible = False
        End If
    End Sub

    Protected Sub RadGrid1_ItemDataBound(ByVal sender As Object, ByVal e As GridItemEventArgs) Handles RadGrid1.ItemDataBound
        CreateExpandCollapseButton(e.Item, "EmpID")
    End Sub

    Public Sub CreateExpandCollapseButton(ByVal item As GridItem, ByVal columnUniqueName As String)
        If TypeOf item Is GridDataItem Then
            If item.FindControl("MyExpandCollapseButton") Is Nothing Then
                Dim button As New Button()
                AddHandler button.Click, AddressOf button_Click
                button.CommandName = "ExpandCollapse"
                button.CssClass = IIf((item.Expanded), "rgCollapse", "rgExpand")
                button.ID = "MyExpandCollapseButton"

                If item.OwnerTableView.HierarchyLoadMode = GridChildLoadMode.Client Then
                    Dim script As String = [String].Format("$find(""{0}"")._toggleExpand(this, event); return false;", item.Parent.Parent.ClientID)

                    button.OnClientClick = script
                End If

                Dim level As Integer = item.ItemIndexHierarchical.Split(":"c).Length
                If level > 1 Then
                    button.Style("margin-left") = level + 10 & "px"
                End If

                Dim cell As TableCell = (DirectCast(item, GridDataItem))(columnUniqueName)
                cell.Controls.Add(button)
                cell.Controls.Add(New LiteralControl("&nbsp;"))
                cell.Controls.Add(New LiteralControl((DirectCast(item, GridDataItem)).GetDataKeyValue(columnUniqueName).ToString()))
            End If
        End If
    End Sub

    Sub button_Click(ByVal sender As Object, ByVal e As EventArgs)
        CType(sender, Button).CssClass = IIf((CType(sender, Button).CssClass = "rgExpand"), "rgCollapse", "rgExpand")
    End Sub

End Class

[bradley baker]

Nevermind I was able to fix it and everything is working perfectly now =).