RowFilters with related tables

  • Thread starter Thread starter Pete Davis
  • Start date Start date
P

Pete Davis

I have two tables, let's just say Master and Details. The Master is the
parent table, Details the child table.

What I want is to create a row filter on the children. But I also want to
create a row filter on the master table such that only master records that
have children that meet the criteria in the children table filter, will be
included.

That is, only master records with one or more qualifying children.

Can this be done with row filters? I mean, I could go through the child
table to get a list of all the unique foreign keys and do an IN operation on
them. I'd rather not go this way on several grounds including, we could be
talking about thousands of records and then you could end up with a really
huge filter string which would be undesirable. I guess what I'm looking for
is something more automatic that won't have to be modified if the child data
set is modified. I suspect there's not a way to do SQL style relational
filters, but thought it was worth asking just in case.

Pete
 
Cor Ligthert said:
Pete,

Reading your text I am in doubt if the select can help you.

http://msdn.microsoft.com/library/d.../frlrfsystemdatadatatableclassselecttopic.asp

Otherwise I would go for the getchildrows and see if that is zero.
http://msdn.microsoft.com/library/d...rfsystemdatadatarowclassgetchildrowstopic.asp

I hope this helps something

Cor
Click to expand...

Thanks, but that's not going to do it. I guess what I neglected to mention
is this has to be done as a RowFilter since it's a DataViewManager which is
bound to a grid control. I want the grid to only show the parent rows for
parents that have children and the children which meet the row filter
criteria.

I know, this is probably some where in the land of obscurity. I mean, just
figuring out how to bind a grid to a DataViewManager and apply filters took
a bit of digging. MS didn't really go out of their way to document this
stuff.

Pete
 
If your tables/database is properly normalized (4th normal form) then
iterate through the associate table.. MasterDetail which you would have
built to resolve the many to many relationships between the tables.

Cheers

Denis
 
Denis Dougall said:
If your tables/database is properly normalized (4th normal form) then
iterate through the associate table.. MasterDetail which you would have
built to resolve the many to many relationships between the tables.

Cheers

Denis
Click to expand...

It's not many to many. It's one to many and there is no associate table.

I think you're misunderstanding the question. The question is more in
regards to how to implement my requirements via a RowFilter.

Pete
 
Hi,

When filling the Master data, set your SQL to pull the data only if
they have the related child rows
i.e Select m.a,m.b,m.c from master m where m.a in (select distinct
child.a from child)

It might be in-efficient. But can definitely be looked at & query can
be optimized

Does it help ?

Kalpesh
 
Hi,
When filling the Master data, set your SQL to pull the data only if
they have the related child rows
i.e Select m.a,m.b,m.c from master m where m.a in (select distinct
child.a from child)

It might be in-efficient. But can definitely be looked at & query can
be optimized

Does it help ?
Click to expand...

Again, I need this to be done via RowFilters, not via the SQL. Basically,
here's how it works:

I have a DataViewManager bound to a grid control. The grid is hierarchical
and shows Master and Detail records. Above that, I have a Filter control
that lets the user select columns and values. When they set a value, I then
apply a RowFilter along the lines of:

dvm.DataViewSettings[detailsDataTable].RowFilter = "DepartmentID = 4"

This will filter all detail records showing only those for DepartmentID = 4.
Now, I only want the Master records that have children that have
DepartmentID=4.

I don't want to have to reget the data from the data source as this wouldn't
be feasible from a performance standpoint.

Pete
 
Can you clone a table and populate the clone with the "matching" entries?
similiar to http://www.codeproject.com/csharp/PracticalGuideDataGrids4.asp
5.5 Cloning?
Click to expand...

No, this isn't feasible. I don't want to rebind the grid to a new datatable.
I don't want to create a new dataset. I need this to be done via the
RowFilter in the DataViewManager, if possible. If it's not possible then I
have plenty of other ways I can do it that are really not desirable. My
question is, can it be done with a RowFilter and if so, how?

Pete
 
Pete,

In my opinion can you not create this using a filter in SQL and as well will
you not be able to do that in a dataset. Because there is nothing to filter,
it is matching.

The same as in SQL you can use the getchildrows to make a seperate table.

I hope this helps,

Cor
 
Pete,
In my opinion can you not create this using a filter in SQL and as well
will you not be able to do that in a dataset. Because there is nothing to
filter, it is matching.

The same as in SQL you can use the getchildrows to make a seperate table.

I hope this helps,

Cor
Click to expand...


