Discussions

Expand all | Collapse all

Creating a table based upon location relationships

  • 1.  Creating a table based upon location relationships

    Posted 07-21-2017 15:43
    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?


  • 2.  RE: Creating a table based upon location relationships

    Posted 07-21-2017 16:24
    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.


  • 3.  RE: Creating a table based upon location relationships

    Posted 07-24-2017 20:06
    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-h">https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula">https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula


  • 4.  RE: Creating a table based upon location relationships

    Posted 07-25-2017 15:10
    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.


  • 5.  RE: Creating a table based upon location relationships

    Posted 07-21-2017 18:03
    Patrick, 

    Im curious, what are the items in table 1 and table 2?


  • 6.  RE: Creating a table based upon location relationships

    Posted 07-21-2017 18:06
    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.