Adding Labels Using Related Data

3377
39
Jump to solution
10-13-2020 10:54 AM
JeffDavis7
Occasional Contributor

Hello,

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.

Jeff

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:]]

        else:

          # if the relate key is already in the dictionary

          # append the next list of field values to the

          # existing list associated with the key

          relateDict[relateKey].append(relateRow[1:])

    # 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

  else:

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

  # return the label expression to display

  return expression

0 Kudos
1 Solution

Accepted Solutions
RandyBurton
MVP Regular Contributor

Thanks for the screen capture.  I am going to make one assumption: the field "Name" is in the "DBOPlotOccJoin" table.  If that is the case, then here is a simplified example of the labels and data I used for my testing.

Labels

The following code gets pasted in the expression section of the Label Expression window.  Be sure to check 'Advanced' and select Python as the 'Parser' before pasting.

# Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [GIS_ID] ):
  # 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 (GIS_ID),
    # followed by sort field(s) (Name), then label field(s) (...)
    relateFieldsList = ["UserField4", "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:]]
        else:
          # if the relate key is already in the dictionary
          # append the next list of field values to the
          # existing list associated with the key
          relateDict[relateKey].append(relateRow[1:])
    # 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 = [GIS_ID]
  # 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>Name 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])
       expression += '\n{0}'.format(fieldValues[0])
    # clean up the list variables after completing the for loop
    del sortedList, fieldValues
  else:
    # expression += '\n<FNT name="Arial" size="10"><BOL>Name Count = 0</BOL></FNT>'
    pass
  # return the label expression to display
  return expression‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Line 3 is the field in the feature that we are using for labeling and linking to the related table: GIS_ID.

Line 12 contains the linking field in the related table along with the related data used for the labels: UserField4 and Name

Line 41 is sorting the dictionary values, in this case the "Names".  You may wish to comment this line out if you do not want the names sorted.

I commented out line 43 as this adds a line to the label indicating how many names are associated with the feature.

Line 47 was also commented out, as there is only one field value being used, in this case "Name".

The else block, lines 51-53, was left in, but the name count is not added to the label.

Hope this helps.

View solution in original post

39 Replies
RandyBurton
MVP Regular Contributor

I noticed that your origin table may be a join, so it is possible that the field names have been modified because of the join.  I would also confirm that UserField4 and GIS_ID have some matching values.  Are you using Desktop or Pro?

Since this is based on Richard Fairhurst‌'s code, I'll tag him into the conversation.

0 Kudos
JeffDavis7
Occasional Contributor

Thank you Randy for responding to my post.  I really appreciate your time.

Yes.  That is correct.  The origin table is based on a join between two tables; one representing Plots while the other is Occupants.  The field names both before and after the join have remained consistent and yes, UserField4 and GIS_ID have matching values.  I am using Desktop.

0 Kudos
RandyBurton
MVP Regular Contributor

I'm still confused with your tables.  Is 'DBOPlotOccJoin' the related table?  And, is 'BSACemeteriesCopy' the feature layer you are symbolizing?  Perhaps a few sample rows of the feature layer and the related table would clear up my confusion.

If you have already joined the two tables into one that you are symbolizing, you won't need the steps in Richard's code.  You would just pass multiple fields to the 'FindLabel' function and add some formatting.  Again, some sample rows would be helpful; it can be made-up data as it is the structure that I'm trying to understand.

0 Kudos
JeffDavis7
Occasional Contributor

That is correct...DBOPlotOccJoin is the file geodatabase table and BSACemeteriesCopy is the feature layer being used for symbolizing.  I've attached a screen capture depicting both of these tables with the UserField4 and the GIS_ID fields present.  You can see the "many" in the DBOPlotOccJoin to the "one" in BSACemeteriesCopy in the capture.

0 Kudos
RandyBurton
MVP Regular Contributor

Thanks for the screen capture.  I am going to make one assumption: the field "Name" is in the "DBOPlotOccJoin" table.  If that is the case, then here is a simplified example of the labels and data I used for my testing.

Labels

The following code gets pasted in the expression section of the Label Expression window.  Be sure to check 'Advanced' and select Python as the 'Parser' before pasting.

# Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [GIS_ID] ):
  # 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 (GIS_ID),
    # followed by sort field(s) (Name), then label field(s) (...)
    relateFieldsList = ["UserField4", "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:]]
        else:
          # if the relate key is already in the dictionary
          # append the next list of field values to the
          # existing list associated with the key
          relateDict[relateKey].append(relateRow[1:])
    # 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 = [GIS_ID]
  # 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>Name 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])
       expression += '\n{0}'.format(fieldValues[0])
    # clean up the list variables after completing the for loop
    del sortedList, fieldValues
  else:
    # expression += '\n<FNT name="Arial" size="10"><BOL>Name Count = 0</BOL></FNT>'
    pass
  # return the label expression to display
  return expression‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Line 3 is the field in the feature that we are using for labeling and linking to the related table: GIS_ID.

Line 12 contains the linking field in the related table along with the related data used for the labels: UserField4 and Name

Line 41 is sorting the dictionary values, in this case the "Names".  You may wish to comment this line out if you do not want the names sorted.

I commented out line 43 as this adds a line to the label indicating how many names are associated with the feature.

Line 47 was also commented out, as there is only one field value being used, in this case "Name".

The else block, lines 51-53, was left in, but the name count is not added to the label.

Hope this helps.

JeffDavis7
Occasional Contributor

First of all...the code works beautifully!!  The display is exactly what I was looking for.  Thank you so much, Randy for taking the time and assisting me.  I really appreciate it!

Second...as a follow-up question...should there be any issues with the labels coming up in AGO?  Since the labels look so great I decided to explore creating a custom application for my internal clients.  Unfortunately, when I publish using the same Desktop project file that shows the labels perfectly, no labels appear within the application window.  I realize this is not an AGO forum, but have you run into any issues with labels disappearing in AGO when using this label workflow?

0 Kudos
HeatherScroggins
Occasional Contributor

I know this is a bit old at this point but I have a question about this code.

First off, thank you so much for posting it. It works beautifully most of the time. However, every now and then I get something like this happening:

HeatherScroggins_0-1619555915385.png

This point only has 6 things related to it all of which are on the first two lines:

HeatherScroggins_1-1619556060967.png

You can see the related features in the table here:

HeatherScroggins_2-1619556111317.png

It's like the label is repeating features for some reason. Is there a way I can fix this?

I used your code exactly as it is above, just changed field names and table location.

 

0 Kudos
HeatherScroggins
Occasional Contributor

I figured it out!

Right before the expression is returned I added the code:

expression = expression.replace("&", "&amp;")

 

And that fixed the issue of ArcMap not liking an actual "&" in the text string.

0 Kudos
RandyBurton
MVP Regular Contributor

Glad to help.  Regarding your follow-up question, I would not expect this procedure to work on AGO.  I suggest asking the question "can Arcade create labels using data from related tables" in the ArcGIS Online forum.  Hopefully Xander Bakker‌ can provide an answer for you.