You just need to do that work on the front end, and store that count as an indexed number at import time. This assumes the time ranges are fixed; a user-selectable range wouldn't work like this. Just have to do what you can to get that count stored and indexed.

Failing that, a fallback plan would be to isolate your 6 hour set of records (which at this point includes count = 1), and then use a sort, loop and omit. Sort by count descending, go to the last record, and walk backwards until you find a count = 2. Then go to the next (first record where count = 1), and omit multiple to the tune of 1 million (something far bigger than you need to omit all the count = 1).

If there are thousands of count = 1 records to walk backwards through, then you could optimize that part by guessing: Go to record (Round(FoundCount * .7; 0)). If that's a 2, you need to go further down, if a 1, back up the list some.

This second thing takes advantage of the fact that sorting on an un-indexed field a small subset of records is much faster than searching one.

Geoff

> -----Original Message-----
> From: FileMaker Pro Discussions [mailto:FMPRO-
> [log in to unmask]] On Behalf Of Peter Buchanan
> Sent: Wednesday, January 11, 2012 9:55 AM
> To: [log in to unmask]
> Subject: Related records, constraining found set.
> 
> Client has a database, containing vehicle registration numbers (plate
> numbers), recorded by a network of cameras. So each time a vehicle passes
> a
> camera, its plate is read, and recorded. Data is outputted from camera
> software in .csv format, and into Filemaker. Six months' data is retained in
> Filemaker, this totals approx 6 million records. The data is recorded in 4
> fields : Plate, Date, Time, CameraName
> 
> Client requires a reporting function, to show one day's data for camera1
> only, with a time range midnight to 6am. Finding these records is easy and
> remarkably fast (a few seconds). But client also requires that this found
> set then be constrained to show only those plates with at least 2 (or more)
> records, for this same date and time range. So if a vehicle moves only once
> through camera1 in the date/time specified, drop it. To this found set, he
> would want to add any records for these plates, but from the remaining
> cameras in the network, as long as they match date and time range.
> 
> I created a self join relationship with four match fields :  plate, date,
> time_start, time_end.
> 
> Then a calculation field : Count(matching records)
> 
> Searched for records with Count>1
> 
> This takes many minutes, and I can understand why.
> 
> There has to be a smarter method?
> 
> Any help appreciated, once again.
> 
> 
> Regards
> 
> Peter