Calculation and Relationship

[David Garza]

Hello,

I am attempting to create a portal that will list all of the establishments from one table (REP_establishment_distance (TABLE: ESTABLISHMENTS) ) on another layout REP_Representative (TABLE: Sales_Reps). The problem is I only want to list the records that are within x miles of each other which can/is calculated with a Custom Coordinate Function.

In the REP_ table, I have a static fields called WithinDistServiceArea. In the Establishments, I have a non-stored calculation field called EstRepDist. The distances must be calculated on the fly because there are 150 different Sales Reps and over 60,000 Establishmnets.

Each distance is different for each Sales Rep and Establishment; I calculate over 9,000,000 different combinations to calculate the distances. I do not believe a JOIN table would be feasible, but then calculating each distance everytime the REP_ layout is opened would not be feasible either.

What would you suggest? I have thought about manually selecting each Establishment, a one-time search for any distances (using a looping script which I already have to export the locations of the Establishments into a KML or XML file for Google Maps/Google Earth) but when new establishments are added, this would not work.

Thank You and God Bless!

David

[comment]

See if this points you anywhere useful.