Latest Contributions by DavinShokes1

The last try at line 230 is where it is succeeding the REST call, but the layer's field values stay null. A few lines prior, updateRow[1] gets an integer from the dictionary CustomerIDDict.                       # Set Null tag to first available integer              updateRow[1] = templist[0]  Then updateRow[1] gets put into...               print fl.calculate(where="OBJECTID={} AND CustomerID={}".format(updateRow[3], updateRow[0]), calcExpression={"field" : "TAG", "value" : "{}".format(updateRow[1]),"field" : "PropertyID_WO", "value" : "{}".format(updateRow[2])})  I'm guessing it turns into: calcExpression={"field" : "TAG", "value" : "4",....},but I'm not certain. Also, at line  97 added a decode and a typeerror went away. I can't tell if this decode causes issues in the dictionary later on though.              # Build a composite key value from 2 fields: CustomerID;TAG               keyValue = '{};{}'.format(searchRow[0], searchRow[1]).decode('utf-8') ... View more

I've made some headway. My script is successfully insertings new rows into sql server with pyodbc. However, I've got an encoding issue when updating the hosted layer. The sql table varchar columns and I assume that hosted layers are nvarchar. The call to update the hosted layer succeeds but the text column doesn't actually update. Integer fields do. Here is my code I've kept the actual column names so the SQL Server table fields are: SiteID-->CustomerID, FeatureID-->PropertyID, and EasyID--> Location The hosted layer fields are: SiteID-->CustomerID, FeatureID-->PropertyID_WO, and EasyID--> TAG I've match the sequence to prevent name issues. I'm don't know how to go further without figuring out how to update TAG with Location. # -*- coding: utf-8 -*- # --------------------------------------------------------------------------- # import sys, os, datetime, arcpy, pyodbc, time, uuid def hms_string(sec_elapsed):     h = int(sec_elapsed / (60 * 60))     m = int((sec_elapsed % (60 * 60)) / 60)     s = sec_elapsed % 60.     return "{}:{:>02}:{:>05.2f}".format(h, m, s) # End hms_string def timedif(end_datetime, start_datetime):     seconds_elapsed = (end_datetime - start_datetime).total_seconds()     return hms_string(seconds_elapsed) # End timedif start_time = datetime.datetime.now() print "# Script start: {}".format(start_time) from arcpy import env, da from arcrest.security import AGOLTokenSecurityHandler from arcrest.agol import FeatureService from arcrest.agol import FeatureLayer from arcrest.common.filters import LayerDefinitionFilter from datetime import timedelta print "# Imports loaded." # Set Geoprocessing environments Scratch_gdb = r'C:\Python\scratch.gdb' arcpy.env.scratchWorkspace = Scratch_gdb arcpy.env.workspace = Scratch_gdb Local_Table = r'C:\Users\dkshokes\AppData\Roaming\ESRI\Desktop10.3\ArcCatalog\RTC03.sde\Rainbow.dbo.Property' Output_Table_1 = "T1" Expression_1 = r'"CustomerID" = 166602 AND "Location" is not Null' Online_Trees = r'http://services.arcgis.com/###/arcgis/rest/services/###/FeatureServer/0' username = "####" password = "####" proxy_port = None proxy_url = None agolSH = AGOLTokenSecurityHandler(username=username, password=password) Output_Table_2 = "T2" Expression_2 = '"CustomerID" = 166602' fields_1 = ['CustomerID', 'Location', 'PropertyID', 'OID@', 'Description', 'CBH', 'PropertyQuantity', 'PropertyItemID', 'DateEntered', 'LastChangeDate', 'Special_Note'] fields_2 = ['CustomerID', 'TAG', 'PropertyID_WO', 'OID@', 'COMMON', 'DBH', 'QTY', 'PropertyItemID', 'CreationDate', 'EditDate', 'Notes', 'Creator', 'Editor', 'PruneYear', 'ProposalStatus', 'SoilYear'] print "\n# Online variables loaded.  " # Local variables: T1 = Scratch_gdb + "\\" + Output_Table_1 print "# T1 Table: {}".format(T1) print "# T1 Fields: {}".format(fields_1) T2 = Scratch_gdb + "\\" + Output_Table_2 print "# T2 Table: {}".format(T2) print "# T2 Fields: {}".format(fields_2) FieldMap_1 = r"PropertyID \"PropertyID\" true true false 50 Long 0 10 ,First,#," + Local_Table + ",PropertyID,-1,-1;CustomerID \"CustomerID\" true true false 4 Long 0 10 ,First,#," + Local_Table + ",CustomerID,-1,-1;Location \"Location\" true true false 7 Text 0 0 ,First,#," + Local_Table + ",Location,-1,-1;PropertyItemID \"PropertyItemID\" true true false 4 Long 0 10 ,First,#," + Local_Table + ",PropertyItemID,-1,-1;Description \"Description\" true true false 255 Text 0 0 ,First,#," + Local_Table + ",Description,-1,-1;PropertyQuantity \"PropertyQuantity\" true true false 4 Long 0 10 ,First,#," + Local_Table + ",PropertyQuantity,-1,-1;CBH \"CBH\" true true false 8 Double 1 18 ,First,#," + Local_Table + ",CBH,-1,-1;Special_Note \"Special_Note\" true true false 255 Text 0 0 ,First,#," + Local_Table + ",Special_Note,-1,-1;Status \"Status\" true true false 10 Text 0 0 ,First,#," + Local_Table + ",Status,-1,-1;DateEntered \"DateEntered\" true true false 36 Date 0 0 ,First,#," + Local_Table + ",DateEntered,-1,-1;PropertyGroup \"PropertyGroup\" true true false 2 Short 0 1 ,First,#," + Local_Table + ",PropertyGroup,-1,-1;Removed \"Removed\" true true false 2 Short 0 1 ,First,#," + Local_Table + ",Removed,-1,-1;LastChangeDate \"LastChangeDate\" true true false 36 Date 0 0 ,First,#," + Local_Table + ",LastChangeDate,-1,-1" if arcpy.Exists(T1):     arcpy.Delete_management(T1)     print "Previous T1 Found so it was deleted." if arcpy.Exists(T2):     arcpy.Delete_management(T2)     print "Previous T2 Found so it was deleted." #print "\n# Local variables loaded...Making temporary business table." arcpy.TableToTable_conversion(Local_Table, Scratch_gdb, Output_Table_1, Expression_1, FieldMap_1, "") #print "# Successfully made temporary business table." #print "\n# Querying Feature Layer and Making Temporary Feature Class." fl = FeatureLayer(Online_Trees, securityHandler=agolSH, initialize=False,proxy_url=proxy_url, proxy_port=proxy_port) print fl.query(where=Expression_2, out_fields='*', returnGeometry=True, returnIDsOnly=False, returnCountOnly=False, returnFeatureClass=True, out_fc=Output_Table_2) print "\n# Ready to compare." + "  Time elapsed: {}".format(timedif(datetime.datetime.now(), start_time)) print "\n# Intialize T1 as a concatenated dictionary" T1Dict = {} # Initialize a list to hold any concatenated key duplicates found T1KeyDups = [] # Open a search cursor and iterate rows with arcpy.da.SearchCursor(T1, fields_1) as searchRows:     for searchRow in searchRows:         # Build a composite key value from 2 fields: CustomerID;TAG         keyValue = '{};{}'.format(searchRow[0], searchRow[1]).decode('utf-8')         if not keyValue in T1Dict and searchRow[1] is not None:             # Key not in dictionary.  Add Key pointing to a list of a list of field values             T1Dict[keyValue] = [list(searchRow[0:])]             #print "T1Dict keyValue: {}".format(keyValue)         elif searchRow[1] is not None:             # Key in dictionary.  Append a list of field values to the list the Key points to             T1KeyDups.append(keyValue)             T1Dict[keyValue].append(list(searchRow[0:]))         #print T1Dict[keyValue][0:] del searchRows, searchRow ##print "\n# Sample of how to access the keys, record count, and record values of the dictionary" ##for keyValue in T1Dict.keys(): ##    for i in range(0, len(T1Dict[keyValue])): ##        print "T1Dict Key CustomerID;TAG is {} Record:{} of {} PropertyID_WO:{} ObjectID:{}.".format(keyValue, i+1, len(T1Dict[keyValue]), T1Dict[keyValue][0], T1Dict[keyValue][1]) if len(T1KeyDups) > 0:     print "T1KeyDups keys found! {}".format(T1KeyDups)     # fix T1 here else:     print ("No Duplicates!") print "\n# T1Dict Complete:" + "  Time elapsed: {}".format(timedif(datetime.datetime.now(), start_time)) print "\n# Get the list of TAGs associated with each CustomerID in a dictionary for T1" T1CustomerIDDict = {} with arcpy.da.SearchCursor(T1, fields_1) as searchRows:     for searchRow in searchRows:         keyValue = searchRow[0]         if not keyValue in T1CustomerIDDict:             # Key not in dictionary.  Add Key pointing to a list of a list of field values             T1CustomerIDDict[keyValue] = [searchRow[0:]]                    else:             # Append a list of field values to the list the Key points to             T1CustomerIDDict[keyValue].append(searchRow[0:])         #print T1CustomerIDDict[keyValue] del searchRows, searchRow print "\n# T1CustomerIDDict Complete" print "\n# Get the list of TAGs associated with each CustomerID in a dictionary for T2" T2CustomerIDDict = {} with arcpy.da.SearchCursor(T2, fields_2) as searchRows:     for searchRow in searchRows:         keyValue = searchRow[0]         if not keyValue in T2CustomerIDDict:             T2CustomerIDDict[keyValue] = [searchRow[0:]]         else:             T2CustomerIDDict[keyValue].append(searchRow[0:]) del searchRows, searchRow print "\n# T2CustomerIDDict Complete" print "\n# Creating CustomerIDDict" CustomerIDDict = {} for keyValue in T2CustomerIDDict.keys():     intList = []     for TAG in T2CustomerIDDict[keyValue]:         try:             if TAG[1]:                 TAG = int(TAG[1])                 intList.append(TAG)         except:             print "Error with T2.TAG"     if keyValue in T1CustomerIDDict:         for TAG in T1CustomerIDDict[keyValue]:             try:                 if TAG[1]:                     TAG = int(TAG[1])                     intList.append(TAG)             except:                 print "Error with T1.TAG"              #print "\nnumeric T2.TAGs: {}".format(set(intList))     #print "\nnumeric T1.TAGs: {}".format(set(intList))     # remove already used numbers out of the numbers from 1 to 9999     # and get a sorted list for use with an update cursor.     CustomerIDDict[keyValue] = sorted(set(range(1, 200)) - set(intList)) print "\n# CustomerIDDict Complete" print "\n# Inserting and Updating" + "  Time elapsed: {}".format(timedif(datetime.datetime.now(), start_time)) ### This cursor sucessfully inserts into sql server a new row with an unused TAG and attributes from the hosted feature. ### It then updates the hosted layer fields propertyID_WO and TAG. ##  propertyID_WO succeeds but TAG is blank # print "#  Working with pyodbc sql stuff" cnxn_SQL = pyodbc.connect(Trusted_Connection='yes', Driver = '{SQL Server Native Client 10.0}', Server = '###', database = '###') cursor_SQL = cnxn_SQL.cursor() with arcpy.da.UpdateCursor(T2, fields_2) as updateRows:      for updateRow in updateRows:                    # if T2.Row has TAG and CustomerID but no PropertyID compare and look for match in T1         if (updateRow[2] == None) and not (updateRow[1] == None):             print "1st if TAG {} PropertID {}".format(updateRow[1],updateRow[2])             for valu in T1CustomerIDDict[updateRow[0]]:                 if valu[1] == updateRow[1]:                     try:                         updateRow[2] = valu[2]                         print "# Updating PropertyID_WO for TAG:{} hosted layer with {}".format(updateRow[1],updateRow[2])                         fl.calculate(where="OBJECTID={} AND CustomerID={} AND TAG={}".format(updateRow[3], updateRow[0], updateRow[1]), calcExpression={"field" : "PropertyID_WO", "value" : "{}".format(updateRow[2])})                         print "# Success"                     except:                         print "# Failed Hosted Update of PropertyID"         if (updateRow[1] == None) and (updateRow[2] == None):             print "3rd if TAG {} PropertID {}".format(updateRow[1],updateRow[2])             newPropID = None             print CustomerIDDict[updateRow[0]][0]             # Set templist to show available integer TAGs for 1 CustomerID at a time.             templist = CustomerIDDict[updateRow[0]]             # Set Null tag to first available integer             print updateRow[1:3]             updateRow[1] = templist[0]             # Insert to local sql server table, update online from sql results, then Remove used tag.             print updateRow[1:3]             try:                 # Get ItemID required by insert trigger                 propitemid = cursor_SQL.execute("SELECT [PropertyItemID] from [TREESPECIESRELATIONSHIPTABLE] Where [COMMON__GIS_name_] = ?",updateRow[4]).fetchone()[0]                 # Insert New trees into T1                 cursor_SQL.execute("IF NOT EXISTS (SELECT 1 FROM Property WHERE CustomerID = ? and Location = ?) INSERT INTO Property(CustomerID, Location, Description, CBH, PropertyQuantity, PropertyItemID, DateEntered, LastChangeDate, Special_Note) values (?,?,?,?,?,?,?,?,?)", updateRow[0], updateRow[0], updateRow[0],updateRow[1],updateRow[4],updateRow[5],updateRow[6],propitemid,updateRow[8],updateRow[9],updateRow[10])                 cnxn_SQL.commit()                 # Grab to primary key for updating hosted layer                 newPropID = cursor_SQL.execute("Select PropertyID From Property where CustomerID=? AND Location=?", updateRow[0], updateRow[1]).fetchone()[0]                 print "Finished Insert for New PropertyID {}".format(newPropID)                 cnxn_SQL.commit()                 updateRow[2] = newPropID                 templist.remove(updateRow[1])                 print updateRow[1:3]             except:                 print "# Failed SQL Server Insert"             try:                 print "# Updating TAG and PropertyID_WO for hosted layer"                 print fl.calculate(where="OBJECTID={} AND CustomerID={}".format(updateRow[3], updateRow[0]), calcExpression={"field" : "TAG", "value" : "{}".format(updateRow[1]),"field" : "PropertyID_WO", "value" : "{}".format(updateRow[2])})             except:                 print "# Failed Hosted Update of TAG and PropertyID"         updateRows.updateRow(updateRow) del updateRows, updateRow cnxn_SQL.close()         # ## ### ### print "Finished!" + "  Time elapsed: {}".format(timedif(datetime.datetime.now(), start_time)) ... View more

     Wow thank you for these excellent resources! I will try to digest this over the weekend, but this looks very promising. Sorry that I didn't provide all of the aspects of this dilemma. I tried to simplify it enough that python masters like yourself wouldn't have to read a novel. As you ascertained, this is complex.      The business table (T1) sits on a 2005 SQL server, unsupported since 10.2. EasyID is a  7 char string used for labeling at a site and it is the syncing bane of my existence. Users are allowed to assign "C5-C8" so that FeatureID represents numerous real world things. C5, C6, C7, and C8 could also exist in T1 individually. It is a data quality mess. To combat this the groupings will not make it to T2. Instead C5, C6, C7, and/or C8 will be digitized by the T2 user, if so desired.      Luckily, users are unable to delete rows and can't view or change the FeatureID or SiteID. The other table (T2) and a copy of the Sites table are layers in a hosted service for collecting new site features.  I update the Sites with a scheduled script and thanks to Collector for ArcGIS 10.3 honoring relationships, all features maintain their SiteID.      In short, users are limited to picking an EasyID during feature creation in either table and they could possibly update EasyID on T2. On the off chance they do change EasyID on a feature existing in T1 and T2 there will be a script that joins by FeatureID and updates the one with an earlier edit date.      I could go on and on, but don't want to spoil your weekend. Thanks again Richard Fairhurst‌! ... View more

