Creating a table based upon location relationships

  • 0
  • 1
  • Question
  • Updated 1 year 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?
Photo of Patrick Griffin

Patrick Griffin

  • 80 Points 75 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
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.
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
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
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.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Patrick, 

Im curious, what are the items in table 1 and table 2?
Photo of Patrick Griffin

Patrick Griffin

  • 80 Points 75 badge 2x thumb
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.