Binding to table with recursive join

[Administrator]

Hi there,

I searched the forums and knowledge base but did not see anything on binding to a table in VB.Net that has a recursive join. The join creates a hierarchy structure and it would be very nice if the grid control would support this.

Thanks,

Wes

[Ramu]

Hi Wes,

We are looking into it and will update you as soon as possible.

Thanks,
Ramu.K

[Administrator]

Hi Ramu,

Any luck yet? I can get it to load the data, but it''s all messed up and not in the format I was expecting. It''s not nested at all.

Thanks,

Wes

>Hi Wes,

We are looking into it and will update you as soon as possible.

Thanks,
Ramu.K

[Ramu]

Hi Wes,

This is possible in our grid. Here is the sample that shows the Grid in a hierarchical manner.

Our grid will not automatically display in hierarchy structure by using the join query. To display grid in hierarchical form you have to define the “GridRelation” for parent and child table.

Steps to define hierarchical grid:

1) In the properties window choose “GridRelation” under Table Descriptor.

2) “GridRelationsDescriptor Collection Editor” opens up.

3) Add a new member and then choose the childTableName to the member.

4) Choose “RelatedMasterDetails” value for Relationkind property.

5) Choose the “RelationKeys” property. “RelationKeyDescriptor Collection Editor” opens up.

6) Add a new member and choose “ChildKeyFieldName” and “ParentKeyFieldName” to the member.

7) click “ok” button. Displays the grid in hierarchical form.

In this sample I have recursively joined with “OrdereID” column.I have used Orders table from NWIND Database. Please have a look at the sample and let me know if you have any queries.

recursive_Jion_in_vb

Thanks,
Ramu.K

[Administrator]

Hi Ramu,

After reading your reply I realized that I had not correctly described what I want to do. I apologize for wasting your time with the previous request.

I have a table with two columns (there''s more, but this is all we''re concerned with right now): CompanyBusinessUnitID (CBUID) and ParentCompanyBusinessUnitID (PCBUID). If the PCBUID is null then the row is a top level row in the hierarchy. If it is not null than the current row has a parent. This is how the hierarchy is built.

This table represents a company employee hierarchy.

Is it possible to represent this with your grid? I think it is, but I don''t understand your grid well enough yet to get it to work.

Thanks for any help you can give me and again I apologize for misleading you previously.

Thanks,

Wes

>Hi Wes,

This is possible in our grid. Here is the sample that shows the Grid in a hierarchical manner.

Our grid will not automatically display in hierarchy structure by using the join query. To display grid in hierarchical form you have to define the “GridRelation” for parent and child table.

Steps to define hierarchical grid:

1) In the properties window choose “GridRelation” under Table Descriptor.

2) “GridRelationsDescriptor Collection Editor” opens up.

3) Add a new member and then choose the childTableName to the member.

4) Choose “RelatedMasterDetails” value for Relationkind property.

5) Choose the “RelationKeys” property. “RelationKeyDescriptor Collection Editor” opens up.

6) Add a new member and choose “ChildKeyFieldName” and “ParentKeyFieldName” to the member.

7) click “ok” button. Displays the grid in hierarchical form.

In this sample I have recursively joined with “OrdereID” column.I have used Orders table from NWIND Database. Please have a look at the sample and let me know if you have any queries.

recursive_Jion_in_vb

Thanks,
Ramu.K

[Ramu]

Hi Wes,

Could you please tell me UI design and the structure of the Data Table? So that it will be helpful for us to solve the issue as soon as possible. (if possible can you please provide a screen shot on the final UI that would be great). So we can try creating a sample for your requirements.

Thanks,
Ramu.K

[Administrator]

Hi Ramu,

I have attached a couple images of the table in question. One image is design time and the other is runtime.

The UI design needs to be a "tree" look and feel but with editing capabilities. That''s why we''re looking at a grid instead of a tree. I have reviewed many, many of the tree products out there and they either, don''t look good, don''t do what they say they will, lack something we need or are way too expensive.

Thanks,

Wes

CompanyBU.zip

[Ramu]

Hi Wes,

We have started creating the sample. We assume this behavior can be achieved by the following.

Trying to group by “ParentCompanyBusinessID”. If this field contains NULL value we will be removing the “GroupCptionCell” and the +/- cell and display only it’s rows or we will leave the records as grouped ( that is with the +/- cell). Please let me know if we are getting deviated from your requirements. If it is so could you give me the final UI of the grid to be displayed?

Sorry for the inconvenience caused.

Thanks,
Ramu.K

[Administrator]

Hi Ramu,

I''ve included a screen shot of what we are displaying now using the MS Treeview control. Using your grid control the layout of the data should still be the same.

As you can see, Bob''s Jobs is the top level node so it doesn''t have a ParentCompanyBusinessUnitID. Everything else should have a parent ID as they are all children of Bob''s Jobs or a child of a child.

The text in italics represent users which I add after creating the tree. You don''t need to worry about them as I can add them afterwards or just include them in with the query that populates the tree/grid.

Hopefully the screenshot gives you the information you were looking for.

Thanks,

Wes

CompanyBUGUI.zip

[Sivakumar]

Hi Wes,

Thanks for your screenshot. We are looking for the possiblities of implementing this. We will update you as soon as possible.

Sorry for the inconvenience caused and thanks for your patience.

Thanks,
A.Sivakumar

[Sivakumar]

Hi Wes,

I herewith attached the sample as per your requirements. I used Datatable as datasource for the grid. Let me know if you want a sample which shows the same for dataset.

click to download Self join in grid Sample

Configuring the Grid:
---------------------

Grid is connected to the datatable which has a ''CBUID'' column and ''PCBUID'' column and
once we set the datasource we set the hierarchy relations for the grid.

We set the relation between ''CBUID'' and ''PCBUID'' columns where if the ''PCBUID'' is null then the row is a top level row in the hierarchy. If it is not null than the current row has a parent. This is how the hierarchy is built.

It is important to set the primary key column for each table descriptor else, the grid cannot be in editable mode.


''QueryCellStyleInfo'' Event is handled so that when the NestedTables record count is null we will hide the +/- column and make it as a static cell.

''RecordFilterDescriptor'' is used to restrict the grids auto population code to detect when a self-relation occurs. In such case the grid will not add any further levels.


The look and feel of the grid is pretty basic in this sample, but can be customized in any way you want.

Please let me know if you need furthur assistance.


Thanks,
A.Sivakumar