This topic adds two active columns to the Products table from the Nwind.mdb sample database.
The Products table has fields for Category ID, Product Name, Unit Price, Units in Stock and various other columns not shown in the illustration above. Each product in the table has a Product ID. We will add two active columns that provide the category name for each product as well as the average price at which the product was sold.
The category name will be found by looking up the Category ID for the product in the Categories table.
The Categories table provides a Category Name for each Category ID.
The average sales price will be found by looking up all instances of the Product ID for the product in the Order Details table.
This table lists all products in all orders. By finding every occurrence of a particular product we can see what the unit price was that was actually applied for that product in the order.
Step 1: Add Active Column
Open the Products table and right click onto any of the column heads and choose Add - Active Column.
In the Add Active Column dialog. Provide the name of the function for the Category Name column and other parameters as shown above. The function name used is the name of the function that we will write into the script in the next step. Press OK and the Products Script for the Products table will be created and opened for editing.
Step 2: Enter the script
The script component is set up to use VBScript by default. To save time, we will enter the functions for both active columns into the script component at this time:
' -- determines average price for product using [Order Details] table
Function AveragePrice
' -- obtain product ID
product = Record.Data("Product ID")
' -- obtain details of orders for product
Set details = ComponentSet(ComponentSet.ItemByName("Order Details"))
Set detailsForProduct = details.RecordSet.EqualTo("Product ID", product)
AveragePrice = detailsForProduct.Average("Unit Price")
End Function
' -- looks up product category name within [Categories] table
Function CategoryName
' -- obtain category ID
category = Record.Data("Category ID")
' -- obtain category name from Categories table
Set categories = ComponentSet(ComponentSet.ItemByName("Categories"))
Set catRecords = categories.RecordSet
catRecordIndex = catRecords.ItemByValue("Category ID", category)
If catRecordIndex < 0 Then
CategoryName = ""
Else
CategoryName = catRecords(catRecordIndex).Data("Category Name")
End If
End Function
Step 3: Add second Active Column
Add the second active column by right clicking onto any column head in the table and choosing Add - Active Column.
We add the active column called Average Price. The function called is the AveragePrice function written into the script in the previous step.
After creating the column we right click on the Average Price column and choose Format and set the formatting to use $ currency formatting.
The result of the above is that two new columns appear in our table which are computed using the functions in the script.
See Also
See the Scripts topic for information on scripting. See the Active Columns topic for more on active columns.