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")

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may