How do I filter a child table through its parent?

Hi all!

I am trying to apply a filter to a table in a dataset using criteria from a
parent table, but I have no luck so far.

Having a dataset with tables for Customers and Orders, and a relation
between them, I wish to find the highest order for customers in a specific
region. Creating a view for Customers filtering them to only display
Customers in Region='A' is no problem, but as I try accessing Orders, it is
not filtered and so I have no way of finding the most expensive order for the
region 'A'. Just using the same criteria on the dataview for Orders does not
work, neither "Region='A'" nor "Customers.Region='A'" can be applied to the
Orders table.

I am actually not interested in displaying this information, I need to use
values from the filtered version of Orders for extensive calculations and
aggregations, so attaching my dataset/tables/views to any userinterface
components won't help.

Any ideas greatly appreciated!

AshishKelo
Thu Jan 27 13:57:09 CST 2005

Carolina,

Why you fighting with the records with the Dataset objetc, it might effect
the performance get all the Data gather through a function and manupilcations
done in that, using a repeater for each row of Customer and Orders. This will
help you in migrating the data hencforth.

Thanks,
Ashish Kelo

"Carolina" wrote:

> Hi all!
>
> I am trying to apply a filter to a table in a dataset using criteria from a
> parent table, but I have no luck so far.
>
> Having a dataset with tables for Customers and Orders, and a relation
> between them, I wish to find the highest order for customers in a specific
> region. Creating a view for Customers filtering them to only display
> Customers in Region='A' is no problem, but as I try accessing Orders, it is
> not filtered and so I have no way of finding the most expensive order for the
> region 'A'. Just using the same criteria on the dataview for Orders does not
> work, neither "Region='A'" nor "Customers.Region='A'" can be applied to the
> Orders table.
>
> I am actually not interested in displaying this information, I need to use
> values from the filtered version of Orders for extensive calculations and
> aggregations, so attaching my dataset/tables/views to any userinterface
> components won't help.
>
> Any ideas greatly appreciated!
>

Sahil
Thu Jan 27 19:20:11 CST 2005

Carolina,

What are your table structures?

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik


"Carolina" <Carolina@discussions.microsoft.com> wrote in message
news:09ADD969-A683-4A85-A193-7574E12B06B1@microsoft.com...
> Hi all!
>
> I am trying to apply a filter to a table in a dataset using criteria from
> a
> parent table, but I have no luck so far.
>
> Having a dataset with tables for Customers and Orders, and a relation
> between them, I wish to find the highest order for customers in a specific
> region. Creating a view for Customers filtering them to only display
> Customers in Region='A' is no problem, but as I try accessing Orders, it
> is
> not filtered and so I have no way of finding the most expensive order for
> the
> region 'A'. Just using the same criteria on the dataview for Orders does
> not
> work, neither "Region='A'" nor "Customers.Region='A'" can be applied to
> the
> Orders table.
>
> I am actually not interested in displaying this information, I need to use
> values from the filtered version of Orders for extensive calculations and
> aggregations, so attaching my dataset/tables/views to any userinterface
> components won't help.
>
> Any ideas greatly appreciated!
>

Carolina
Fri Jan 28 01:43:03 CST 2005

Hi Ashish!

Thank you for your answer.

I should have mentioned that I am designing a windows based application that
will be running on clients with local datasources (MSDE). We work with a
group of local customers and the user will be able to retreive a number of
customers with orders from the local database, and we need to keep this in a
relational structure as we manipulate the data. Furthermore, the user must be
able to select (by manually ctrl+click marking) an arbitrary subset of
customers, and from this subset generate a number of different reports.
Reports include grouped tables, historical analysies and so on. I see no
other way than doing calculations for these reports "on the fly".

I do have a dedicated component doing the grouping and calculations, and I
aim to minimize iterations and repeteteive procedure calls as much as
possible. I am not sure what you mean with using a repeater - could you
explain in more detail how I could apply it to my scenario?


"Ashish Kelo" wrote:

> Carolina,
>
> Why you fighting with the records with the Dataset objetc, it might effect
> the performance get all the Data gather through a function and manupilcations
> done in that, using a repeater for each row of Customer and Orders. This will
> help you in migrating the data hencforth.
>
> Thanks,
> Ashish Kelo
>
> "Carolina" wrote:
>
> > Hi all!
> >
> > I am trying to apply a filter to a table in a dataset using criteria from a
> > parent table, but I have no luck so far.
> >
> > Having a dataset with tables for Customers and Orders, and a relation
> > between them, I wish to find the highest order for customers in a specific
> > region. Creating a view for Customers filtering them to only display
> > Customers in Region='A' is no problem, but as I try accessing Orders, it is
> > not filtered and so I have no way of finding the most expensive order for the
> > region 'A'. Just using the same criteria on the dataview for Orders does not
> > work, neither "Region='A'" nor "Customers.Region='A'" can be applied to the
> > Orders table.
> >
> > I am actually not interested in displaying this information, I need to use
> > values from the filtered version of Orders for extensive calculations and
> > aggregations, so attaching my dataset/tables/views to any userinterface
> > components won't help.
> >
> > Any ideas greatly appreciated!
> >

Carolina
Fri Jan 28 02:57:01 CST 2005

Customers are typed with one reference table, one-to-many relation exists to
Orders. Orders are typed with two different reference tables (source and
category). All reference tables are loaded in the dataset and relations are
established. I am using a typed dataset.

"Sahil Malik" wrote:

