I have 2 tables, say Table A and Table B. Each table has address columns. I need to filter 5 rows from Table B for each row in Table A if it meets a distance condition based on the address fields. Rows in both tables will be updated on a daily basis. Approximately each table has 1000 rows as of today.
An example, for each address in Table A, I need addresses that are within 10 mile radius from Table B
The approach I’m thinking to implement is:
Have a new table that has the address column from Table A, and 5 more columns (to hold matching addresses captured from Table B).
Schedule a Zap to run once a week
Clean the new table every time the zap runs
Fetch all rows from Table A and Table B
Write a piece of Python code and build the dictionary that I want
Write this back to the newly created table
It seems to be logically correct. I just wanted a few more eyeballs to see if I’m overlooking anything, especially from a Glide Updates perspective. Each week, I’ll be using 1000 updates when i write to the new table plus few updates to delete, to fetch rows. Thus, got a doubt whether there is any other common approach, or anything else that I need to be aware about.
Thanks for taking the time to read this relatively long post
If it works for your use case, I would assume that you are selecting an item from table A to view it. At that point, I would write the address to a column in the user profile table, or a user specific column in some sort is helper table. Then pull that address into Table B with a single value column. Have your Distance column compare against that single value column and filter a collection to only show those records with a distance less than 10.
Yes, but calculating distance always happens on the device. All it’s doing is a somewhat simple trigonomic math calculation to calculate distance between two sets of coordinates. Nothing super complicated (if you are willing to consider trigonometry as not complicated ).
If you are using addresses instead of coordinates, then it does have to first geocode the addresses to coordinates behind the scenes before it can use those coordinates to calculate distance, but I would hope that it’s only doing that geocoding once per address and not doing it over and over again. Not sure how Glide handles that part behind the scenes.
Look at it this way. If you set up your distance column to calculate based on user’s current location, then it updating those distances continuously as the user moves, so it would be a whole lot more frequently that what I suggested in my first post.
@Jeff_Hager Thanks for the detailed response. Probably I should have mentioned a bit more about my usecase. Both the address columns are static, in the sense, they are not the user’s location. They are the addresses of say Office Buildings and AirBnB type places. A record gets added when an Office is added or a AirBnB type place is added. Thus, it is static in that sense.
If that’s true, then Jeff’s approach is sound and there will just be a single update involved each time the record being viewed is written into the User Specific Column. Everything else involves computed columns, which do not consume updates.
@Darren_Murphy , @Jeff_Hager - I implemented the approach you explained and it works like a charm. You saved me quite some work and updates Thank you so much.
Infact, I faced the issue of duplicate items in my collection. One of your past post helped me fix that as well. Thanks for that too.
Just that, I need to fix a thing, the action on which I load the required values in Users table, I need to rework a bit there.
).
Thank you so much.