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?