DataViewManager.Rowfilters and Child Filtering

Question

Hi can anyone here help with this? I have a dataset that is populated with data from two related tables. I create a DataViewManager to manage views of the data based on dynamically created queries which are passed to the DataViewManager.RowFilter. The DataViewManager is bound to a datagrid which displays the results of the queries. The datagrid also displays the child data relating to each row by clicking on the + sign at the start of each row. Currently the queries I am adding to the RowFilter are based on the parent table for example; dvManager.DataViewSettings[client].RowFilter = ((region = 'Blah') OR ((region = 'Bluh')) AND ((area = 'Here') OR (area = 'There')) AND (gender = 'Male'); - This approach works well however I now need to filter records based on values in the child table. If I wanted to do this by going back to the {MSAccess}database I would be creating an SQL statement such as the one below; SELECT DISTINCT client.* FROM clientcat AS clientcat_1 clientcat AS clientcat_2 client INNER JOIN clientcat ON client.clientid = clientcat.clientid WHERE ((client.clientid=[clientcat].[clientid]) AND (clientcat.category='Literature')) AND ((client.clientid=[clientcat_1].[clientid]) AND (clientcat_1.category='Art Administration')) AND ((client.clientid=[clientcat_2].[clientid]) AND (clientcat_2.category='Performance')); According to MSDN the DataViewManager is capable of this functionality although they do not offer any examples of exactly how to do it. My question is - Is it possible to query a DataViewManager in this way and can someone show me how? :)

Robert Josefs · Answer

According to the documentation... PARENT/CHILD RELATION REFERENCING A column in a child table may be referenced in an expresion by prepending the column name with Child. For example Child.Price would reference the column named Price in the child table. If a table has more than one child the syntax is: Child(RelationName). For example if a table has two child tables named Employee and Titles and the DataRelation objects are named Publishers2Employee: and Publishers2Titles the reference would be: Child(Publishers2Employee).fname Child(Publishers2Titles).title A parent table may be referenced in an expression by prepending the column name with Parent. For example the Parent.Price references the parent table's column named Price. AGGREGATES The following aggregate types are supported: Sum (Sum) Avg (Average) Min (Minimum) Max (Maximum) Count (Count) StDev (Statistical standard deviation) Var (Statistical variance). Aggregates are usually performed along relationships. Create an aggregate expression by using one of the functions listed above and a child table column as detailed in PARENT/CHILD RELATION REFERENCING above. For example: Avg(Child.Price) Avg(Child(Orders2Details).Price) An aggregate can also be performed on a single table. For example to create a summary of figures in a column named Price: Sum(Price) Note If you use a single table to create an aggregate there would be no group-by functionality. Instead all rows would display the same value in the column. If a table has no rows the aggregate functions will return a null reference (Nothing in Visual Basic). Data types can always be determined by examining the DataType property of a column. You can also convert data types using the Convert function shown below.