devxlogo

Calculated columns that refer to relationships

Calculated columns that refer to relationships

The DataSet is a container of multiple DataTables, and it allows to create parent-child relationships between two tables, as shown below:

' create a relationship between the Categories and the Products tables,'  against the CatID columnds.Relations.Add(New DataRelation("CatProducts", _    ds.Tables("Categories").Columns("CatID"), ds.Tables("Products").Columns _    ("CatID")))

This is nice, but even nicer is the possibility to create calculated columns in a table, that refer to the parent-child relationship. For example, here’s how you can create a new column ProductsCount in the DataSet’s Categories table, which will return the number of child products:

' add a new calculated column to the Categories table, with the number of child ' productsds.Tables("Categories").Columns.Add("ProductsCount", GetType(Integer), _    "Count(Child(CatProducts).ProductID)")

The expression refers to the CatProducts relationship created before, and uses the Count function to count the number of child records returned by the relationship for each single category. Many aggregate functions are supported, such as Min, Max, Sum, Avg.

From a child record, you can also refer to the parent item of course. The following code shows how to add a calculated column to the Products table, that will contain the name of the parent category:

' add a new calculated column to the Products table,'  with the name of the parent categoryds.Tables("Products").Columns.Add("CategoryName", GetType(String), _    "Parent(CatProducts).CategoryName")

See also  5 Tips for Working With an Onsite Interpreter
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist