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.

DataViewManager.Rowfilters and Child Filtering

Answers (1)