Sort based on related summary field

[cuttysark]

Hello,

I am trying to sort records based on a summary field from a related table, but FM9 won't seem to do it.

I have a table JOBS, a table CONTRACTORS. Each job can have one contractor. In the JOBS table, I also have a summary field TOTAL_JOBS, which is a COUNT OF JOB_ID (unique id field).

I have set up another table occurence of both CONTRACTORS and JOBS, and want to view how many jobs each contractor has done, then sort them by that number. I'm in a list layout, and have all the contractors listed. Next to that, the related summary field JOBS::TOTAL_JOBS, which displays the correct number of total jobs done by each contractor.

However, I can't for the life of me get the list to sort by the summary field. I've tried sorting by contractor name and then "Reorder based on Summary field", but this doesn't seem to do anything. Can anyone show me where I'm going wrong?

Thank you for your help.

[Søren Dyhr]

Intitial thoughts was this: Is it scaling issues preventing you from putting the Total_Jobs as an aggregate function Count( in the contractor table instead, or...?

Using summary fields over a relation isn't particular documented (yet).

But then I tried to investigate the reasoning behind the Reorder by summary, and it works ... you need to sort the Contractor ID on the related summary field. I think this is where you turned wrong ....sorting directly on a summary field is not possible, it's grayed out if attempting.

--sd

Edited July 17, 2009 by Guest