XY event point related to multiple collected points

Question

I am new to Field Maps and creating maps, most of my experience is just consuming. A little back story... We currently have septic system data stored in tables on a SQL server that is hosted and available as an XY event layer online. This data is used to feed an Access frontend. We currently enter the XY in Access when we get an application. We just randomly pick a spot for the point on the parcel. Currently every so often I recreate the event layer to update to have the new septics included.

We are preparing to change how we do installation inspections and gather measurements. We will be using a Trimble Catalyst. I was able to set up a map to use in Field Maps, make a smart form for collecting the points with a dropdown with names like tank, lateral... No problem there points collect fine and all.

Where I am getting lost is a way to select the XY event layer point which has all then data for the system and tie that point to the collected points. Not exactly sure how to explain this, so please bear with me...

My septic table has a primary key, I would like to have that PK as a foreign key in the collected points table. The only way I have been able to do that is to enter it manually into a field when the point is collected. Even if I could start with a dropdown that I would be able to select the septic permit number from and then have a relationship to that record in the collected points table but I have not found a way to do that. I have asked the smarter GIS people within the County but no one was able to figure it out either.

I hope I have explained this to where others can understand and thanks in advance for any help.

MatthewCory · Answer

Sorry for the delay.

I don't really need to capture the XY of the system, that was just how I was able to plot the systems on a map and be able to see which properties had permits and get info about each properties systems, since the data we have any spatial.

The septic table is on the SQL server and a connection allowed to the ARCGIS server. I create the XY event layer in Pro and update it as a shared layer.

The Access frontend is actually really complex. I developed it over 10 years ago and has been a constant work in progress adding things we want it to do. Everything we do in our department goes through that program. It is able to create permits, attach documents to those permits, email documents, do system sizing calculations based on soils, etc. Just a few months ago we had several companies look at it to take our data and create a frontend in HTML5 with all the same features and abilities we currently have. All the bids came in around $150,000.

Our data structure inside of Access is basically a one to many, everything is based off the parcel. A parcel can have many septics, wells, complaints and so on. I wonder if I can make a view that joins our parcels with our septics and serve that as the primary table. In the field can click on the parcel and then add collected points for the system layout. The joined view can have the septic ID key also that will tie everything back to the septic table. Thinking through my fingers now...

RhettZufelt · Answer

I suspect so, but without knowing more about the data, it's hard to really tell.

Could you share the table and points FC?  Probably don't really need to see the data, but what columns are in/being used in both.

Is there a specific reason you need to capture points (xy's) in both the table and the FC?  Are there duplicate points in there (i.e., one location with multiple "inspections")?

Also, how did you publish an your XY event layer as a hosted feature service on AGOL?  Does the sewer table reside directly on SQL server, or is there a copy/view?

And, what is the Access frontend doing that can't be done in a web map?

Don't know that you will be able to edit one dataset with a separate program (Access), and the other with FM without some after the fact scripting or manual manipulation to update the source/related tables accordingly.

R_

MatthewCory · Answer

That's exactly what I want and trying to get to. I think with the way our data is, and unable to change that due to it being a backend to an Access frontend, that is my problem. When I create in Pro and try to share the map to AGOL I get an error that in memory relates are not supported. Am I correct thinking the XY layer is the issue?