# Creating a table based upon location relationships

• 0
• Question
• Updated 2 years ago
• In Progress
I am having trouble trying to group rows based upon a table relationship. As of right now i have two tables. Table 1 with specific locations, and Table 2 with rows that i want to be assigned to the locations listed in Table 1. I want to know if its possible to assign the table 2 rows to table 1 based upon the location of the rows in table 2.

For instance if a row in table 2 is within 10 miles of a location in table 1, it would be assigned to that location. I would also want to allow the rows of table 2 to be assigned to multiple locations in table 1 in case of any overlap. Both tables have complete address fields that have been recognized and found via google maps.

Can this be done? and if so how?
• 80 Points

Posted 2 years ago

• 0
• 1,048 Points
First, I would say that you would need a 3rd Table that would hold the connected records between Table 1 and Table 2. The 3rd Table will allow you to have connections to records in Table 1 from Table 2.

Second, Quick Base doesn't have anything built in that will determine the distance between 2 points. To do this, you will need some type of script that would do a comparison of all records in Table 1 to those in Table 2, determine the distance between them, and then create a record in Table 3 if the distance meets your specified criteria. You may be able to build something like this using Zapier or Workato, but I've never attempted distance calculations in either of them.
• 2,216 Points
Actually, if you have the latitude and longitude points stored for each of the two locations (we use a Zipcode table to bring in those lookup fields), you can use a formula. Not sure if QuickBase has all of the trig functions necessary to do this calculation (I haven't actually implemented this yet in QB), but I have had it working in our legacy app in MS Access for the past 20 years. See this article for guidance: https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula
• 1,048 Points
Quick Base does not have the necessary math functions to perform this action within a formula field. Haversine Formula calculations would have to be done by a script with the results populated back to Quick Base.
• 31,698 Points
Patrick,

Im curious, what are the items in table 1 and table 2?
• 80 Points
Matthew,

Table 1 would be a list of locations by address. Table 2 would be a list of individuals along with their home address.

The goal is to be able to separate all the individuals from Table 2 and assign them to the locations in Table 1 based upon their proximity from one address to the other.