A Sorting Anomaly

[Jim Carr]

I have a simple sales report. Each line item summarizes the sales activity of a given dealer. There are no Body parts in this report.

The summaries are sorted by dealer, then by total sales -- best performer at the top.

Viewing the sorted report in Preview mode, the report works perfectly, with one maddening flaw. When (and only when) there are more than 500 to 510 records in the found set being summarized, the TOP dealer on the report shows incorrect summary totals-- Switch to single-record Browse mode and the correct totals are displayed! But go back to Preview mode and these first-summary totals are way smaller-- but as I said, ONLY if the found set is over 500 records or so (it varies with the found set). Omit a few records and the totals start showing correcly again.

The top-selling dealer always appears on top, even though the incorrect totals shown should put him way down on the list. That is, he's sorted as though the correct totals were indeed being displayed.

All of the fields are easliy large enough to display all content, and they do in browse mode.

I hate to confess that I am utterly mystified. Any ideas? An illustration is attached.

FileMaker Version: 5

Platform: Mac OS 9

[Leb i Sol]

Hey Jim!

I had no luck with your attachement...but I would double check your records and make sure that they are related (if relationship is used) or that records trully exsits. See if you can note those records that are NOT being incuded into your report and examine them manually. Also, if you have re created this report and allowed "wizard" to generate the script sometimes the script does not get "overwritten" properly...so re-do-re-name your report. perhaps and updater for your FM 5 is in place?

I am bit worried/instrested to see your file.fp5 as you said that "there are no Body parts"....anyhow, these are a few things that come to my mind..

sounds rather odd...

Good Luck and post some more as go along.

[Fitch]

I've seen this before. You are sorting on a summary field (the dealer total) and probably trying to sort on more than one summary field. You can only sort on one summary field.

[Leb i Sol]

did u pre-make the sum/total fields or did u try letting the wizards do it?

[Jim Carr]

I made the fields; no wizards.

And what really burns me is, it works fine with,say, 450 records. But not with 550! How can that be?

[Jim Carr]

And no scripts are needed to make this problem happen. A manual find and sort is ll it takes. It seems to be only the size of the found set that makes the difference.

[Ocean West]

Thinking out loud here... you don't have the "running" total checkbox checked on the summary field? Do you?

[Jim Carr]

Thanks for looking.

No, I don't have the "running total" box checked.. Should I?

[Ocean West]

I created a test file. It seems to work here...

FileMaker Version: Dev 7

Platform: Mac OS X Panther

test.fp5.zip

[Jim Carr]

But how many records are you summarizing?

I have discovered that the records themselves are sorted and grouped alphabetically by dealer, then re-ordered by Total Sales for the report and preview. And that the true totals for the top-selling dealer are being replaced by the totals for the first ALPHABETICAL dealer! I wonder if this gices anyone a clue?

I have created abbreviated versions of the necessary data. I don't want to post them, but will send them to anyone willing to take a look at the file.

[Fitch]

In addition to the summary field, FileMaker requires you to sort on at least one other non-summary field. I got a copy of Jim's files and discovered that the subsummary part was sorting on a local "Dealer Name" field, but the field displayed on the part was a "Dealer Name" from a related file. Changing the sort for the subsummary part to the related field, then sorting on the related field, seemed to fix the problem.

[Jim Carr]

Wow. The Fitchmeister! That was the problem. What can I say? Duh.

This forum is just awesome.

I still wonder why the problem occurred only above a certain number of records, but, at least the mistakehas been discovered.

Thank you to everyone who looked at my problem!