AnsweredAssumed Answered

Adding Labels Using Related Data

Question asked by jadavis101699 on Oct 13, 2020
Latest reply on Oct 21, 2020 by jadavis101699



I've tried to utilize the code I found posted by Richard Fairhurst that allows you to create labels based on data contained within a related table to no avail.  I keep getting the veritable "No features found. Could not verify expression".  Everyone's favorite error message.  BTW...a big shout out to Richard for posting this code and doing all he can to help others with this quest.


I am attempting to add the information contained under the Table field "Name" to individual polygons.


Any insight on what I am doing wrong would be most appreciated.


Thank you.




Here is the logistics of my relationship class setup:

Origin Table: DBOPlotOccJoin

Primary Key: UserField4

Foreign Key: GIS_ID

Destination Table: BSACemeteriesCopy

Table Label Field: Name


Here is Richard's python code that I have augmented:

# Initialize a global dictionary for a related feature class/table

relateDict = {}

def FindLabel ( [UserField4] ):

  # declare the dictionary global so it can be built once and used for all labels

  global relateDict

  # only populate the dictionary if it has no keys

  if len(relateDict) == 0:

    # Provide the path and table name to the relate feature class/table

    relateFC = r"R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb\DBOPlotOccJoin"

    # create a field list with the relate field first (POLYID),

    # followed by sort field(s) (SCHOOLID), then label field(s) (CROSS_STREET)

    relateFieldsList = ["GIS_ID", "Name"]   

# process a da search cursor to transfer the data to the dictionary

    with arcpy.da.SearchCursor(relateFC, relateFieldsList) as relateRows:

      for relateRow in relateRows:

        # store the key value in a variable so the relate value

        # is only read from the row once, improving speed

        relateKey = relateRow[0]

        # if the relate key of the current row isn't found

        # create the key and make it's value a list of a list of field values

        if not relateKey in relateDict:

          # [searchRow[1:]] is a list containing

          # a list of the field values after the key.

          relateDict[relateKey] = [relateRow[1:]]


          # if the relate key is already in the dictionary

          # append the next list of field values to the

          # existing list associated with the key


    # delete the cursor, and row to make sure all locks release

    del relateRows, relateRow

  # store the current label feature's relate key field value

  # so that it is only read once, improving speed

  labelKey = [UserField4]

  # start building a label expression.

  # My label has a bold key value header in a larger font

  expression = '<FNT name="Arial" size="12"><BOL>{}</BOL></FNT>'.format(labelKey)

  # determine if the label key is in the dictionary

  if labelKey in relateDict:

    # sort the list of the list of fields

    sortedList = sorted(relateDict[labelKey])

    # add a record count to the label header in bold regular font

    expression += '\n<FNT name="Arial" size="10"><BOL>School Count = {}</BOL></FNT>'.format(len(sortedList))

    # process the sorted list

    for fieldValues in sortedList:

       # append related data to the label expression

       expression += '\n{0} - {1} - {2} - {3}'.format(fieldValues[0], fieldValues[1], fieldValues[2], fieldValues[3])

    # clean up the list variables after completing the for loop

    del sortedList, fieldValues


    expression += '\n<FNT name="Arial" size="10"><BOL>School Count = 0</BOL></FNT>'

  # return the label expression to display

  return expression