locked
Sorting Parent Table based on Child Column Values RRS feed

  • Question

  • I'm trying to create a Windows form that allows users to interact with two related tables.

    I have two tables, a 'projects' table and a 'contacts' table.  Each project is associated with a contact through a contact_id field.  I've created a dataset with tableadapters for both tables, and created a relation between the two (with 'projects' as the parent and 'contacts' as the child table).

    On my form, I've created a BindingNavigator linked to a BindingSource for the 'projects' table, and then created another bindingsource which uses the 'projects' BindingSource as its DataSource and the relation as its DataMember.  Binding controls through these two bindingsources lets me display and navigate through data perfectly.

    Here's my trouble: I want to be able to sort based on the name of a contact.  That data isn't in the parent table anywhere, and I'm not sure how to reference the child table/relation in the .Sort method of the 'projects' BindingSource.  I've tried using both "[contacts].[contact_name]" and "[projects_contacts].[contact_name]"  (trying to get to the field through the name of the child table and the name of the relation, respectively), but both give me an error about not being in the BindingSource's IBindingList.

    Any suggestions?

    ~ald
    Tuesday, July 1, 2008 7:30 PM

Answers

  • DaveKlem:

    I appreciate the suggestion, but what I was attempting to do was slightly more complicated than what Beth M demonstrates in that video or the others I've seen in that series.

    What I ended up doing was adding an expression column named 'contact_name' to the 'projects' table in my dataset, using this expression: Max(Child(projects_contacts).contact_name)

    This essentially adds a computed column to that table in the dataset.  (I had to use 'Max' because expression columns anticipate one-to-many relationship, so they demand a function such as max/min/avg/sum that will return only one value.  In my case, there will only ever be one row out of the contacts table that matches, so 'Max' will always return that value.)

    Having set this up, I can simply sort or filter on 'contact_name' in the projects table (through the 'projects' binding source), without having to reference the parent/child relationship.  This allows me to accomplish my original goal (ie; 'sort all projects based on the name of the contact').

    ~ald







    • Marked as answer by ald7685 Thursday, July 3, 2008 8:12 PM
    • Edited by ald7685 Thursday, July 3, 2008 8:13 PM typo
    Thursday, July 3, 2008 8:12 PM

All replies

  • Beth M posted a nice video regarding this exact feature.
    I think it is #3 in forms over data series.

    http://msdn.microsoft.com/en-us/vbasic/bb466226.aspx
    Wednesday, July 2, 2008 7:38 AM
  • DaveKlem:

    I appreciate the suggestion, but what I was attempting to do was slightly more complicated than what Beth M demonstrates in that video or the others I've seen in that series.

    What I ended up doing was adding an expression column named 'contact_name' to the 'projects' table in my dataset, using this expression: Max(Child(projects_contacts).contact_name)

    This essentially adds a computed column to that table in the dataset.  (I had to use 'Max' because expression columns anticipate one-to-many relationship, so they demand a function such as max/min/avg/sum that will return only one value.  In my case, there will only ever be one row out of the contacts table that matches, so 'Max' will always return that value.)

    Having set this up, I can simply sort or filter on 'contact_name' in the projects table (through the 'projects' binding source), without having to reference the parent/child relationship.  This allows me to accomplish my original goal (ie; 'sort all projects based on the name of the contact').

    ~ald







    • Marked as answer by ald7685 Thursday, July 3, 2008 8:12 PM
    • Edited by ald7685 Thursday, July 3, 2008 8:13 PM typo
    Thursday, July 3, 2008 8:12 PM