Richard, I haven't gotten far enough to get any errors. I don't know how to build/filter the dictionary for T2.OID T2.SiteID string EasyID, then gather only integer EasyIDs, and return the 1st missing integer starting from 1. Without it I can't test updating Null EasyIDs in new T2 features. Thank you for posting that link to stackexchange! It looks very similar the components of my scenario. I will update after testing the code. ... View more

This script will run at night. The T1 features that don't have a Site ID in T2 are basically ignored until the T2 user adds the first feature with that SiteID. The user may have manually matched the SiteID and EasyID before the script runs. Additionally, T2 users adding the first occurances of features in either table will skip adding EasyID and rely on unique ones being generated. One other note is once a feature has all 3 ID'S it will never be changed by the users. Perhaps if I pre filter T1 to T2 SiteID, the script would be simpler. I believe you have identified step 1 as redundant since it occurs later and max(EasyID) is gained across both tables. ... View more

Good points Xander Bakker‌. I have included example info and fixed the mentioning. Thanks. ... View more

Give the layer a filter in the webmap feeding the Geoform. It can be anything like ObjectID = 0 so the no features show after form submission. If you want them to see their Dot after submitting you can try Filter by CreationDate = CURRENT_DATE(), but the user will see any from same time frame. ... View more

Looks like javascript table editing is creeping into the realm of possibility with FeatureTables In beta now but readOnly:false will be great. I played around with the dgrid and its still a little sketchy with large tables. Someday. ... View more

