Sorting by Summary Field

[spankalee]

I hope this is an ok place for this. It's really a report question, but there's no general report forum.

I want a report of sales by customer, for a particular range of dates, and I'd like to sort it by sales.

I have a summary field in my InvoiceLineItems table which is a total of the line item cost. For my report I have a layout with a summary part when sorted by customer name. That works fine, but that gives me a report sorted by customer name. I'd like to do a sort like this:

TotalSales

CustomerName

But I can't sort by a summary field.

Suggestions?

Thanks.

[-Queue-]

See FM's online Help regarding 'Sort Records', specifically the 'Reorder the records based on the values in a summary field' portion.

[spankalee]

Thanks! Completely missed that. Looks like just what I needed.

I'm having problems with it though. Basically it's just not working - it's still sorted by customer and now my summary values aren't showing up.

There's three relevant tables here: Invoices, InvoiceLineItems, and Customers.

InvliceLineItems are related to Invoices by InvoiceID, Invoices are related to Customer by CustomerID. TotalSales is a summary field in InvoiceLineItems (total of cost).

The layout has a header and summary part, no body part, adn shows records from InvoiceLineItems. I'm sorting by Invoices::CustomerID and reordering by InvoiceLineItems::TotalSales.

The records are still sorted by CustomerID and the TotalSales field is now empty. If I do the sort again without the reorder the TotalSales fields go back.

weird, huh?

[-Queue-]

Um, I do not think it makes sense to reorder CustomerID by TotalSales. It would only make sense if you were reordering the Cost field by it, but that would not fit your purpose here. I would try creating a GetSummary calc field and sorting by it instead.

[spankalee]

Can you help me understand this a bit more? I did a quick trial, but it's not working.

Should I create the GetSummary calc field in the InvoiceLineItems table? If so, then since I need to specify the break field then this calc field would only be valid for summarizing sales by customer.

Then, what do I sort on? I need to sort by CustomerID to get the summary to work, but I want to sort by sales. So do I sort CustomerID then CustomerSales (the GetSummary calc), or the other way?

Also, why is it that it doesn't make sense to reorder CustomerID by TotalSales? It seems to match the example in the FileMaker help. There they have regions and sales:

A 800

B 150

C 300

D 1200

and reorder by sales:

D 1200

A 800

C 300

B 150

Isn't this exactly what I'm doing with Customers instead of Regions? Is it because I'm dealing with multiple related tables?

Thanks

[spankalee]

While I'm on this topic, let me take a step back and explain what I was doing before. Maybe I'm going about this all wrong.

Before I tried making this report I simply had a calculation field in Customers that totaled the sales. It was nice since it worked from browse mode, you could easily sort of many different fields, and omit customers, etc. There are two problems though:

1) The sum was for all dates, and we ned to do reports in certain date ranges.

2) There are several types of InvoiceLineItems, or rather they have different status codes like Order, Shipped, Invoiced, Canceled, Return, etc... Only certain codes apply to the sum

edit: 3rd problem: The calc field I have in the Customers table is based on a relationship to the Invoices file, there's no direct relationship between Customer and InvoiceLineItems, so I'd have to pipe in the CustomerID from Invoices via a calc. I hate that

I can see how to still do this with a relationship and calculation, but it seems like it might be overly messy.

First I would need a begin and end date as part of the relationship, then I would need several constants to filter out the right status codes. Since there's no "or" for relationships, I would actually have to have one relationship for each code I care about. Then I would have a calculation field summing it all up.

It would certainly nice to keep all of this working in browse mode, but it seemed more efficient to do a find and sort with summaries and go into preview mode. The sorts take forever though (at least several thousand line items, but that doesn't seem like a lot to me, so I don't know why it takes so long), and this summary business is getting messy as well.

The FileMaker documentation doesn't really say which method is better, but from the guys in the trenches, what's preferred?

Edited July 27, 2005 by Guest

[-Queue-]

You would not necessarily need multiple relationships for the status codes. A global text or calculation field, which contains each desired status code separated by

[comment]

Sorting by CustomerID, reordered by summary field (Total of ExtendedPrice) should work.

I believe duplicating CustomerID as a lookup in LineItems should speed up find and sort. Same with any other fields that are needed for find/sort, but are unstored from Line Items point of view.