I've just started trying out filemaker 7, (I'm converting from Access). I've managed to use the Product Catalog Template and modify it to my needs so it looks and acts like my access database except for one thing.

I'm tring to implicated a fairly difficult sorting solution. Heres what it is:

Each Record has one assigned keyword and a price value.

I want each record which has the same keyword to find the total average of price. This average is then sorted accending with the other keyword price averages.

This is probably have to be done in script, if anyone could help, it would be great! I'll give a gmail account too if you want.

Database: http://www.philzones.com/WineryAttemp2.fp7

For average price calculating create self relationship on keyword - let it be A.

Then create calculation field. Average_Price=Sum(A::Price)/Count(A::Price)

Thanks for the answer, but I cant properly integrate your answer. Could you do it for me or give me clearer steps please?

Here is a template that demonstrates (hopefully) how easy reporting is when adhering to a classic relational structure.

wines.fp7.zip

I looked at your attachment and understood most of it but I still couldn't integrate it into my template. ?

Thanks for the help so far.

Philip, I believe you are in need of a subsummary report with a reorder on the summarized field. In this case the average of the Price. I've attached your file with:

- a new summary field in the Product Catalog called PriceSummary that averages the Price field

- a new layout called Keyword Summary that contains a subsummary part on the Keywords field and displays the new PriceSummary field

- when the Product Catalog table is sorted by Keywords, the subsummary part will show the summarized data in Preview mode (look at the part definition for the subsummary part on this layout to see how it works)

- a new script called Keyword Summary that takes you to the new layout, sorts the records by the Keywords field, then reorders the result set by PriceSummary, and finally displays the new layout in preview mode.

- the reorder is specified in the Sort Records dialog (I think I set it as descending on PriceSummary, I see now you mentioned ascending)

There is a section in the help on this, look for: 'Sorting records by subsummary values'. I hope this gets you a little further along.

John.

WineryAttemp2.zip

When i posted my post, i tried it then posted. Now i dont in work. In Mondey i post your how to do it step by step.