I'm a novice trying to create a summary report of clusters of various (n) true/false as well as max/min runs of true/false in the dataset. I can do the job in Excel but would really appreciate any help to automate this solution in FM.

Edited May 16, 201510 yr by Phylum

Could you explain in more detail the type of data you track and how you need to report on it? Perhaps my own ignorance is at fault, but I have no idea what either "a summary report of clusters of various (n) true/false" or "max/min runs of true/false" means. If it means what I suspect it does, then Filemaker may not be the best choice for this task.

Edited May 16, 201510 yr by comment

Thanks your reply.

The data is Boolean (true or false). I'm trying to extract two pieces of (summary) data, for example:

 - how many times "true" occurs, say 3 times (pick any number), in successive records, and

-  the maximum times that "true" occurs in successive records in the whole dataset

"Successive"? What do you mean? Consecutive? Likely not. Are you saying you want to find the number of occurrences in the found set and the  number of occurrences in all records?

Yes, my bad, consecutive. The number of times 'true' occurs consecutively (say 3 times) in the whole record set as well as the highest number of consecutive "true's" in the whole set. 

ConsecutiveTrue.rtf

Edited May 17, 201510 yr by Phylum

The data is Boolean (true or false). I'm trying to extract two pieces of (summary) data, for example:

 - how many times "true" occurs, say 3 times (pick any number), in successive records, and

-  the maximum times that "true" occurs in successive records in the whole dataset

Yes, that's what I was afraid of. The problem with this request is twofold:

1. Filemaker does not consider your records to be in any particular order, until you sort (or unsort) them;
2. Filemaker is very good at reporting on your data; it is much less effective when you ask it to report on what's in-between your data.

In order to find out the length of a "run", you will need a script to loop through the records (after finding and sorting the records you wish to include in this report), and write down the count every time a "run" ends. Only then you will be able to summarize these numbers.

If your data is static (i.e. it's collected and will not change anymore), then you can run this script once and have it write the "run" lengths either into the records themselves, or by creating records in another table. Otherwise, you'll have to do this every time you want to produce a report (and in such case it would be preferable to use another table and leave the original records untouched).

 

 

 

Edited May 17, 201510 yr by comment

I've used GetNthRecord to catch small clusters however it was obvious it would become unwieldy and outliers would eventually be missed. Thanks for your advice.