Sorting a portal according to physical distance located in a parent table

[jshobar]

I have two tables of data. One is a list of customers, and the other is a list of stores. I have the Latitude and Longitude of everything and know how to calculate the distance between any two items.

I'm looking to create a portal in the customer table that shows the 3 closest stores. I think I can accomplish this by creating a sorted portal, but I am unsure on how to sort a portal in this way. Any ideas to get me going in the right direction would be greatly appreciated. Thanks in advance.

[comment]

How many stores do you have?

See also:

http://fmforums.com/forum/topic/71942-limiting-number-of-portal-results/

[jshobar]

Thanks, reading through that post now.

I have 25,000 stores in the list. In the end, I'm really only looking to locate the 3 closest stores and append the original customer table with this info. Do you think I'm going about this the wrong way?

[comment]

Well, the problem is that in order to find the store that is closest to one customer, you need to calculate 25,000 distances first...

[jshobar]

Yes, that is correct. My line of thought was calculate these 25k distances, rank them, take the top 3. Then repeat for each customer.

[comment]

You could do that - but then if the customer's address changes, you also need to update those 3 stores. And if you add/delete/relocate a store, you must do the entire thing again for ALL customers.

I would prefer to pre-filter the stores by a minimum distance - but ultimately it's a business decision.

[jshobar]

Comment, as usual, you bring up some very good points. For this particular job, it will be a one time thing. By this I mean, we are mailing out to a new list of customers every month and do not have to worry about store or customer addresses changing. I'm having trouble calculating the distances to each store. It seems this calculation needs to be done in the stores table, but then it is not unique to each customer record. I know there must be something obvious I am missing.

[comment]

It seems this calculation needs to be done in the stores table, but then it is not unique to each customer record.

No, it cannot be. If you want to pre-calculate the distance of each customer to each store, you will need another table with [25,000 * number of customers] records.

You can, however, have an unstored calculation in the Stores table calculating the distance for the currently selected customer.

[jshobar]

Ah, I see now. I think I understand how to use a table with [25k * # of customers] records, but it seems using an unstored calculation is the way to go. Because this would only calculate for the selected record, would I need to write a script to run though all customer records? I'm searching the forums now. You've already been a big help, and your direction is greatly appreciated.

[comment]

Because this would only calculate for the selected record, would I need to write a script to run though all customer records?

Yes. But you would probably do that anyway, if you want to send individual mails.

[jshobar]

I have moved forward with using a script. I've set up two global fields that populate according to the current record's lat/lon. I have a cartesian relationship set up to between the customer and store tables and I'm specifying the relationship to be sorted according to an unstored distance calculation. All the distances calculate correctly, but the portal doesn't show the correct sort order. However, if I open the calculation field and close it, the portal does resort to the proper order. Any thoughts on why the relationship would not update it's sorting? The distance calculation updates immediately, just not the sort.

[comment]

I open the calculation field and close it

Not sure what you mean by that. Try ending your script with:

Refresh Window [Flush cached join results]

[jshobar]

Yes, sorry, I should have been more clear. If I go to Manage Data Bases > Fields > open my distance calculation field and then close out of it, the relationship resorts to the correct order.

Placing 'Refresh Window [Flush cached join results] ' at the end of the script worked. I knew it had to be something simple. Thanks for your help.