Forum Discussion

PatrickGriffin's avatar
PatrickGriffin
Qrew Member
8 years ago

Creating a table based upon location relationships

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?
Ask about Quickbase functionality
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    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.
    • MichaelBarrow's avatar
      MichaelBarrow
      Qrew Cadet
      8 years ago
      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
    • ArchiveUser's avatar
      ArchiveUser
      Qrew Captain
      8 years ago
      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.
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    Patrick, 

    Im curious, what are the items in table 1 and table 2?
  • PatrickGriffin's avatar
    PatrickGriffin
    Qrew Member
    8 years ago
    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.