> Carolina,
>
> What are your table structures?
>
> - Sahil Malik
> http://dotnetjunkies.com/weblog/sahilmalik
>
>
> "Carolina" <Carolina@discussions.microsoft.com> wrote in message
> news:09ADD969-A683-4A85-A193-7574E12B06B1@microsoft.com...
> > Hi all!
> >
> > I am trying to apply a filter to a table in a dataset using criteria from
> > a
> > parent table, but I have no luck so far.
> >
> > Having a dataset with tables for Customers and Orders, and a relation
> > between them, I wish to find the highest order for customers in a specific
> > region. Creating a view for Customers filtering them to only display
> > Customers in Region='A' is no problem, but as I try accessing Orders, it
> > is
> > not filtered and so I have no way of finding the most expensive order for
> > the
> > region 'A'. Just using the same criteria on the dataview for Orders does
> > not
> > work, neither "Region='A'" nor "Customers.Region='A'" can be applied to
> > the
> > Orders table.
> >
> > I am actually not interested in displaying this information, I need to use
> > values from the filtered version of Orders for extensive calculations and
> > aggregations, so attaching my dataset/tables/views to any userinterface
> > components won't help.
> >
> > Any ideas greatly appreciated!
> >
>
>
>

Carolina
Fri Jan 28 02:59:02 CST 2005

Customers has a "typetable" (customer group), and a one-to-many relation to
Orders. Orders has two typetables (source and category). All tables are
loaded in the dataset and relations established.

"Sahil Malik" wrote:

> Carolina,
>
> What are your table structures?
>
> - Sahil Malik
> http://dotnetjunkies.com/weblog/sahilmalik
>
>
> "Carolina" <Carolina@discussions.microsoft.com> wrote in message
> news:09ADD969-A683-4A85-A193-7574E12B06B1@microsoft.com...
> > Hi all!
> >
> > I am trying to apply a filter to a table in a dataset using criteria from
> > a
> > parent table, but I have no luck so far.
> >
> > Having a dataset with tables for Customers and Orders, and a relation
> > between them, I wish to find the highest order for customers in a specific
> > region. Creating a view for Customers filtering them to only display
> > Customers in Region='A' is no problem, but as I try accessing Orders, it
> > is
> > not filtered and so I have no way of finding the most expensive order for
> > the
> > region 'A'. Just using the same criteria on the dataview for Orders does
> > not
> > work, neither "Region='A'" nor "Customers.Region='A'" can be applied to
> > the
> > Orders table.
> >
> > I am actually not interested in displaying this information, I need to use
> > values from the filtered version of Orders for extensive calculations and
> > aggregations, so attaching my dataset/tables/views to any userinterface
> > components won't help.
> >
> > Any ideas greatly appreciated!
> >
>
>
>

Carolina
Fri Jan 28 04:37:09 CST 2005

I managed to solve this with help from a colleague at work!

The solution that worked for me is brilliantly simple: filtering on parent
criteria can be done using "parent.field" on the child's
defaultview.rowfilter property.

For example: myds.Orders.DefaultView.RowFilter = "parent.City='Stockholm'"

Thank you Ashish and Sahil for trying to help me!

"Carolina" wrote:

> Hi all!
>
> I am trying to apply a filter to a table in a dataset using criteria from a
> parent table, but I have no luck so far.
>
> Having a dataset with tables for Customers and Orders, and a relation
> between them, I wish to find the highest order for customers in a specific
> region. Creating a view for Customers filtering them to only display
> Customers in Region='A' is no problem, but as I try accessing Orders, it is
> not filtered and so I have no way of finding the most expensive order for the
> region 'A'. Just using the same criteria on the dataview for Orders does not
> work, neither "Region='A'" nor "Customers.Region='A'" can be applied to the
> Orders table.
>
> I am actually not interested in displaying this information, I need to use
> values from the filtered version of Orders for extensive calculations and
> aggregations, so attaching my dataset/tables/views to any userinterface
> components won't help.
>
> Any ideas greatly appreciated!
>

Sahil
Fri Jan 28 09:32:08 CST 2005

Slightly late to the party, but I am glad you got it to work.

I've been a bit inactive here lately, but I will come back soon as my
daytime job lets me to .. LOL :)

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik



"Carolina" <Carolina@discussions.microsoft.com> wrote in message
news:1F8F5D3B-79F7-4605-BA3F-9A924F6160F7@microsoft.com...
> I managed to solve this with help from a colleague at work!
>
> The solution that worked for me is brilliantly simple: filtering on parent
> criteria can be done using "parent.field" on the child's
> defaultview.rowfilter property.
>
> For example: myds.Orders.DefaultView.RowFilter = "parent.City='Stockholm'"
>
> Thank you Ashish and Sahil for trying to help me!
>
> "Carolina" wrote:
>
> > Hi all!
> >
> > I am trying to apply a filter to a table in a dataset using criteria
from a
> > parent table, but I have no luck so far.
> >
> > Having a dataset with tables for Customers and Orders, and a relation
> > between them, I wish to find the highest order for customers in a
specific
> > region. Creating a view for Customers filtering them to only display
> > Customers in Region='A' is no problem, but as I try accessing Orders, it
is
> > not filtered and so I have no way of finding the most expensive order
for the
> > region 'A'. Just using the same criteria on the dataview for Orders does
not
> > work, neither "Region='A'" nor "Customers.Region='A'" can be applied to
the
> > Orders table.
> >
> > I am actually not interested in displaying this information, I need to
use
> > values from the filtered version of Orders for extensive calculations
and
> > aggregations, so attaching my dataset/tables/views to any userinterface
> > components won't help.
> >
> > Any ideas greatly appreciated!
> >