Hello, I'm suffering with a new dillema, hopefully someone can help me out. I have the following tables:

COUNTRY PROVINCE PLACE

The country table has a CountryId field as PK. The province table has a ProvinceId as PK, and a CountryId as FK. The place table has a PlaceId as PK, and a ProvinceId as FK.

What I'm lokking for to create is a table that contains the following:

CountryId, ProvinceCount, PlaceCount

Counting the provinces in a country, for each country I solved using one of the samples of a scalar query expression, and the AggregateFunction.Count, like this:

ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(Geo_CountryFields.CountryId, 0);
fields.DefineField(new EntityField("NumberOfProvinces",
    new scalarQueryExpression(
 Geo_ProvinceFields.CountryId.SetAggregateFunction(AggregateFunction.Count),
(Geo_CountryFields.CountryId == Geo_ProvinceFields.CountryId))), 1);

DataTable results = new DataTable();
TypedListDAO dao = new TypedListDAO();
 dao.GetMultiAsDataTable(fields, results, 0, null, null, null, true, null, null, 0, 0); 

This works fine for the province, but now I also want to include the place count. And now I'm stuck.

Any suggestions?

Regards, Paul

ps. Using SelfService and v2.6

Try adding the following:

var relations = new RelationCollection();
relations.Add(Geo_PlaceEntity.Relations.ProvinceRelation...);

fields.DefineField(new EntityField("NumberOfPlaces",
    new scalarQueryExpression(
Geo_Place.PlaceId.SetAggregateFunction(AggregateFunction.Count),
(Geo_CountryFields.CountryId == Geo_ProvinceFields.CountryId), relations)), 2);

hummm... sounds good. I just solved it slightly different, a bit more verbose... this propably just fixes it as well.. Thanx!