# Migrating to GlobalID Relationship

563
6
Jump to solution
02-05-2019 10:39 AM
Highlighted
New Contributor III

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?

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Occasional Contributor II

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

6 Replies
Highlighted
Occasional Contributor II

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

Highlighted
MVP Esteemed Contributor

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... .

Highlighted
New Contributor III

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!

Highlighted
MVP Esteemed Contributor

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 fieldimport arcpy, time, smtplib, sys, inspect, osarcpy.env.workspace = r'X:\path\to\your\data_source'ws = arcpy.env.workspacetableList = ['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 egdbedit = 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 tablefor 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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
Highlighted
New Contributor III

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 fieldimport arcpy, time, smtplib, sys, inspect, osarcpy.env.workspace = r"Y:\Working\TravauxPublics\Branch_Line_Flushing\Script\Hydrants.gdb"ws = arcpy.env.workspacetableList = ["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)}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
Highlighted
MVP Esteemed Contributor

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.