Please read the other posts in this topic to see why I can't. Of course I
can do this in a simple SQL join. That isn't my question. My question is:
Can it be done with RowFilters? That's all I'm asking? Not, is there another
way I can do it. There are a number of ways I CAN do it, but none of them
meet my needs. Doing it with a RowFilter would meet my needs.

Again, at the risk of repeating myself, this is a DataViewManager that's
bound to a custom grid control (not the MS S.W.F.DataGrid). Requerying the
data every time the user changes the filter is NOT an option. The amount of
data involved would make the performance prohibitive.

Pete
 
Pete,
Please read the other posts in this topic to see why I can't. Of course I
can do this in a simple SQL join.
Click to expand...

I know as probably everybody one in this thread what you want.

A join is no filter it is a command to search for matched records and than
to return the ones that match.

Cor
 
Pete,
I know as probably everybody one in this thread what you want.

A join is no filter it is a command to search for matched records and than
to return the ones that match.

Cor
Click to expand...

True, I misspoke.

I could use a subquery to return the results in SQL. But again, my desire
isn't to requery the data from the database or create a new DataSet, etc.
It's to work within the existing bound DataViewManager without having to
rebind to the grid.

Pete
 
Seems someone has written a control to do as you wish, (I think). It
revolves around building a template for the "new" child display. It can be
found at http://www.denisbauer.com/ASPNETControls/HierarGrid.aspx I didn't
run it but the source looks close or at least might point you somewhere
that
you can look. It comes with an assembly.

Good Luck!

Denis
Click to expand...

Denis,

I am not looking for a control to display a hiearchical grid. I HAVE a
control that does this.

Please read my question again. I am specifically asking a question about
using RowFilters in DataViewManagers. The grid control came up in response
to someone mentioning an option that didn't meet my needs. BUT I HAVE A
CONTROL. What I want to know is can I accomplish the kind of row filtering I
want with DataViewManager and RowFilters? I don't want to know about
alternative methods. I have plenty of them. I don't want to know about other
controls. I simply want to know, will RowFilters do what I want them to do
and if so how?

My original question described the setup and then asked one simple question:
"Can this be done with row filters?" Nobody has answered this single
question yet, after a number of replies.

I'm sorry if I sound upset, I guess I'm just frustrated, but I've been going
back and forth for the past 5 hours with everyone in a number of different
tangents and I'm asking what I think is a very straightfoward and simple
question, but every response has avoided answering the original question and
gone off in a tangent, and I'm getting no closer to knowing the answer.

Pete
 
Hey Pete,

The closest I can come up with is the findrows method object .... if no
master key value is found it would return an empty datarowview array (of
Details) else you get an array for a single master key value. If iterating
through a view of Master is not an option, perhaps suggesting to MS that a
OLE linked Table Found indicator or sorts.

But I still can't definitively answer your question. I guess my view is that
a work around is a solution of sorts. Whether the solution is pretty or
"shady" we all use work arounds.

Denis
 
Hey Pete,
The closest I can come up with is the findrows method object .... if no
master key value is found it would return an empty datarowview array (of
Details) else you get an array for a single master key value. If iterating
through a view of Master is not an option, perhaps suggesting to MS that a
OLE linked Table Found indicator or sorts.

But I still can't definitively answer your question. I guess my view is
that
a work around is a solution of sorts. Whether the solution is pretty or
"shady" we all use work arounds.

Denis
Click to expand...

After further research, it appears there is no way to do this in version 1.1
of the framework. Someone submitted it as an issue to MS and they claim to
be adding expanded RowFilter functionality in version 2.0 of the framework.
I haven't tried it yet, so I don't know if this is truly the case. Nor will
I be able to since our product is fixed to 1.1 and that won't be changing.
Oh well.

My solution is not nearly as elegant as I would like, but at least I can
blame MS when the other developers come crying to me about it.

Pete
 
"Pete Davis"

There is a possibility. You can add a boolean column in your datatable and
(if you want database) which you fill with the information if it has
childs. If you do not add that to your database, than you have to fill that
once in your datatable and keep it updated.

In that way you can use the dataview

I hope this helps,

Cor
 
After further research, it appears there is no way to do this in version 1.1
of the framework. Someone submitted it as an issue to MS and they claim to
be adding expanded RowFilter functionality in version 2.0 of the framework.
I haven't tried it yet, so I don't know if this is truly the case. Nor will
I be able to since our product is fixed to 1.1 and that won't be changing.
Oh well.

My solution is not nearly as elegant as I would like, but at least I can
blame MS when the other developers come crying to me about it.

Pete
Click to expand...


I find myself needing to do this same thing. What was the solution you came
up with?
 
Back
Top