473,320 Members | 1,926 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

RowFilters with related tables

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

Nov 17 '05 #1
18 2794
Pete,

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

http://msdn.microsoft.com/library/de...electtopic.asp

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

I hope this helps something

Cor
Nov 17 '05 #2
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:O1*************@TK2MSFTNGP10.phx.gbl...
Pete,

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

http://msdn.microsoft.com/library/de...electtopic.asp

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

I hope this helps something

Cor


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
Nov 17 '05 #3
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
"Pete Davis" <pdavis68@[nospam]hotmail.com> wrote in message
news:o7********************@giganews.com...
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

Nov 17 '05 #4

"Denis Dougall" <De***********@moh.gov.on.ca> wrote in message
news:Oh**************@TK2MSFTNGP14.phx.gbl...
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


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
Nov 17 '05 #5
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

Nov 17 '05 #6
> 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 ?


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
Nov 17 '05 #7
Can you clone a table and populate the clone with the "matching" entries?
similiar to http://www.codeproject.com/csharp/Pr...DataGrids4.asp
5.5 Cloning?
"Pete Davis" <pdavis68@[nospam]hotmail.com> wrote in message
news:Is********************@giganews.com...

"Denis Dougall" <De***********@moh.gov.on.ca> wrote in message
news:Oh**************@TK2MSFTNGP14.phx.gbl...
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


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

Nov 17 '05 #8
> Can you clone a table and populate the clone with the "matching" entries?
similiar to http://www.codeproject.com/csharp/Pr...DataGrids4.asp
5.5 Cloning?


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
Nov 17 '05 #9
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 Davis" <pdavis68@[nospam]hotmail.com> schreef in bericht
news:4N********************@giganews.com...
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:O1*************@TK2MSFTNGP10.phx.gbl...
Pete,

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

http://msdn.microsoft.com/library/de...electtopic.asp

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

I hope this helps something

Cor


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

Nov 17 '05 #10
> 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

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
Nov 17 '05 #11
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.


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
Nov 17 '05 #12
> 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.


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


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
Nov 17 '05 #13
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/ASPNETCont...ierarGrid.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
"Pete Davis" <pdavis68@[nospam]hotmail.com> wrote in message
news:P9********************@giganews.com...
Pete,
Please read the other posts in this topic to see why I can't. Of course Ican do this in a simple SQL join.


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


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

Nov 17 '05 #14
> 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/ASPNETCont...ierarGrid.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


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
Nov 17 '05 #15
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
"Pete Davis" <pdavis68@[nospam]hotmail.com> wrote in message
news:r-********************@giganews.com...
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/ASPNETCont...ierarGrid.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
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

Nov 17 '05 #16
> 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


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
Nov 17 '05 #17

"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

Nov 17 '05 #18



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

I find myself needing to do this same thing. What was the solution you came
up with?
Nov 21 '05 #19

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
1
by: Christa Waggett | last post by:
Hi, I'm not a programmer but would appreciate some help with the following. I've been looking at various sites but cannot find the information I require. I have a table of strata plans and if we...
2
by: Masa Ito | last post by:
The rowfilter in a dataview seems to support a very limited subset of regex expressions. I need to filter a column of strings by their length. ie: if 2 character code, or 3 character code. ...
4
by: HLCruz via AccessMonster.com | last post by:
I am working with a database that has client information separated in to 4 related tables - tFolder, tAddress, tEmail, tPhone number. In addition there are related tables tGifts and tCalls. The...
2
by: Mark Rae | last post by:
Hi, This isn't *specifically* an ASP.NET question, so I've also posted it in the ADO.NET group - however, it's not too far off-topic... Imagine a SQL Server 2005 database with a table with an...
4
by: db2admin | last post by:
Hello, I want to plan rearranging tables in our database according to business areas. say all tables in business area A will be in seperate tablespace or tablespaces. I am planning to monitor...
11
by: shriil | last post by:
Hi I have this database that calculates and stores the incentive amount earned by employees of a particular department. Each record is entered by entering the Date, Shift (morn, eve, or night)...
5
by: upwardgazing | last post by:
I'm using Access 2003 (Access 2000 file format) and I have two tables related one-to-many called tblTempEncounter and tblTempEncounterDetails. I need to move a record from the first table with it's...
3
by: BASSPU03 | last post by:
(I'm using Access 2003 on a Windows XP O/S.) I have four tables that are related in the following order: tblFiscalYear > tblBulkObligations > tblProjects > tblResources These are their...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.