Hierarchy Child CurrentRow and Values

Posted by: briansmith-fp on 8 September 2017, 2:02 am EST

  • Posted 8 September 2017, 2:02 am EST

    This might be an easy question to answer, but I am having no luck since working with this grid a week ago.



    I want to create a view where the customer information is printed and the child view would be all jobs under that customer. This was easy and I followed the demo.



    I want to be able to click on customer and get the value of the row:



    Private Sub grdJobsites_CellDoubleClick(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.CellClickEventArgs) Handles grdJobsites.CellDoubleClick

    Dim row As Integer

    'MessageBox.Show(grdJobsites.Sheets(0).GetValue(row, 0), "Select Item", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

    End Sub



    And this works to get the current row for the parent, but when I expand and click on the child, I get the same id for the parent. I want to be able to access the id for the child or any other values I have bound to the control.



    Please help me. :)





    Also, in this grid under the customer I have email addresses in the database. How do I dynamically bind with the hyperlink celltype?





    Thanks in advance. Brian
  • Replied 8 September 2017, 2:02 am EST

    Brian,



    To get values from the sheet that was clicked on you can use the View parameter passed to the event.



    MsgBox(e.View.Sheets(0).GetValue(e.Row, 0))





    To create a HyperLinkCellType with the URL being a value from a database, you can set your celltype to be a customcelltype where you override the PaintCell method and pass the value being passed into the override into the URL property of the class.





    FpSpread1.Sheets(0).DataAutoCellTypes = False

    FpSpread1.Sheets(0).DataSource = dt



    FpSpread1.Sheets(0).Columns(0).CellType = New mylink





    Public Class mylink

    Inherits FarPoint.Win.Spread.CellType.HyperLinkCellType



    Public Overrides Function GetEditorValue() As Object

    Return MyBase.Link

    End Function



    Public Overrides Sub PaintCell(ByVal g As System.Drawing.Graphics, ByVal r As System.Drawing.Rectangle, ByVal appearance As FarPoint.Win.Spread.Appearance, ByVal value As Object, ByVal isSelected As Boolean, ByVal isLocked As Boolean, ByVal zoomFactor As Single)

    MyBase.Link = value

    MyBase.PaintCell(g, r, appearance, value, isSelected, isLocked, zoomFactor)

    End Sub

    End Class

  • Replied 8 September 2017, 2:02 am EST

    Scotts, Thanks for the quick reply. I have two more questions for you.



    1) With the hierarchy, I would like to only expand the child views that have data related to the parent. So the "+" would disappear.



    2) I could not quite follow your example with the hyperlink. I am sure you sent it correct, but I am having trouble following it, I appologize. What I want is my database full of emails to be able to have a mailto: + emailaddress for the data and text = emailaddress. So when the user clicks on the email address it launchs the default email program. I am sure your previous post was correct, I guess I am just not passing something correctly. Could you please go into depth a little further.



    Thanks again.

    Brian
  • Replied 8 September 2017, 2:02 am EST

    Brian,



    1) You can call the SetRowExpandable method to remove the plus sign on certain rows. In this code I set a Relation on the dataset, then after binding the Spread, I loop through the rows of the datatable to find any without children. On those rows, I remove the plus sign from the corresponding row in the Spread.



    ds.Relations.Add("Root", ds.Tables("Categories").Columns("CategoryID"), _

    ds.Tables("Products").Columns("CategoryID"))



    FpSpread1.DataSource = ds

    For i As Integer = 0 To FpSpread1.Sheets(0).RowCount - 1

    If ds.Tables(0).Rows(i).GetChildRows("Root").GetLength(0) = 0 Then

    FpSpread1.Sheets(0).SetRowExpandable(i, False)

    End If

    Next





    2) Change your HyperLinkCelltype class to use the following code. I did not realize what the link needed to be in order to actually email someone.



    Public Class mylink

    Inherits FarPoint.Win.Spread.CellType.HyperLinkCellType



    Public Overrides Function GetEditorValue() As Object

    Return MyBase.Link

    End Function



    Public Overrides Sub PaintCell(ByVal g As System.Drawing.Graphics, ByVal r As System.Drawing.Rectangle, ByVal appearance As FarPoint.Win.Spread.Appearance, ByVal value As Object, ByVal isSelected As Boolean, ByVal isLocked As Boolean, ByVal zoomFactor As Single)

    MyBase.Link = "mailto:" & value

    MyBase.Text = value

    MyBase.PaintCell(g, r, appearance, value, isSelected, isLocked, zoomFactor)

    End Sub

    End Class

  • Replied 8 September 2017, 2:02 am EST

    Scotts, Thanks again, you have made working with the new sheet rather easy. All those worked great, but then I try to do more and run into some problems:



    1) I want to be able to jump to a record in the grid and highlight it. Say I type on my form somewhere the customerID that I want to pull up, I want it to jump to the record in the grid and highlight it.



    I also need to be able to do this in the child view. Where I can highlight a customer and then the jobsite I referenced under the child view.



    Thanks again, Brian
  • Replied 8 September 2017, 2:02 am EST

    Brian,



    To reposition the viewport of the Spread and make a cell active, you can use the code like the following:



    FpSpread1.SetViewportTopRow(0, 100)

    FpSpread1.SetViewportLeftColumn(0, 10)

    FpSpread1.Sheets(0).SetActiveCell(100, 10)





    Getting the SpreadView for a child window to call this code on would be a little more work. Tell me more about the situation in your code that you would use this code to reposition the child sheet.
  • Replied 8 September 2017, 2:02 am EST

    Scotts,



    I need to clarify a little better. What I have on the form is the spreadsheet with the customers listed, and then below the sheet I have a combobox filled with all the customers names. When a user drops down the combobox and selects a customer and clicks go, I want to be able to lookup that customer in the dataset and navigate & highlight the row in the sheet.



    Different Sheet:



    I also want to be able to do this with the child view where the user can select a customer and job from two different comboboxes and then it navigates to the customer expands it and highlights the correct jobsite. These records will be referenced by their id from the database thats bound to the dataset.



    Thanks for the help. Hope that clears it up a little.



    Brian
  • Replied 8 September 2017, 2:02 am EST

    Brian,



    Here is code that will search the DataSet for the parent row and then find the equivalent row in the Spread and make that row selected and expanded, then take a value from another textbox to search for that string in the child sheet and select it.



    Dim ds As DataSet = CType(FpSpread1.Sheets(0).DataSource, DataSet)

    Dim key1 As Object

    Dim key2 As Object

    Dim drParent As DataRow

    Dim spreadRow As Integer

    Dim i As Integer

    Dim childview As FarPoint.Win.Spread.SheetView



    key1 = TextBox1.Text

    key2 = TextBox2.Text

    i = 0

    drParent = ds.Tables(0).Rows.Find(key1)

    For Each dr As DataRow In ds.Tables(0).Rows

    If Object.ReferenceEquals(dr, drParent) Then

    spreadRow = i

    End If

    i += 1

    Next

    FpSpread1.SetViewportTopRow(0, spreadRow)

    FpSpread1.Sheets(0).SetActiveCell(spreadRow, 0)

    FpSpread1.Sheets(0).Models.Selection.AddSelection(spreadRow, 0, 1, FpSpread1.Sheets(0).ColumnCount)

    FpSpread1.Sheets(0).ExpandRow(spreadRow, True)



    childview = FpSpread1.Sheets(0).GetChildView(spreadRow, 0)

    For i = 0 To childview.RowCount - 1

    If childview.GetValue(i, 0) = key2 Then

    childview.SetActiveCell(i, 0)

    childview.Models.Selection.AddSelection(i, 0, 1, childview.ColumnCount)

    End If

    Next



  • Replied 8 September 2017, 2:02 am EST

    Scott,



    The code looks good, but I get an error that states: "Table doesn't have a primary key."



    These tables do have primary keys, so I am not sure what is wrong.



    Also, what would you do if you just want the basic single key search, i.e. after you insert a record, you want the grid to refresh and then highlight that new record.



    Thanks for the help.



    Brian
  • Replied 8 September 2017, 2:02 am EST

    Brian,



    1) In order to use the Find method on a DataRow, there must be a PrimaryKey set on the DataTable. Make sure you set the PrimaryKey property on the DataTables in question.



    2) I am not sure what you are looking for here. It sounds like two questions. a) how to implement single key search and b) how to insert a row and then highlight it. Can you tell me what you are looking for here?
  • Replied 8 September 2017, 2:02 am EST

    Scott,



    What I am trying to do is two different things.



    1.) I just have a regular grid, no hierarchy, and I want to search in a text box the id of a customer and I want the grid to scroll down to it an highlight that customer.



    2.) Very simular here, but this is the hierarchy view with the customers listed and then all there jobsites under them. Here I want to type into a text box the customer id and the jobsite id and I want it to scroll down to the customer, expand the jobsites, and then highlight that jobsite by reference to the jobsite id.



    Also, when I do this search on jobsite, I want to collapse any expanded views on different customers to only have this one open.



    I hope this is a better explanation. I was able to get the primary key working, but the code you gave me before doesn't work.



    Thanks again, Brian.
  • Replied 8 September 2017, 2:02 am EST

    Brian,



    1) Here is code to search a non-hierarchial Spread control for text that is specified in the textbox control. Then it moves that row to the top of the viewport and selects that row.



    Dim ri As Integer

    Dim ci As Integer

    FpSpread1.Search(0, TextBox1.Text, False, True, True, False, 0, 0, ri, ci)

    FpSpread1.SetViewportTopRow(0, ri)

    FpSpread1.Sheets(0).Models.Selection.AddSelection(ri, -1, 1, -1)





    2) What was wrong with the code from before for doing the double search? Could you send us a small zipped project to fpsupport@fpoint.com reproducing this problem for us to debug?
  • Replied 8 September 2017, 2:02 am EST

    Scott,



    That last code worked great. I also modified it for the jobsite grid(seperate form) to expand the row. I guess though I am having problems with the hierarchy here the most. Let me explain:



    I have a customers table and jobsites table, they are in a dataset with primary keys and relationship on customerid. I have the customers as the parent and the jobsites as the child. I want to be able to click on a customer (not just the '+' sign) and have it expand all the jobsites under it. (We took care of not showing the '+' if there are no children earlier)



    Now if I double click on a jobsite(anywhere on that child row) I want to capture the jobsiteid of that row and then I send it to a select statement and populate some text boxes on the form to update the record. Once I click save, I want to be able to highlight that customer and expand it to show that record I just updated.



    Also I want to be able to insert a record with that form and have the customer highlighted and expanded to that jobsite I just inserted. I hope this helps out. I was able to finish my customer form doing all of this by capturing the row I clicked, but I am having problems with the heirarchy part of capuring all of these events.



    Thanks in advance for the help, you have really brought me up to speed fast with your product.



    Brian
  • Replied 8 September 2017, 2:02 am EST

    Brian,



    If you already have the row expanded, by you double clicking on it, why are you needing to do this again after the save? Could you send us a small zipped project reproducing this problem, so we can see how you are setting this up?
  • Replied 8 September 2017, 2:02 am EST

    Scott,



    I think I figured it out witrh your help, but I need two things.



    1) I want to double-click anywhere on a parent row, not just the "+" sign and expand the child rows under it. Also when I do this, I want to close any other parent row that is expanded, so only one can be expanded at a time.



    2) I want to differentiate between double-clicking a parent or a child. I want to know that I double-clicked a child row and then I would do some processing.



    Thanks for the help.



    Brian
  • Replied 8 September 2017, 2:02 am EST

    Brian,



    You can map the CellDoubleClick and check the view that was double clicked to see if it is the parent or child sheet.



    Private Sub FpSpread1_CellDoubleClick(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.CellClickEventArgs) Handles FpSpread1.CellDoubleClick

    If e.View.Parent Is Nothing Then 'Parent sheet

    e.View.Sheets(0).ExpandRow(e.Row, True)

    End If

    End Sub

  • Replied 8 September 2017, 2:02 am EST

    Scott,



    I recently came up with a unique problem with the grid. When I double-click on a customer, I popup a MSGBOX asking them if they want to open that customer.



    It doesn't matter if they click yes or no, once the MSGBOX disapears, when you hover over the grid with the mouse, it highlights above that record every row or if you move below that record it highlights everyrow. It doesn't affect the data or any manipulations, it seems that when you popup a msgbox that once you return it is stuck on a mousedown or something?



    Any ideas or similar problems before. Thanks, Brian
  • Replied 8 September 2017, 2:02 am EST

    Brian,



    I am not able to reproduce this problem. I can see that this problem could happen since the Msgbox would stop processing on the main application. When this happens, messages can get lost. You could try calling an Application.DoEvents() to see if this helps, but without being able to reproduce the problem, I have no ideas for how to fix this.
  • Replied 19 March 2020, 12:45 am EST

    How to catch button event from hierarchical view in this example ? I need current row and column number ?
  • Marked as Answer

    Replied 19 March 2020, 8:57 pm EST

    Hi,

    To catch button click event from hierarchical view, handle FpSpread's ButtonClicked event and inside access the child sheet values through e.View.Sheets[0].GetValue(row index, col index).
    Like the following code gets the value for the first column in child sheet, as follows:
    System.Diagnostics.Debug.WriteLine(e.View.Sheets[0].GetValue(e.Row, 0));

    For complete code, refer attached image/application.

    With regards,
    Ruchir
    Hierarchical_WinSpread.zip
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels