determine record overlap

[ori]

Hi all - new to this forum and (mostly) new to filemaker

I am trying to do the following:

I have one reference table with values like this

BatchID; SerialID; Start; End

where BatchID is a serial number for a set of entries, SerialID is a unique ID for each entry and Start and End are integers that specify a linear coordinate for the record. E.g.

BatchID; SerialID; Start; End

1; 1; 100; 200

1; 2; 1000; 1500

2; 3; 110; 230

I now want to use records from a second table and find out whether a given value in the second table has coordinates overlapping with those of a record in the reference table and if yes with which batch. E.g. for the records

RecordID; Start; End

X ; 150; 250

Y ; 80; 109

Z ; 8000; 9000

I would like to get information akin to:

RecordID; Start; End; OverlapBatch1; OverlapBatch2

X ; 150; 250; Yes; Yes

Y ; 80; 109; Yes; No

Z ; 8000; 9000; No; No

I have tried quite a few things with conditional calculations (case function) and using complex relationships - but seem to get nowhere so any help/suggestion would be appreciated.

[comment]

The first part is easy. Let's call your first table Data and the second one Test. Define a relationship between them as:

Test::Start ≤ Data::End

AND

Test::End ≥ Data::Start

Now you can put a portal to Data on a layout of Test and see all overlapping records from Data in it - including their BatchID.

The second part is more problematic, because you'd need to create an individual relationship for each BatchID, and hard-code the specific BatchID somewhere, e.g.

Test::Start ≤ Data 2::End

AND

Test::End ≥ Data 2::Start

AND

Test::cBatch2 [color:red]= Data 2::BatchID

where cBatch2 is a calculation field = 2.

Edited June 25, 2008 by Guest
fixed a typo (marked in red)

[ori]

:thankyou:

this works just fine.

Now I just have to find a way to make sorts or finds within our data fast enough - (the actual tables comprise a million or so entries each) and finds/sorts/lookups on those portal fields take far too long.

I would be able to reduce these times significantly by splitting the tables - but I am still looking for more elegant ways.

Within the tables most of the entries are known to be non-overlapping and are marked that way. E.g. just to use the same tables as in my above post - only entries with the same location can potentially overlap:

BatchID; SerialID; Start; End; Location

1; 1; 100; 200; a

1; 2; 1000; 1500; b

2; 3; 110; 230; a

RecordID; Start; End; Location

X ; 150; 250; a

Y ; 80; 109; a

Z ; 8000; 9000; b

now I tried to accommodate this by using this relationship:

Test::Start ≤ Data::End

AND

Test::End ≥ Data::Start

AND

Test::Location = Data::Location

but I don't think that reduces the time spend for any finds or sorts - If I understand the way it works correctly it likely increases the amount of necessary calculations instead of decreasing it - is there a way to account for this apart from splitting the tables?

Thanks again for any help :)

[comment]

Short answer: I don't know.

To elaborate:

It's quite possible that comparison operators take longer to evaluate than an equijoin. It was certainly true for some earlier versions, and it's not supposed to be true now - but you can never be sure.

The other thing is you can never tell in what order the related set is being assembled. Perhaps if you move the location criteria to be the first, you will see some speed advantage.

The practical problem here is that unless you have a very large data set, you cannot test any of this. And when you do, these tests take a very long time. One thing I can say with certainty: never count the result of the first test. There will be a VERY significant difference after the required fields have been indexed.

[mr_vodka]

Someone asked this last year...

http://www.fmforums.com/forum/showtopic.php?tid/189339

Edited June 25, 2008 by Guest

[comment]

FM does not take into account which order your multi-predicate relationships are entered at. No matter how you arrange each criteria of the relationship, it will rearrange the order by what is easier to FM first. IOW, it will try and take care of all the equijoins PRIOR to any theta joins.

I wish there was a place where these things are documented. FMI could really learn a thing or two from their parent company.