2 Replies Latest reply on Sep 20, 2013 10:22 AM by DuganMorgridge

    Comparing records using self-join relationship



      Comparing records using self-join relationship


           I am  -not - a developer, really, but I do consider myself a somewhat advanced "user" of FM Pro.  I'm currently in the midst of a project to update the database at the very small arts management company I work for.  It's an adventure.  The current solution is a dos-based quasi-relational database with a ton of design flaws.  Importing the historical data will be hell, but I'm not there yet.  

           I'm trying to build in some up-to-date features and capabilities, and one is giving me fits.  We do business with a lot of different entities who present our various artists in concerts (Presenters).  

           So, of course, I have a table for Presenters that carries the basic information for each (name, location, what they do, etc.  When we get a contract with one presenter, usually the goal is to then find another presenter within a comfortable travel distance to present the same artist the next day or the day after next (which increases the artist's ROI on travel costs). 

           I need to be able to compare the distance between one presenter and, essentially all others.  I have a zip code database set of with Lon/Lat data for each zip code (not GPs accurate but we just need to know it's within a, say, 1-4 hour drive away.  I found a custom function to calculate the distance between two Lon/Lat geopoints (using the zip codes to establish those two values).

           What I ultimately want to be able to do is have a portal (filtered to show only records within the proper range) on the main "Presenters" layout that allows me to see a list of other presenters (related using a second TO named Presenters SelfJoin where ID # =/= ID#).


           The distance function takes 4 parameters - ( lat1 ; lon1 ; lat2 ; lon2 ; units ) and spits out the distance between the points identified by the two lon/lat pairs.  I have a calculated field set up to display the result of the following:

           Distance (  Presenters::Lat  ; Presenters::Long ; Presenters SelfJoin::Lat ; Presenters SelfJoin::Long ; miles )

           The calc field is displayed in the portal showing records from the SelfJoin TO on the main Presenters layout. The calculation is evaluated within the context of the SelfJoin TO.

           Using 4 sample records (remember I haven't imported all the actual data yet) it seems to work  - when I browse record 1 everything is great, the portal shows the other 3 records with proper mileage counts.  But something is falling apart. When I browse to record 2, the portal shows records 1, 3 and 4, but the calculated values remain the same in 3 and 4  and nothing shows in 1. 

           Obviously I'm having trouble making sure that the parameters get pulled from the lon/lat fields of the proper records, but I'm not sure how to make it work.  Is this completely impossible?  Would I have to script it?  Scripting seems impractical because of the thousands of presenter records that will eventually populate the table - and since I want to narrow the set down using the result of the calculation, I don't have a way to constrain the set another way first.

           I have a feeling this boils down to something simple that I just haven't encountered yet, but maybe not.  Any helpers out there?

        • 1. Re: Comparing records using self-join relationship

               What does your self join relationship look like? Is your calculation defined an unstored field of type calculation? (not an auto-entered calc on a number field).

               You'll need 3 table occurrences and 2 self joins to get the correct matchups and calculated distances for your filtered portal.


               Presenters::anyfield X Presenters|All::anyfield

               Presenters|All::gSelectedPresenterID = Presenters|Selected::__pkPresenterID

               gSelectedPresenterID is a global field so that all presenter records match to the same presenter for your distance calculation. You'll need to use a script--I'd perform it with OnRecordLoad on the Presenters layout, to update it with the value of Presenters::__pkPresenterID before you can get everything to work for a given presenter. Then your calculation field needs to refer to fields in Presenters|Selected and calculate from the context of Presenters|All in order to compute a different distance from each presenter to the current presenter.

          • 2. Re: Comparing records using self-join relationship

                 Thanks a million!  I'm still wrapping my head around the different ways global fields can be useful... I only had two table occurrences involved before.  I did have the unstored calculation right, and now it works beautifully!