First off, thank you to anyone who might be able to help. I’d provide examples, but I think the question itself is pretty straightforward. Thank you to anyone who can provide direction on this.
In one table I have a few thousand records that represent segments of a pipeline. I have the latitude and longitude for both ends of the pipe (upstream “US” and downstream “DS”), as well as the average of both (6 columns). In another table I have a form setup that would represent crossings of said pipe, and it includes a latitude and longitude column as well. I want to setup a rollup or look-up column that provides the record (or records) from the first table that fall within a tolerance zone from the latitude/longitude entered from the form (0.5 x average difference between US & DS Lat/Long, respectively).
This is what the source table (1st table) looks like
This is what the Form table would look like, including what I’d like to see the outcome look like (without the manual entry).
Hey Dillon, would love to help but could use a specific example to help clarify things
Let’s use Crossing 1 as an example. You mentioned the tolerance zone is 0.5 x average difference between US & DS Lat/Long, respectively. With reference to the figures below, could I know how we would calculate said tolerance zone(s)?
Thanks for the interest and willingness to help. There are two columns on the “Toy Joint Log” tab labeled Latitude Delta and Longitude Delta which finds the difference between US & DS respectively. I intend to use the average of those numbers, currently 0.0001349 for latitude and 0.0000117 for longitude. Does this help?
0.5 x average difference lat = 0.5 x 0.0001349 = 0.00006745
0.5 x average difference long = 0.5 x 0.0000117 = 0.00000585
Lower lat tolerance range for Crossing 1 = 42.2252514 - 0.00006745 = 42.22518395
Upper lat tolerance range for Crossing 1 = 42.2252514 + 0.00006745 = 42.22531885
Lower long tolerance range for Crossing 1 = 177.31347820 - 0.00000585 = 177.31347235
Upper long tolerance range for Crossing 1 = 177.31347820 + 0.00000585 = 177.31348405
If a Joint’s US/DS lat value is larger than or equals to 42.22518395 AND lower than or equals to 42.22531885, then we want to know about it
If a Joint’s US/DS long value is larger than or equals to 177.31347235 AND lower than or equals to 177.31348405, then we want to know about it
Apologies for the questions; once I know exactly how the math works I’ll be able to put a script together for you
Thanks for the requested clarification, and your willingness to work on a script for me (that’s awesome!). Yes, this is exactly what I’m looking to do. It should be noted that eventually there will be about 45,000 joints of pipe (records) separated into 25 different system areas. The systems will be kept in a 3rd table and linked to the joints. I’d like to do a rollup formula that looks for the average tolerance and divides that by 2 (I know how to do that one). I bring this up because the the formula starts to look more like this:
This is so cool that you’re so willing to help with this, wish I was somewhere close so that I could buy you a beer as just a thank you for the consideration so far. With respect to the different systems, that will play a part because we have 50, 60 & 80 foot pipe lengths between those systems. That, however should be cured by the rollup, so I think the formula you’re headed towards will work (close counts). Also the button solution is elegant and will work great with the interface I need to use with this data.
In looking at your profile, I see you are a freelance programming consultant. I’m thinking that it might be wise for us to connect next week to carry this conversation further. I’ll message you directly on the matter. Thanks!