Thanks for all the suggestions. Adding a new table and processing records there has produced a fast and trouble-free solution. Peter From: FileMaker Pro Discussions [mailto:[log in to unmask]] On Behalf Of Blake Downes Sent: 11 January 2012 19:34 To: [log in to unmask] Subject: Re: Related records, constraining found set. Peter, Here's a variation on Geoff's suggestion. It involves adding a table (could call it PlateAlerts) to your DB, but your client will probably like the added capabilities it provides. I'd also suggest adding a boolean MidniteToSix calc to your existing table to simplify finds and relationships for this task. Main script: 1. Find the set of records for specified Date where MidniteToSix = True (Daily average of 33000 or so should be reduced to late night traffic of a few thousand plates, based on your million-per-month count) 2. Sort by CameraName then Plate 3. Step through this found set counting the records where Plate and CameraName are the same as the previous record (use variables to store previous record values). When you reach a new Plate or CameraName or end-of-file, if the counter is greater than 1, create a new PlateAlerts record including Plate, CameraName, Date, and value of the counter from your variables. (NOTE: At this point, you could run a subscript to update this new record with time and camera data via a Plate|Date relationship, or you can handle that after creating all the PlateAlert records for the run.) Reset your variables to the new Plate and CameraName and continue. Please note that this will create a PlateAlert record for each camera that a vehicle visits more than once, so your reporting will need to account for this. You could change the sort order to avoid it but that has other side effects. Your client will probably be excited about the ability to use the PlateAlerts table to identify repeat vehicles over a span of days, and determine routes taken based on the camera timestamps, so you can expect more work. Blake On Wed, Jan 11, 2012 at 9:34 AM, Geoff Graham <[log in to unmask]> wrote: 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