I would imagine that this is difficult to reproduce  because the feature service was originally published in 2013. I cannot  put out a new version until field staff complete their work this week. As a side effect, the 1400 photo attachments are currently eating 90 credits a day in feature storage fees. Chomp chomp. ... View more

Well I solved it and this is indeed a bug. The feature service in question was republished and was given a new field for GlobalIDs. So it has a string field named "GlobalID" and an esriFieldtypeGlobalID field named "GlobalID_2". Copying features also copies the value that should be unique! This is why the first feature that is copied has the photo attachments of future features. Perhaps other sync issues are related to this failure to generate unique IDs. Be careful what your users copy! Edit: I read something interesting about copying with relationships: http://resources.arcgis.com/en/help/main/10.2/index.html#/Relationship_class_properties/004t00000004000000/ When you split a feature, the original feature is maintained (with updated geometry), and a new feature is created. If you have a relationship based on the original ObjectID, only one of the two features created in the split will maintain the relationship. However, if you used another field as the key, when you split the feature, the ID value of the original feature would be copied to the two new features. As a result, the records in the related table would now be related to both new features—ideal if the relationship class is set up as many-to-many.If you won't be splitting features and are sure that all objects will remain in their original class, you can use the ObjectID as their IDs. If you can't guarantee this, it's best to set up and use your own ID field instead of relying on the ObjectID field. ... View more

Thanks Robert, I had seen a tool on github a while back and didn't know if someone had made a widget similar to it. Ideally editing would be standard functionality built in like Flex Viewer‌. ... View more