5 Replies Latest reply on Dec 10, 2015 7:08 PM by alphuket

    Table Join on Close Value

    alphuket

      I need to join two tables related to the same activity, but the only relationship is the event time.  The problem is that one Table uses GPS time, and the other uses a Computer clock time that can drift by up to a 5 seconds from GMT time.  Could anyone suggest a good method whereby I could join the two tables?  Thanks. 

        • 1. Re: Table Join on Close Value
          AlPhuket

          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.

          • 2. Re: Table Join on Close Value
            okramis

            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 & ¶ &

            _dtround

            )

             

            Regards

            Otmar

            • 3. Re: Table Join on Close Value
              alphuket

              Thanks Otmar,

              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?

              • 4. Re: Table Join on Close Value
                okramis

                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


                best regards

                Otmar

                • 5. Re: Table Join on Close Value
                  alphuket

                  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