HELP! Sorting by Summary Field

[Alexto94103]

Hello,

Could someone please tell me whether I can do the following with File Maker 6? I have spent countless hours trying to do this with no success.

I have a database with three fields:

PRODUCT ID (e.g. product X, product Y, etc.)

PRICE PER PRODUCT

TOTAL (which is Summary of the PRICE PER PRODUCT field, gives me the total $ sold by product)

The report I use the most includes two fields (PRODUCT ID and TOTAL) looks like this (the subsummary in the report is sorted by Product ID)

---

PRODUCT X $100

PRODUCT Y $300

PRODUCT Z $200

yes, since the subsummary is sorted by Product ID, the order is alphabetical.

---

Is there a way to sort by the value of the TOTAL field value, so the report would look like this:

==

PRODUCT Y $300

PRODUCT Z $200

PRODUCT X $100

==

Your help is tremendously appreciated.

Alex T.

[LiveOak]

The good news is that it can be done. The bad news is that it is a little involved. What you have to do is to sort, summarize and then export the data to a temporary file. Then import to another FM file, sort and print. Basically you must create a data set with one record for each summary value, then you can sort the records. In this data set the summary total is just another field, not a number created on the fly by FM.

If you were hoping for a solution that actually allowed you to sort by your existing summary field, sorry I know of no way to do this as any resort disturbs the sort order which allows the creation of the summary value.

-bd

[Alexto94103]

<<The good news is that it can be done. The bad news is that it is a little involved. What you have to do is to sort, summarize and then export the data to a temporary file. Then import to another FM file, sort and print. Basically you must create a data set with one record for each summary value, then you can sort the records. In this data set the summary total is just another field, not a number created on the fly by FM.

If you were hoping for a solution that actually allowed you to sort by your existing summary field, sorry I know of no way to do this as any resort disturbs the sort order which allows the creation of the summary value.

-bd >>

Gee!! How involved and time consuming! I was really hoping for an easier solution, but if this is all I can do, I will have to live with it. Thank you for your help!

[-Queue-]

You could also create a calculation field that uses a self-relationship based on matching product ids and create a subsummary part by this field. If your record set is extremely large, the sort may take quite a while. But you could first sort by product id, then loop through the records and omit all but one of each product id. Then you wouldn't even need a subsummary part; you could do this in browse mode on a body part.

[LiveOak]

Thanks JT, this is the best technique if you are able to generate the summary information with a relationship. In cases were the sort involves nested subsummaries, suitable compound keys may not be easy/possible.

In this case it is probably better to do the reporting from the Product file sorted by the total amount. This inherently gives you a single record for each product.

-bd

[-Queue-]

Agreed, but it appears from the description that there is only one file, hence the need for omitting all but one of each product.