Summaring Fields with more than 20,000 Records

[DBS]

I have an FM solution that generates summary reports. When it generates reports with less that 20,000 records the summary fields calculate correctly. When it generates more that 20,000 records, the summary fields will not calculate at all. I don't know if this is a limitation in FM or if my scripting my be incorrect. Any ideas?

[Brian C]

Is the data you are attempting to summarize based on related data or an unstored calculation field? 20,000 sounds like more than just a coinsidence. It may in fact be a set limit within the application in order to put a cap the memory usage for summarizing unstored data.

After doing a quick test of creating 20,000+ records and using a summary field to total the stored field that containing a 1 for each record, I do not seem to have a problem. I have also tested this example by using numbers between 1 and 1000 in an attempt to try and see if I could break it quickly. So far it still seems to work.

Can you provide us with an idea of the length of the numbers you are adding together? It may be working for me since my example may be an overly simplistic approach to the problem.

Have you tried to export just the data from the field you are attempting to summarize? See if you continue to have the same problem in a new file with just that amount and a summary field.

The only other thing I can think of which could cause this might be corrupted data in one of the records which is causing the summary to return unpredictable results.

[Ender]

I'm with Brian, that this might be a problem with corrupt data in one of the records.

Perhaps you can give us more info on the type of summary you are doing and what the fields are.

[DBS]

I am using sorted data that is all within the same database. For example: I am adding together the length of time that a student is in the center. For one years worth (which is just under 20,000 records) the total is 30,000 hours. I have not tried to export the data yet, I can try that. Also, if it is a corrupted record, how do I find that one record?

[comment]

Just a hunch: I don't have ver.6, but out of curiosity I went to see if ver.4 has a maximum for a calculation resulting in Time. It seems that the limit is just under 32,767 hours. Beyond that, I get a question mark. A coincidence?

[DBS]

It's possible that the problem is that there is a limit on the number of hours ver. 6 will calculate. That would make sense because anything 30,000 hours or less calculates fine. Is there a way to find out if there is a limit in ver. 6 and if whether or not anyone knows of a work around to this problem?

Julie

[comment]

To test, create two fields: num (Number) and cTest (calculation, result is Time) =

Time ( num, 0 , 0 )

Enter 32767 (or higher) into num and you'll know.

The workaround would be to translate the data into a number, BEFORE summarizing. For example, if the field being summarized is Hours (Time or calculation resulting in Time), create a calc field cDecimalHours = (calculation, result is Number) =

Hours/3600

and summarize that.

[DBS]

You were right, there is a limitation in ver. 6 on calculating time. I changed my calculation as you said and everything works fine. Thank you so much.