Question about efficient managing of spreadsheets/shapefiles

[EricRenfer]

I could use some help with managing information in a spreadsheet that is then used to create a shapefile. We have an inventory of storm sewer infrastructure that needs annual inspections and coordinate and attribute updates. I'll do my best to describe what I am trying to do.

What I currently do: Throughout the year as this infrastructure is inspected, I update coordinates and mark them as "inspected" or "deleted". This data is in an Excel spreadsheet. Once updated, I add XY data to the ArcMap document, then convert it to a shapefile so that the map reflects the infrastructure that has been inspected and what still needs inspections. 

What I would like to do: Find a way to have the shapefile linked to a spreadsheet/table/database so that I can change the information there and have the shapefile update automatically.

I have the feeling there is a better way to do what I'm doing. Updating and creating a new shapefile every week or so seems cumbersome and I don't want to have a folder full of multiple shapefiles. Any thoughts or suggestions would be greatly appreciated!

Eric

[MitchHolley1]

I assume there are different departments that access this data?  Otherwise, you could just manage it in either GIS or Excel.  This sounds like Python could help out.  

[EricRenfer]

Yes, we share this data with other agencies from time to time. Managing the data in GIS would be updating the attribute table during an edit session?

Never used Python before so I'm not sure about that. I know just enough to be dangerous, but fall well short of being proficient with ArcMap.

[ChrisDonohue__GISP]

Is the infrastructure static over time?  Or to say it another way, are there a set number of assets you have to change and they are they the same each year? ie large numbers of assets are not being added and subtracted. 

• If things are relatively static, you could start out by doing a one-time creation of an asset feature class based on each assets spatial location.  Then create a UniqueID field in the feature class and assign unique ID's to each feature in your feature class.  In your Excel spreadsheet give each asset would have the same unique ID (you can export your feature class the first time as Excel to get this set up). Also, provide a field to record your Inspection attributes.

• If things are more dynamic, you would do the same as above but need to add additional steps in the process to deal with features that no longer exist and to deal with adding in the new spatial locations.

So after you do the updating of the Excel worksheet in the field, one could a table Join to append the Excel worksheet to the assets Feature class to transfer the latest information into GIS so the mapping can be done.

Essentials of joining tables—Help | ArcGIS for Desktop 

Note - I would suggest saving off the joined file so as to make the Join permanent, as there are a few issues that come up from time to time when trying to symbolize data off a feature class that has been joined.

Chris Donohue, GISP

[EricRenfer]

The infrastructure is dynamic in that things get replaced or removed, or we discover that we haven't finished mapping and inventorying everything that's out there. I will give the Join function a try as you indicated and see how it works for me.