I have 3 tables: Properties, Quotes & Zips.

 

Each property has a longitude and latitude and each Quote has a longitude and latitude.  The quote lon/lats are global. Each quote has a field called City_State, which is a calculation of the City and State.  This looks up the lon/lat from the matching City and State in the Zips table.

 

I have a portal on Quotes layout that shows the properties in distance from the Quote lon/lat.  It works fine, but when I go to a new record on Quotes, I have to Cut/Paste either the City or State to refresh the City_State calculation to refresh the portal.  I've tried refresh window, etc and that doesn't help.  

 

Any ideas?  I'm not sure if I have the relationship wrong, but my ideal goal would be to show the properties in distance from the quote and auto refresh every time I look at a new quote...

 

Thanks!

each Quote has a longitude and latitude.  The quote lon/lats are global.

 

These two statements contradict each other.

 

 

when I go to a new record on Quotes, I have to Cut/Paste either the City or State to refresh the City_State calculation to refresh the portal.

 

 

How exactly is this calculation defined?

On Quotes, the Long/Lat are Number fields, Global, Auto-enter Calc from Zips. (simply Quotes::Latitude = Zips::Latitude) If I remember correctly, I kept them as unstored calcs but the portal wouldn't show.  

 

Also, probably a terrible way, the tables of Quotes & Properties are related by Country, since all records in both tables are USA.  

Let's try it this way:

 

1. Define the relationship between Quotes and Zips as:

 

Quotes::City = Zips::City

AND

Quotes::State = Zips::State

 

I am assuming here that both tables have these two fields.

 

 

2. In the Quotes table, define the Longitude field as type Number,  not global, and set it to auto-enter 'Looked-up value:' Starting with table

, Lookup from related table: [Zips] , Copy value from field: Longitude. Do the same thing for the Latitude field.

 

 

3. In the Quotes table, show all records, click into City field, and select Relookup Field Contents from the Records menu. This will populate the Longitude/Latitude fields in the existing records.

 

 

That should be it, hopefully.

Hmm, not seeming to work right.  It appears to only work on the first record of Quotes.

 

I see the zips changing on the fields Quotes::Longitude & Quotes::Latitude, that works well.  The Longitude and Latitude for the properties don't change obviously as they are permanent...  My calculation for Distance is a field on the Properties table, and is unstored and calculates the distance between the Property Lon/Lat and the Quotes::Lon/Lat.  When I open two windows, one with Quotes and one with Properties, the Distance calc doesn't change on Properties when I go to different quotes...

 

Maybe it has to do with my relationship of Country from Quotes to Properties?

 My calculation for Distance is a field on the Properties table, and is unstored and calculates the distance between the Property Lon/Lat and the Quotes::Lon/Lat.

 

Ouch. You didn't say that before. I am not sure that's such a good idea anyway, since it's bound to be slow (all Properties need to recalculate whenever you move to another quote).

 

If I had to do it like that, I would define global gLongitude and gLatitude fields in the Quotes table and run a script triggered on OnRecordLoad to:

SetField [ Quotes::gLongitude ; Quotes::Longitude ]
SetField [ Quotes::gLatitude ; Quotes::Latitude ]

These two global fields are the fields from which Properties need to calculate their distances.

 

--

If that works, you can probably dispense with the lookups described earlier and set the two global fields directly to the  lat/long fields in Zips. But you can't make them calculation fields, because there's nothing to trigger a refresh when you move to another record.

**

 

 

 

Maybe it has to do with my relationship of Country from Quotes to Properties?

 

Continuing to guess: your portal is filtered by the distance, is that right?

Edited February 27, 201411 yr by comment

Ya, I filter less than 50 miles.  I have customers who need a place in a city, and I enter their needs/requests in the Quote layout, and then see a list of all the properties available within 50 miles.  Is there a better way to do the search?  I'm not sure how to get around the distance calculation.  I have several thousand properties, so yes it will be slow...

 

I'll give your idea a shot and see if that works for now.  Thanks for all the help, I greatly appreciate it.

This thread is backwards - we are only now getting to what should have been the starting point, i.e. the purpose of this exercise. So now, two points:

 

1. To filter less than 50 miles by unstored calculation, you could simply include the distance calculation in the portal setup itself.

 

2. I would calculate a "bounding box" in the Quotes table, then use a relationship based on stored data to fetch the prospective properties. See: http://fmforums.com/forum/topic/71942-limiting-number-of-portal-results/?p=346802

Comment:

 

Thanks for all help, I'll give your bounding box a shot when I have some time.