Overwriting joined table does not update attributes in feature Layer

[TravisAndersonPE]

Can someone please answer this? I have a Web Feature Layer on AGOL that contains points representing water meters. I also have a Table in Arc Pro that contains the current customer information for this month. The Web Layer and the Table have a common field called AccountNumber. I upload the Table to the same folder as the Web Layer. I open the map that contains the Web Layer and added the Table to the map. I then navigated to the Join Tool in Analytics, selected the Web Layer as the Target, and Table as the Join table. It completes successfully and creates a new Web Layer. Clicking on the point gives me all the fields from the original Web Layer, along with the fields from the Table. 

Next month I will have a new table. To make life simple, I would like to Overwrite the Table contained on AGOL by using Share, Overwrite Table in Arc Pro. However, it appears that changing an attribute in the Table on AGOL has no affect on the Joined Web Layer so Overwriting will do me no good. I have tried several different ways to update this web layer without going through many steps, but I hit a road block every time.  

The Table actually originates from a connection to a OLE DB. I export that database table to a Table and then push that to AGOL. I can also pull the OLE DB table over into the Table of Contents and I can create a local feature layer that updates automatically. It works great, but doesn't do me any good not being online. And it won't let me push that feature layer to AGOL because of something to do with the database being an old version. I wondered if I could create a "pipeline" to the table as some kind of workaround, but I haven't had a chance to look into it yet.

Much appreciated.

Travis

[jcarlson]

If the initial data source is a database, I would consider using a Python script to read a SQL query into a Pandas DataFrame. The ArcGIS Python API can take a DataFrame and submit it to a Feature Layer / Table as an edit.

import arcgis
import pandas as pd

gis = arcgis.gis.GIS('your portal url')

table = gis.content.get('table itemid').tables[0]

sql = """
select something
from somewhere
where condition
"""

df = pd.read_sql(sql, 'db connection string')

# reshape table as needed

fs = df.spatial.to_featureset()

# empty table
table.management.truncate()

# load new data
table.edit_features(adds=fs)

[TravisAndersonPE]

I appreciate the reply Josh. What you are suggesting sounds great. However, I don't really know anything about writing scripts, even though you've already penned it above. That's not in my wheelhouse. Let me try and digest what you wrote. I don't think my two college courses in programing in C are going to help much. 

Thanks again.

[TravisAndersonPE]

Josh,

If the OLE DB connection exists in Arc Pro, can I do as you are suggesting from Arc Pro and overwrite all the matching fields in an existing Web Layer on ArcGIS Online? I think that's what you are saying. Simply, I have a feature layer on ArcGIS Online, and want to replace the old attribute data with new attribute data from the OLE DB connection. It has proven to be difficult with my limited abilities. 

Thanks Josh.

Travis

[jcarlson]

Essentially, yes. The web layer contains a copy of the DB data, but is not directly linked to it, so I would run a script that overwrites the values. It can be quite complicated, but by leaving the existing layer and its schema in place, you avoid errors in overwriting.