Find the closest reseller ~ best way to set it up

[Drew Hoffman]

Long time reader, 1st time posting, so sorry in advance if I did it wrong.

I want to create a script that will return the closest reseller of parts based on zip code the consumer submits. I understand how zip codes work. My question is what's the best way of doings this with out multiple find requests looping a script.

Things to keep in mind

For this example I have 1 table with company and zip code.

There is not a reseller in every zip code

The only way I know how to do this is

A- multiple finds

Make a script that will do a search, then based on the found count widen the results for another search

B- relationship

Make a relationship with a temp field. Then make a script to adjust temp field based on number of records in portal.

Anyone know if you can tell fm to grab the closest match based on a found set? Maybe a weighted sort of some kind.

Thanks

[comment]

I understand how zip codes work.

 

Apparently I don't, because I don't understand your question. If all you have is zip codes, and the zip code submitted by the customer doesn't match one of yours, I don't see how you can "widen the search". That is, unless you also have the relevant geolocation data of both the customer (i.e. you have a way to lookup the customer's latitude and longitude, based on their zip code) and of all your resellers.