Migrating to GlobalID Relationship

[KevinRobert1]

I am trying to do something and never thought I would get stuck at the relationship creation level.

We've been collecting data with Survey 123 for the past two years (Hydrant Inspection). We now want to "automate" the process so that these inspections gets automatically attached to the corresponding hydrant. My common ID is called FHID (Fire Hydrant ID) in my "survey" table and in the my Fire Hydrant Feature Class. I created a relationship class using FHID as the Primary Key. Once this is complete, I need to convert the relationship to GlobalID so I can create my future surveys based on a feature service. 

What am I missing to relate already collected data and making the Global ID Relationship so I can survey on top of this survey table in the future? 

[LynnBerni · Accepted solution]

Hi Kevin,

We had a similar issue while developing a series of surveys to populate related data tables with water quality data collected at stream SampleSites (points). Our relationship classes were originally based on the sample site location SITEID. Somewhere along the way (while developing the surveys) text fields were no longer allowed to be used for the relationships. Which was kind of a bummer, because that SITEID is meaningful as the primary key.  At any rate, we added a new guid field to the sample sites (points) called SITE_GUID, and that is what the relationship classes are now built on. We also added a SITE_GUID field to each data table.  When surveys are submitted the SITE_GUID is not populated (it's not generated by the survey). Rather, our workaround is an UpdateSiteGuid python script (runs nightly) which populates the SITE_GUIDs in the related tables based on SITEID in SampleSites point file. Essentially, the sample site points have two primary keys, SITEID and SITE_GUID. It's a little clunky, but it works!

If you haven't already seen it, this post is a must read https://community.esri.com/groups/survey123/blog/2017/09/25/working-with-existing-feature-services-i.... 

Hope this answers your question!

Lynn

[JoeBorgione]

I wrote the update script Lynn refers to above.  It actually uses a python dictionary as a 'look up' table to populate the empty GUID.  I think you'll need to first add the global ids to your fire hydrants, and then you could could cursor through the hydrant feature class and grab the Hydrant ID and the respective GlobalID into a dictionary.  I use a procedure that Richard Fairhurst came up with and discusses in /blogs/richard_fairhurst/2014/11/08/turbo-charging-data-manipulation-with-python-cursors-and-diction... .

[KevinRobert1]

Thanks a lot for your feedback Lynn / Joe,

My initial thought was to create that GUID relationship and go from there but reading your comments made me realize that field workers will keep selecting the HydrantID while surveying, so it really looks like what you did is exactly what I am trying to achieve. I am just starting to use Python scripts and let's say that programming is pet peeve. Joe, in @Richard Fairhurst article, there are multiple sections, which of these script should I start with?

Thanks again for your help!

I really appreciate it!

[JoeBorgione]

Focus on example 1. 

In your case, Site_ID = Hydrant_ID.  I update a number of tables in a loop, so you can avoid that part...

# use a dictionary to populate a field
import arcpy, time, smtplib, sys, inspect, os



arcpy.env.workspace = r'X:\path\to\your\data_source'
ws = arcpy.env.workspace
tableList = ['Table1','Table2','Table3']
tableFields = ['SITEID','SITE_GUID']
source = '{}\table\with\siteid\site_guid\records.format(ws)

#create a dirctionary of the {SITEID:SITE_GUID} from source...
guidDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(source,tableFields)}
# guidDict is in the form of:
##    {'BC_00.61': ('{94ACB91D-34DD-40F6-802D-AC092339FD5E}',),
##     'BC_01.94': ('{17A6C9CF-6FC0-4FE1-8AB7-EACBE0DB678F}',),
##     'BC_03.73': ('{B39873DF-1F5A-4AFC-978F-5BD3888CB5D9}',),
##     'BC_04.73': ('{FEE14B1D-7ED4-4D3C-9929-E1AC054F1022}',),
##     'BC_08.83': ('{05627197-5125-43E7-8402-CBF39FE6197C}',),
##     'BC_10.64': ('{A5C5EEA1-7308-4624-B193-F68761BCBF5A}',),
##     .......
##    'JR_09.79': ('{7EFEDE87-FD49-4020-9CFC-02D41D5B538C}',)}

#start an edit session  on non-versioned egdb
edit = arcpy.da.Editor(ws)
edit.startEditing(False,False) #use False False: with undo,(False) multiuser mode(False)
edit.startOperation()

#step through the tables and update
#if the record has a valid site-id and a null site_guid
#update the site_guid Both of these come from source feature class or table

for t in tableList:
    try:
        updateTable = '{}\{}'.format(ws,t)
        updateFields = ['SITEID','SITE_GUID']
    
        with arcpy.da.UpdateCursor(updateTable,updateFields) as updateRows:
            for updateRow in updateRows:
               keyValue = updateRow[0]
               if keyValue in guidDict and updateRow[1] == None:
                   updateRow[1] = guidDict[keyValue][0]
                   updateRows.updateRow(updateRow)
               else:
                    pass
    except Exception as err:
        print err‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[KevinRobert1]

Hi Joe, 

I'm debugging the script and at the "source" table defining part, I get an error message saying that the 'in_table' is not a table or a featureclass...

My table is a feature class inside a GDB (for testing for now), and I've defined it in the workspace at the begining...

# use a dictionary to populate a field

import arcpy, time, smtplib, sys, inspect, os
arcpy.env.workspace = r"Y:\Working\TravauxPublics\Branch_Line_Flushing\Script\Hydrants.gdb"
ws = arcpy.env.workspace
tableList = ["Hydrants_1","Hydrants_Branchline_Survey"]
tableFields = ["FHID","GlobalID_1"]
source = {"Hydrants_1"}

#create a dictionary of the {SITEID:SITE_GUID} from source...
guidDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(source,tableFields)}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[JoeBorgione]

Take the curly brackets in line #8. If I gave them to you, my bad!  Also, not sure why 'Hydrants_1' is part of your table list.  Table list should be the table(s) you are updating.

[SharonUngerer_DNREC]

I got this to work once to populate a Survey_Loc_ID field using GUID.   However now I am trying to rebuild the relationship using the Global ID from a parent point layer for the survey locations.  It's not working. Any tips? 

import arcpy, time, smtplib, sys, inspect, os
arcpy.env.workspace = r'\\enterprise_gdb.sde'
ws = arcpy.env.workspace
tableList = ['DNREC_OWNER.ENV_ConservationTillage_Visits_v3']
tableFields = ['SURVEY_LOC_ID','GLOBALID']
source = 'DNREC_OWNER.ENV_ConservationTillage_v3'
GlobalID_dict = {r[0]:(r[1]) for r in arcpy.da.SearchCursor(source,tableFields)}
edit = arcpy.da.Editor(ws)
edit.startEditing(False,False) #use False False: with undo,(False) multiuser mode(False)
edit.startOperation()
for t in tableList:
    try:
        updateTable = '{}\{}'.format(ws,t)
        updateFields = ['SURVEY_LOC_ID','GLOBALID']
    
        with arcpy.da.UpdateCursor(updateTable,updateFields) as updateRows:
            for updateRow in updateRows:
               keyValue = updateRow[0]
               if keyValue in GlobalID_dict and updateRow[1] == None:
                   updateRow[1] = GlobalID_dict[keyValue][0]
                   updateRows.updateRow(updateRow)
               else:
                    pass
    except Exception as err:
        print ('whoopsy daisy ... error updating')

 I am wondering if its because the field in the table is 'PARENT_GLOBALID' and the field in the point layer is 'GLOBAL_ID'.