I wound up resolving the issue (sort of) in Excel, by adding a field to each table and rounding the time values to the nearest 20 seconds as it is impossible for the events to start within 15 seconds of each other. That gave me values that I could use as Primary and Foreign keys to tie the two tables together. I might later try to do the same thing in Filemaker, but for now Excel VLoopup did what I needed, and I then imported the results into Filemaker.
This rounds time/timestamp to the nearest _n seconds:
Let ( [
_n = 20 //number of seconds
; _dt = Get ( CurrentTime ) // Get ( CurrentTimestamp ) if your field is a timestamp
; _dtnr = GetAsNumber ( _dt )
; _dtnr5min = Div ( _dtnr ; _n )
; _rest = Round ( Mod ( _dtnr ; _n ) / _n ; 0 )
; _dtround = GetAsTime ( _dtnr5min * _n + _rest * _n ) // GetAsTimestamp ( ) if your field is a timestamp
//_dt & ¶ &
I've had a good look at your code, and generally understand it. I've since thought that sooner or later I'm going to get to a point where one value rounds down, and the other value that needs to match, rounds up, so I would need an additional check to highlight any unmatched fields (the second table has a lot of useless data, as it logs everything, not just legitimate events. Unmatched fields would then have to be manually matched.
I did see a similar question on another forum (Postgres, I think), where someone had suggested it was possible to match on a range, by matching BETWEEN values. I can't find anything on a Filemaker version of that - is it possible?
How di you want to make the join, by SQL or in the FM relationships graph?
if SQL: ...WHERE yourTimestamp BETWEEN ? AND ?
or mor performant
...WHERE yourTimestamp >=? AND yourTimestamp <=?
; "" ; "" ; matchTimestampLowest ; matchTimestampHighest )
if FM-relation: make two calculated unstored or calculated global autoenter fields in the left side table of your relation and connect:
minField <= yourTimstamp and
maxField >= yourTimestamp
Hi Otmar and thanks again for your help. I have used SQL a bit in Manifold GIS so I think I've got the SQL version figured out - in all the training videos I've seen on Udemy, Lynda and VTC, none of them cover SQL queries so it may take me a bit to get it working. I'll give a try at your Filemaker Relation version as well.
Thanks & Regards, Al