Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

DataSet and Compute(...)

I have two tables in my DataSet, recipientTable and shippingTable, a relationship has been established between the two (shippingRelation). The recipientTable has a shipping ID, and the shipping table has the associated fee.

How can I use the DataSet .Compute function to get a Sum of the fee's for each recipient? I'm not sure how to access that associated column.

Colby Makowsky
Friday, August 01, 2003

Why not do it in SQL instead, and use the database for its really good at.

Something like:
SELECT r.ID, r.Name, SUM(s.fee)
FROM recipientTable r, shippingTable s
WHERE r.shippingTable_ID = s.ID
  AND ....
GROUP BY r.ID, r.Name

should do the trick.

It could be that the SQL is not 100% correct, but I am sure that you get the general idear.

Jesper Færgemann
Saturday, August 02, 2003

You'll be wanting to add a calculated column to the Columns colection of the "recipientTable" using an expression. See the Expression topic in the docs for information on the syntax:
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp

Duncan Smart
Monday, August 04, 2003

Duncan,

The problem comes in being able to call the compute(...) function for a child table, which would still exist even if the child table had a field with a sum... as it'd need to still attain the Total of the entire sum column.

My solution so far has been to simply iterate through each row in the ChildRows array and sum them up... I was hoping not to have to do such an iteration.

Colby Makowsky
Monday, August 04, 2003

Forgive me if I'm misunderstanding what you want to acheive, but have you read the link I posted?

"A column in a child table may be referenced in an expression by prepending the column name with Child. However, because child relationships may return multiple rows, you must include the reference to the child column in an aggregate function. For example, Sum(Child.Price) would return the sum of the column named Price in the child table..."

"The following aggregate types are supported:
Sum (Sum)
Avg (Average)
Min (Minimum)
Max (Maximum)
Count (Count)
StDev (Statistical standard deviation)
Var (Statistical variance)."

Duncan Smart
Tuesday, August 05, 2003

You were right on the money! Thanks Duncan.

Colby Makowsky
Thursday, August 07, 2003

*  Recent Topics

*  Fog Creek Home