Join Tables and Grids

[nells1]

Hello

I have a system as follows (please excuse the poor diagram):

[tt]
Sites 1-----------m Contacts
1 1
| |
| |
m m
Jobs 1------------m JobContacts
[/tt]

Where JobContacts is a Join Table.

Now my problem is that on my Job form I want to show a grid of the Contacts that are related to this Job and all their details (phone number etc).

I know that to show child records on a form you set the grid's datasource to the form's datasource and then set the data member of the grid to the relationship name from the dataset. This keeps the grid and the form in sync when you switch between records.

My question is how do I set this up with a join table?

Thank you in advance
Nells

 

[RiverGuy]

Set up two DataRelations. I believe it would work the same.

 

[nells1]

Hi RiverGuy

Thanks for your reply.

I have my two DataRelations set up as follows...

MyDataset.Relations.Add("Job_JobContact", _
     MyDataset.Tables("Jobs").Columns("JobID"), _
     MyDataset.Tables("JobContacts").Columns("JobID"))

MyDataset.Relations.Add("Contact_JobContact", _
     MyDataset.Tables("Contacts").Columns("ContactID"), _
     MyDataset.Tables("JobContacts").Columns("ContactID"))

Then on my "Job" form I have added a grid and set it up as follows.... (JobsDatasource is the datasource for the form)

grdContacts.DataSource = JobsDatasource
grdContacts.DataMember = "Job_JobContact"

Which brings back the join table fields as expected.

What I need though is to see the actual contact's details. So when I change the grid settings to this:

grdContacts.DataSource = JobsDatasource
grdContacts.DataMember = "Job_JobContact.Contact_JobContact"

.. I get the error "Cannot create a child list for field Contact_JobContact". This makes sense because Contact_JobContact is a parent relation as far as the JobContact table is concerned.

So then I went looking on the net again and read that if you reversed the second relationship (ie Contact_JobContact) so that JobContact is the parent table, it would work. So i reversed that second relationship and ended up with:

grdContacts.DataSource = JobsDatasource
grdContacts.DataMember = "Job_JobContact.JobContact_Contact"

This does show Contact column headers in the grid, the problem is that it only brings back one Contact record, so instead of seeing all my Contacts that are related to this Job, I only see the first matching record depending on the sort order.

Is this how you intended me to use the DataRelations? I don't like the idea of reversing the relationship, but it probably isn't too big a deal if the grid is for display only.

Do you know why it would only show one record?

Many Thanks
Nells

 

[RiverGuy]

I guess that does sort of make sense. It's only showing one record, because each JobContact only has one job and one contact. Therefore, each row in JobContact will only have one child row. That is the only thing I can think of.

If the Contact records are for display only, can't you bring them over in a view that will have it's Job ID listed, then you only have to have one DataRelation.

 

[nells1]

Yes you're right, that may be my only option. Is that how you get your many to many joins to work?

I would have thought there would be a better way to handle it since these types of joins are very common.

Creating a view will work for me and will do for my current project. I'll keep hunting though.

Thank you so much for your help!!