Select to view content in your preferred language

Creating Labels with Related Table Data

18595
48
02-06-2015 08:15 PM
RichardFairhurst
MVP Honored Contributor
14 48 18.6K
Performance Issue of Past Solutions

The subject of creating labels that include data from related feature classes/tables in a One-To-Many or Many-To-Many relationship has come up many times over the years, and while there have been a few solutions proposed, all have suffered from poor performance.  However, I have discovered a way to overcome the performance issues after further experimentation with the techniques I described in my Turbo Charging Data Manipulation with Python Cursors and Dictionaries.

Previous solutions were slow, because they kept processing queries for each label being generated by building an SQL expression from the relate value of each feature being labeled to return the set of related records one label at a time from the related feature class/table.  This is an extremely inefficient and slow way to process look-ups against a relate.

Solving Performance Issues by Using Global Dictionaries for Related Data

Dictionaries are the perfect solution for handling relate look-ups.  The reason is that the insert/delete/look-up time of items in the dictionary is amortized constant time - O(1) - which means no matter how big the dictionary gets, the time it takes to find something remains relatively constant. This is highly desirable for high-speed look-ups.  Therefore it is much more efficient to read an entire related feature class/table into a dictionary and process the related value of each label against a dictionary key value than it is to repeatedly process SQL queries against the related feature class/table for each label relationship.

However, a dictionary would also be no good if the entire related feature class/table had to be reloaded into the dictionary as each label was being processed.  Fortunately there is no need to do that, and the entire related feature class/table can be loaded into the dictionary once when the first label is created.  To do that the dictionary is created as a global variable of the label function that is only loaded by the first label.  All other labels just check to see if the dictionary has already been loaded.  If it has, the related feature class/table will not be queried again.  As long as the labels are being generated, all subsequent labels will just use the already loaded global dictionary to get the related feature class/table data.

Each time the map is refreshed, the global dictionary is rebuilt for just the first label again and then that dictionary is used until all of the labels are refreshed.  Therefore, edits to the related feature class/table will be reflected each time the labels are refreshed.  Editing a related feature class will cause the map to refresh as edits are posted.  However, editing a related standalone table will not cause the map to automatically refresh the labels as each edit is posted.  You will have to manually refresh the map to see the standalone table updates.  However, I consider that a good thing, since waiting for map refreshes after editing one record at a time is very time consuming and there is no need to refresh the labels at all if the related standalone table is only edited in fields that do not participate in the label expression.

Example 1:  The Fundamental Code to Create Labels Using this Technique

The labels shown in this picture were created by the code below.  The related intersection event table used to create these labels contains over 130,000 records, all of which are read into the dictionary when the first label is processed.  These labels took less than 9 seconds to draw.

Labels_with_1_To_M_Relate.png

To use the code below I went to the Label tab of the Routes and checked the Label check box.  Then I pressed the Expression button for the labels, I changed the Label Parser from VBScript to Python.  After checking the Advanced expression option, I placed the code below in the expression editor.  This expression creates a label for my layer of linear referenced Routes shown above and includes data from a related table of intersection linear reference events.  It lists the Route ID (ROUTE_NAME) in the header of the label in 12 point Bold font.  Under that I show a summary count value of the number of intersection cross street names contained in the related table in 10 point Bold font.  Then I list the related cross street names and their event measure value for each intersection in the regular 8 point font.  The list of cross streets is sorted as a list based on the measures so that they appear in driving order going in the direction of the route's measure orientation.

# Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [ROUTE_NAME] ):
  # 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 to the relate feature class/table
    relateFC = r"C:\Users\OWNER\Documents\ArcGIS\Centerline_Edit.gdb\CL_INTERSECTIONS_PAIRS"
    # create a field list with the relate field first (ROUTE_NAME), 
    # followed by sort field(s) (MEASURE), then label field(s) (CROSS_STREET)
    relateFieldsList = ["ROUTE_NAME", "MEASURE", "CROSS_STREET"]
    # 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 = [ROUTE_NAME]
  # 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>Cross Street Count = {}</BOL></FNT>'.format(len(sortedList))
    # process the sorted list
    for fieldValues in sortedList:
       # append related data to the label expression
       # my label shows a list of related
       # cross streets and measures sorted in driving order
       expression += '\n{} - {:.4f}'.format(fieldValues[1], fieldValues[0])
    # clean up the list variables after completing the for loop
    del sortedList, fieldValues
  else:
    expression += '\n<FNT name="Arial" size="10"><BOL>Cross Street Count = 0</BOL></FNT>'
  # return the label expression to display
  return expression

Example 2: Adapting the Code to Produce Table Style Labels

The labels shown represent only one of the possible ways I could have summarized and/or listed the related feature class/table data.  A semi-tabular presentation is possible if I use a fixed-spaced font like Courier New.  A method for making a tabular style label was given in this post‌ by Jennifer Horsman; however, her code used VBScript and used an inefficient search cursor algorithm.  Below I have adapted her code to use Python and the much more efficient dictionary algorithm shown above.

# Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [ROUTE_NAME] ):
  # 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 to the relate feature class/table
    relateFC = r"C:\Users\OWNER\Documents\ArcGIS\Centerline_Edit.gdb\CL_INTERSECTIONS_PAIRS"
    # create a field list with the relate field first (ROUTE_NAME), 
    # followed by sort field(s) (MEASURE), then label field(s) (CROSS_STREET)
    relateFieldsList = ["ROUTE_NAME", "MEASURE", "CROSS_STREET"]
    # 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 = [ROUTE_NAME]


  # variables to adjust table cell sizes
  iMaxLbl1Sz = 0  
  iMaxLbl2Sz = 0  
  iSpace = 5  


  # 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])
    # process the sorted list to determine cell spacing
    for fieldValues in sortedList:
      strLabel1 = fieldValues[1]  
      strLabel2 = '{:.4f}'.format(fieldValues[0])  
      if (len(strLabel1) > iMaxLbl1Sz):
        iMaxLbl1Sz = len(strLabel1)  
      if (len(strLabel2) > iMaxLbl2Sz):
        iMaxLbl2Sz = len(strLabel2)  
    # clean up the fieldValues variable once the for loop is done
    del fieldValues


    # My label has a key value header followed by a record count
    expression = labelKey 
    expression += '\n<UND>Cross Street Count = {}</UND>'.format(len(sortedList)) + '_' * (iMaxLbl1Sz + iMaxLbl2Sz + iSpace + 1 - len('Cross Street Count = {}'.format(len(sortedList))))
    # process the sorted list
    for fieldValues in sortedList:
      strLabel1 = fieldValues[1]  
      strLabel2 = '{:.4f}'.format(fieldValues[0])  
      k1 = (iMaxLbl1Sz - len(strLabel1)) + 2
      k2 = iSpace + (iMaxLbl2Sz - len(strLabel2)) - 3
      # append related data to the label expression
      # my label shows a list of related
      # cross streets and measures sorted in driving order
      expression += '\n' + strLabel1 + "." * k1
      expression += "|"
      expression += "." * k2 + strLabel2 + "|"
    # clean up all list variables after completing the for loops
    del sortedList, fieldValues
  else:
    # My label has a key value header followed by a record count
    expression = labelKey 
    expression += '\n<UND>Cross Street Count = 0</UND>'
  # return the label expression to display
  return expression

The code above results in this output:

Labels_with_1_To_M_Relate_Table.png

Example 3: Another Table Style Label Alternative

Here is an alternative table style layout.  The code below includes all of the code shown in the previous example through line 36, but replaces the code that began in line 37 in the code for example 2 as follows:.

  # variables to adjust table cell sizes  
  iMaxLbl1Sz = 0    
  iMaxLbl2Sz = 0    
  iSpace = 5    
  
  
  # 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])  
    # process the sorted list to determine cell spacing  
    for fieldValues in sortedList:  
      strLabel1 = fieldValues[1]    
      strLabel2 = '{:.4f}'.format(fieldValues[0])    
      if (len(strLabel1) > iMaxLbl1Sz):  
        iMaxLbl1Sz = len(strLabel1)    
      if (len(strLabel2) > iMaxLbl2Sz):  
        iMaxLbl2Sz = len(strLabel2)    
    # clean up the fieldValues variable once the for loop is done  
    del fieldValues  
  
  
    # My label has a key value header followed by a record count  
    expression = "<CLR red='255' green='255' blue='255'>_</CLR>" + labelKey + "<CLR red='255' green='255' blue='255'>" +  '_' * (iMaxLbl1Sz + iMaxLbl2Sz + iSpace + 2 - len("_"+labelKey)) + "</CLR>"
    expression += "\n_<UND>Cross Street Count = {}</UND>".format(len(sortedList)) + '_' * (iMaxLbl1Sz + iMaxLbl2Sz + iSpace + 2 - len('_Cross Street Count = {}'.format(len(sortedList))))  
    # process the sorted list  
    for fieldValues in sortedList:  
      strLabel1 = fieldValues[1]    
      strLabel2 = '{:.4f}'.format(fieldValues[0])    
      k1 = (iMaxLbl1Sz - len(strLabel1)) + 2  
      k2 = iSpace + (iMaxLbl2Sz - len(strLabel2)) - 3  
      # append related data to the label expression  
      # my label shows a list of related  
      # cross streets and measures sorted in driving order  
      expression += '\n_<UND>' + strLabel1 + "." * k1  
      expression += "|"  
      expression += "." * k2 + strLabel2 + "</UND>_"  
    # clean up all list variables after completing the for loops  
    del sortedList, fieldValues  
  else:  
    # My label has a key value header followed by a record count  
    expression = "<CLR red='255' green='255' blue='255'>_</CLR>" + labelKey   
    expression += '\n_<UND>Cross Street Count = 0</UND>'  
  # return the label expression to display  
  return expression

With some adjustments to the label style and using the Maplex Label Engine, the example 3 code variation can produce an output that looks like the example below:

LabelTableSytle2.png

Considering Other Possibilities Supported by this Technique

More complex relationship primary and foreign keys can also be handled by modifying the code above.  A dictionary works for almost anything where exact values shared between the parent feature class and related feature class/table can be looked-up, even when a join or relate is not possible in ArcMap.  For example, the dictionary key could be used to do look-ups based on a sub-string from a field or on many fields in the parent feature class and/or related feature class/table to create a multi-field key look-up without having to parse or concatenate the values of those fields into a new field in the original feature classes/tables.  Look-ups based on portions of dates or numeric calculations could also be done without creating fields to hold those values, as long as an exact match between the two sources can be made.

A limited set of spatial look-ups can also be handled by a dictionary, such as finding exact matches or duplicates of shapes or finding extracted coordinates that are shared by the two geometries (for example, a dictionary look-up of points can be done against the from or to end point coordinates of a line where the points overlap the line ends).  However, dictionaries cannot be used to speed up inexact value matching or near proximity spatial relationships.

Additionally, I could have used multiple global dictionaries to build labels from more than one relate look-up.  This is useful in situations where the parent feature class has several fields relating to more than one related feature class/table, or where multi-level feature class/table relationships exist (i.e., the parent feature class relates to a child feature class/table, and the child feature class/table relates to another child feature class/table through one or more of its fields).

Situations Where the Technique Shown Might Not Work and Possible Solutions

The primary reason that this technique may not work will occur when the related feature classes/tables are too large to fit in memory after being loaded into a dictionary.  In these cases, applying some sort of query filter on the related feature class/table based on the entire set of parent features in the current map extent would be required before loading it into the dictionary to keep the dictionary from becoming too large.  While it may be possible to do this with arcpy mapping code, I have not tried that to see if it works, but that is something that I may look into later.

Memory management of this code is also important to avoid memory leaks, since this code can bypass the normal memory management processes of the label engine.  For example, memory issues may occur after several label refreshes if variables used to process the lists of listed fields in for loops are not deleted after the loops complete and the list variables are no longer needed.

How I Configured the Labels Shown in the Examples

In case you like the label style layout shown in my screen shot above, here are the settings I used:

I used the Standard Label Engine.  The initial label symbol was set to be the Bullet Leader symbol from the predefined symbol list.  I modified the font from 10 point to 8 point.  For the labels in screen shot 1 I used Arial font and for the table style labels in screen shot 2 I used Courier New (or some other fixed space font).  The Placement Properties are set to Horizontal Placement.  I pressed the Symbol button, then I pressed the Edit Symbol button, then I chose the Advanced Text tab and pressed the Text Background Properties Button.  On the Text Background Properties dialog I changed the leader style to the angled line type associated with the fifth radio button.  I pressed the Symbol button under the Leader check box and changed the line and arrow symbol shown as a dot to red.  After returning to the Text Background Properties dialog, I checked the Border option for the background and then pressed the Symbol button under the Border check box to set the border fill and border line style to No Fill.  After pressing OK on all child dialogs, I set the horizontal alignment on the General tab on the first Symbol dialog to Left.

48 Comments
DuncanHornby
MVP Notable Contributor

Hey great article, that is a really nifty way of labelling by making the dictionary global. Did not know you could do that.

BrianBrenhaug
Deactivated User

This is really fantastic...thanks for writing this up.

JosephFaliti
Occasional Contributor

Hello Richard,

This looks promising as a potential solution for a project I have.  Would you be able to help me get the code working for my situation?  I have tried to modify the code to fit my variables; however, I am experiencing an error: "No features found.  Could not verify expression."

Running Desktop 10.3 Version 10.3.0.4322

Data stored on mapped network drive in fGDB format

Any guidance would be appreciated.

Thanks,

-Joe

RichardFairhurst
MVP Honored Contributor

Joe:

I probably can help you adapt the code.  I will need to know what feature class and table is involved, what field or fields relate the two together, and what fields you want to appear in the label from the feature class and the related table.  I would assist you to create a label that uses the basic label style shown in Example 1 as a starting point.  After that, I can give general pointers on how to implement a more sophisticated label style, but implementing that style would be your responsibility.

JosephFaliti
Occasional Contributor

Richard,

Thank you for your willingness to help out.

Relationship Class Origin Feature Class = MP11_12

Relationship Class Origin Feature Class Key Field = POLYID5

Relationship Class Destination Table = TBLSCHOO

Relationship Class Destination Table Key = POLYID

Feature Class Label Field = POLYID5

Table Label Fields = SCHOOL_ID, NAME, LO_GRD, HI_GRD

-Joe

RichardFairhurst
MVP Honored Contributor

The code below should work to create a basic label once you change the relateFC path in line 9 to match the actual directory path (including geodatabase name with extension like .gdb if applicable) and table name (including any file extension like .dbf if applicable) of your TBLSCHOO table.  Let me know if you get any errors.

# Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [POOLYID5] ):
  # 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"C:\PathName\TBLSCHOO.dbf"
    # create a field list with the relate field first (POLYID), 
    # followed by sort field(s) (SCHOOLID), then label field(s) (CROSS_STREET)
    relateFieldsList = ["POLYID", "SCHOOL_ID", "NAME", "LO_GRID", "HI_GRID"]
    # 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 = [POLYID5]
  # 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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
JosephFaliti
Occasional Contributor

Hi Richard,

I had a chance to try your suggested revision, but I am still experiencing the "No features found. Could not verify expression." error.

Thanks for giving it a shot.

-Joe

RichardFairhurst
MVP Honored Contributor

Did you fix the misspelled field name in line 3?  I just noticed that I typed it as POOLYID5 when it should have been POLYID5.

If that doesn't fix it, try pasting it in sections and verifying the expression for each.  For example lines 1 through 30 should work without an error if you add this line (properly indented of course) after it:

return [POLYID5]

You need to isolate which line is triggering the error.  Are you sure you put in the correct full path and file name of the related table in line 9?

JosephFaliti
Occasional Contributor

I did fix the typo, but that was not the issue.  I have triple checked the path as well.  I'll have to revisit this some other time.  Thanks for your suggestions.

-Joe

RichardFairhurst
MVP Honored Contributor

Just to be sure I have verified that my original code as posted in the Blog still works when pasted into my original layer.  So the likely lines that have a problem are those that have been changed to match your data: Line 3, Line 9, Line 12, Line 34, Line 43, Line 47 and Line 51.  I would probably try isolating lines 1-30 from the remainder to see if lines 3, 9 and 12 can be verified.  Then I would probably try commenting out lines 43 through 51 to see if line 34 caused any problem.  Then if that passed I would verify the expression after removing the comment from line 43, then lines 44 through 49 and finally lines 50 and 51.

EricBowman3
Emerging Contributor

rfairhur24 - awesome work on this.  I was able to get this to work in my scenario.  I have a tract to many depth sequences.  It seems to work well with just a few (2-3) fields, but performance degrades when I have 5-7 fields. I'm running everything in SDE and SQL server 10.3.1.  Any ideas on improving performance? Only have about 3000 tracts to 23000 depth sequences.  

JackHorton
Esri Contributor

This technique could make really great stacked labels if we supported HTML formatting!  If you agree, vote up the following idea:

https://community.esri.com/ideas/9592 

FatihDur
Occasional Contributor

A small efficiency suggestion, the part below can be passed to dict.setdefault method,

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

such as 

relateDict.setdefault(relateRow[0], [relateRow[1:]]).append(relateRow[1:])
OdonchimegAyush
Occasional Contributor

Hi Richard Fairhurst,

Your blog article is very interesting for us. Greet work.

We are trying to do it from sde sql database, and publish it as services on our portal to use for operational dashboard or web app builder.

Could you please help our use case? script result is being 0 count. it must have 1 record at least.

Origin Feature Class:

Feature point:    OTGIS_ENTGDB_REG.SDE.TestUGVentilationStation

key field:      GlobalID

label field:    station

 

Destination Tables:

first related table :          OTGIS_ENTGDB_REG.SDE.TestUGVentPrimaryStatSurvey

first related key:       parentpid

primary label fields:  pressure, wetbulbtemp, humidity

second related table :    OTGIS_ENTGDB_REG.SDE.TestUGVentSecondStatSurvey

second related key: parentsid 

second label fields:  workdrybulb, workwetbulb

third related table :          OTGIS_ENTGDB_REG.SDE.TestUGVentCriticalStatSurvey

third related key: parentcid 

third label fields:  quantity, levelnh3, levelco

Our portal version is 10.6.

thank you very much

RichardFairhurst
MVP Honored Contributor

I have not worked with Portal or tried labels with code like this in web app builder, so if your issues are specific to those platforms, I can't really help.  If it is assumed that the code will be compatible with the platform and your questions are related to making the code work for this configuration of table relationships then I should be able help.

The basic structure of the code always reads the related tables first into one or more dictionaries using the key value that relates each record set to the parent table.  Then the labels are built using the primary feature class features to provide the group header in the label and the dictionaries to provide the detail listings of each label.

Correct me if I am wrong, but it appears from the table set you have outlined that the OTGIS_ENTGDB_REG.SDE.TestUGVentilationStation feature class is the parent feature class controlling the labels and the other three tables all have a foreign keys related to the station field of that parent feature.  It is not clear how the three related tables relate to each other, but I would assume they are in a Many to Many relationship to each other using just the foreign related key fields you have mentioned.  As a result each related table may need its own section in the output label to avoid creating multiple copies of each related record in one table if you were to combine them with the records of another related table.  If you follow this approach then you need to build three dictionaries which will be handled independently relative to the other dictionaries when you build the label.

Using a very basic label layout that just displays each record in a raw form, the core code for extracting the data should be something like:

# Initialize three global dictionaries for each related feature class/table
relateDict1 = {}
relateDict2 = {}
relateDict3 = {}
def FindLabel ( [station] ):
  # declare the dictionary global so it can be built once and used for all labels
  global relateDict1
  global relateDict2
  global relateDict3
  # only populate the first dictionary if it has no keys
  if len(relateDict1) == 0:
    # Provide the path to the relate feature class/table
    relateFC1 = r"connectionfile/OTGIS_ENTGDB_REG.SDE.TestUGVentPrimaryStatSurvey"
    # create a field list with the relate field first, 
    # followed by sort field(s), then label field(s)
    relateFieldsList1 = ["parentpid", "pressure", "wetbulbtemp", "humidity"]
    # process a da search cursor to transfer the data to the dictionary
    with arcpy.da.SearchCursor(relateFC1, relateFieldsList1) 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 relateDict1:
          # [searchRow[1:]] is a list containing
          # a list of the field values after the key.
          relateDict1[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
          relateDict1[relateKey].append(relateRow[1:])
    # delete the cursor, and row to make sure all locks release
    del relateRows, relateRow
  # only populate the second dictionary if it has no keys
  if len(relateDict2) == 0:
    relateFC2 = r"connectionfile/OTGIS_ENTGDB_REG.SDE.TestUGVentSecondStatSurvey"
    relateFieldsList2 = ["parentsid", "workdrybulb", "workwetbulb"]
    with arcpy.da.SearchCursor(relateFC2, relateFieldsList2) as relateRows:
      for relateRow in relateRows:
        relateKey = relateRow[0]
        if not relateKey in relateDict2:
          relateDict2[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
          relateDict2[relateKey].append(relateRow[1:])
    del relateRows, relateRow
  # only populate the third dictionary if it has no keys
  if len(relateDict3) == 0:
    relateFC3 = r"connectionfile/OTGIS_ENTGDB_REG.SDE.TestUGVentCriticalStatSurvey"
    relateFieldsList3 = ["parentcid", "quantity", levelnh3", "levelco"]
    with arcpy.da.SearchCursor(relateFC3, relateFieldsList3) as relateRows:
      for relateRow in relateRows:
        relateKey = relateRow[0]
        if not relateKey in relateDict3:
          relateDict3[relateKey] = [relateRow[1:]]
        else:
          relateDict3[relateKey].append(relateRow[1:])
    del relateRows, relateRow
  # store the current label feature's relate key field value
  # so that it is only read once, improving speed
  labelKey = [station]
  # 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 relateDict1:
    # sort the list of the list of fields
    sortedList = sorted(relateDict1[labelKey])
    # process the sorted list
    for fieldValues in sortedList:
       # append related data to the label expression
       # my label shows a list of related
       # stations with sorted pressures, wetbuldtemps and humidities
       expression += '\n{0} - {1} - {2}'.format(fieldValues[0], fieldValues[1], fieldValues[2])
    # clean up the list variables after completing the for loop
    del sortedList, fieldValues
  if labelKey in relateDict2:
    sortedList = sorted(relateDict2[labelKey])
    expression += '\n<FNT name="Arial" size="10"><BOL>Station Count = {}</BOL></FNT>'.format(len(sortedList))
    for fieldValues in sortedList:
        expression += '\n{0} - {1}'.format(fieldValues[0], fieldValues[1])
    del sortedList, fieldValues
  if labelKey in relateDict3:
    sortedList = sorted(relateDict3[labelKey])
    for fieldValues in sortedList:
        expression += '\n{0} - {1} - {2}'.format(fieldValues[0], fieldValues[1], fieldValues[2])
    del sortedList, fieldValues
  # return the label expression to display
  return expression‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I have not tried the efficiency step outlined by Fatih Dur, so I went with the code structure I have personally used before.  Using Fatih's recommendation could result in shorter and cleaner code, so once you have a code base that you know is working you could try implementing that efficiency step and keep it if the revised code works the same.

OdonchimegAyush
Occasional Contributor

Thank you. It is working on ArcMap. We are trying to publish it into our portal now.

OdonchimegAyush
Occasional Contributor

Richard

Is it possible to filter only lastest 3 record from related table?

below code is working now:

relateDict1 = {}
relateDict2 = {}
relateDict3 = {}
def FindLabel ( [OTGIS_ENTGDB_REG.SDE.TestUGVentilationStation.GlobalID], [OTGIS_ENTGDB_REG.SDE.TestUGVentilationStation.StationName], [OTGIS_ENTGDB_REG.SDE.TestUGVentilationStation.StationType]):
    global relateDict1
    global relateDict2
    global relateDict3
    StationID = [OTGIS_ENTGDB_REG.SDE.TestUGVentilationStation.GlobalID]
    StationName = [OTGIS_ENTGDB_REG.SDE.TestUGVentilationStation.station]
    StationType = [OTGIS_ENTGDB_REG.SDE.TestUGVentilationStation.statsubtype]
    if len(relateDict1) == 0:
        relateFC = r"OTGIS_ENTGDB_REG.SDE.TestUGVentPrimaryStatSurvey"
        relateFieldsList = ["parentpid", "pressure", "wetbulbtemp", "humidity"]
        with arcpy.da.SearchCursor(relateFC, relateFieldsList) as relateRows:
            for relateRow in relateRows:
                relateDict1.setdefault(relateRow[0], [relateRow[1:]]).append(relateRow[1:])
        del relateRows, relateRow
    if len(relateDict2) == 0:
        relateFC = r"OTGIS_ENTGDB_REG.SDE.TestUGVentSecondStatSurvey"
        relateFieldsList = ["parentsid", "workdrybulb", "workwetbulb", "weeknum"]
        with arcpy.da.SearchCursor(relateFC, relateFieldsList) as relateRows:
            for relateRow in relateRows:
                relateDict2.setdefault(relateRow[0], [relateRow[1:]]).append(relateRow[1:])
        del relateRows, relateRow
    if len(relateDict3) == 0:    
        relateFC = r"OTGIS_ENTGDB_REG.SDE.TestUGVentCriticalStatSurvey"
        relateFieldsList = ["parentcid", "quantity", "levelnh3", "levelco"]    
        with arcpy.da.SearchCursor(relateFC, relateFieldsList) as relateRows:
            for relateRow in relateRows:
                relateDict3.setdefault(relateRow[0], [relateRow[1:]]).append(relateRow[1:])
        del relateRows, relateRow
        
    expression = '<FNT name="Arial" size="12"><BOL>{}</BOL></FNT>'.format(StationName)

    if StationType == 'Primary':
        if StationID in relateDict1:
            sortedList= sorted(relateDict1[StationID])
            expression += '\n<FNT name="Arial" size="10"><BOL>{} Survey {}</BOL></FNT>'.format(StationType,len(sortedList))
            for fieldValues in sortedList:
                expression+= '\n{} - {} - {}'.format(fieldValues[0], fieldValues[1], fieldValues[2])
            del sortedList, fieldValues
        else:expression += '\n<FNT name="Arial" size="10"><BOL>No {} survey</BOL></FNT>'.format(StationType)

    if StationType == 'Secondary':
        if StationID in relateDict2:
            sortedList= sorted(relateDict2[StationID])
            expression += '\n<FNT name="Arial" size="10"><BOL>{} Survey {}</BOL></FNT>'.format(StationType,len(sortedList))
            for fieldValues in sortedList:
                expression+= '\n{} - {} - {}'.format(fieldValues[0], fieldValues[1], fieldValues[2])
            del sortedList, fieldValues
        else:expression += '\n<FNT name="Arial" size="10"><BOL>No {} survey</BOL></FNT>'.format(StationType)

    if StationType == 'Critical':
        if StationID in relateDict3:
            sortedList= sorted(relateDict3[StationID])
            expression += '\n<FNT name="Arial" size="10"><BOL>{} Survey {}</BOL></FNT>'.format(StationType,len(sortedList))
            for fieldValues in sortedList:
                expression+= '\n{} - {} - {}'.format(fieldValues[0], fieldValues[1], fieldValues[2])
            del sortedList, fieldValues
        else:expression += '\n<FNT name="Arial" size="10"><BOL>No {} survey</BOL></FNT>'.format(StationType)
    return expression;
RichardFairhurst
MVP Honored Contributor

Since you say you want to keep the 3 latest records from the related tables that suggests that you have a date field associated with those records.  If so, the date field needs to be loaded with the other data to add logic that would publish only the latest 3 records from each table.  Anyway, limiting the number of records is possible, but there are a variety of approaches for implementing that limit and the most efficient method will be determined by the nature of the data that defines what records are the "latest 3 records".  So please explain what data you have available that you would use to choose the latest 3 records from each table.

TaylorKravits
Occasional Contributor

Hey Richard!

I'm not sure if you're still following or helping out on this thread but I've been trying your code for a couple of days and haven't had much success. With this code I was able to label (parcel_no), but that's about it. Is there any chance you could take a look at it to see where I went wrong? My goal is to label "acct", "permit_num", and "dscr". Thank you!

# Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [PARCEL_NO] 😞
# 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 to the relate feature class/table
relateFC = r"C:\Taylor_maps\Permit\ResPermits.gdb\Export_Output"
# create a field list with the relate field first (folio),
# followed by sort field(s) (Folio), then label field(s) (strap)
relateFieldsList = [ "acct" , "permit_num", "dscr"]
# 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 = [PARCEL_NO]
# 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 += '\r\n <FNT name="Arial" size="10"><BOL>Cross Street Count = {}</BOL></FNT>'.format(len(sortedList))
# process the sorted list
for fieldValues in sortedList:
# append related data to the label expression
# my label shows a list of related
# cross streets and measures sorted in driving order
expression += '\r\n{} - {:.4f}'.format(fieldValues[1], fieldValues[0])
# clean up the list variables after completing the for loop
del sortedList, fieldValues
else:
expression += '\r\n<FNT name="Arial" size="10"><BOL> </BOL></FNT>'
# return the label expression to display
return expression

RichardFairhurst
MVP Honored Contributor

Both data sources have to include the field that contains Parcel numbers.  So you are missing that field in the list of fields for the related table if [PARCEL_NO] and "acct" are not the fields that match each other.  The first field in that list has to be the field that contains the values that exactly match the [PARCEL_NO] field.

Please paste your code into a code sample window that preserves the code indentation.  Make sure you are in this post directly, then press the Expand toolbar button

then press the More button and choose Syntax Highlighter

then drop down the Language options and choose Python.

  Then paste your code into the code sample window and hit the OK button to preserve the code indentation.

TaylorKravits
Occasional Contributor

Apologies for not imputing the correct format Richard! So the two fields that are related are the PARCEL_NO (feature class) & "folio" (field in the related table). There are multiple permits for each folio. I tried this code posted below, but got a

File "<expression>" line 1 in <module> and

File "<string>" line 49 in FindLabel

Value Error: Unknown format code 'f' for object of type 'unicode' on line 49.

# Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [PARCEL_NO] ):
  # 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 to the relate feature class/table
    relateFC = r"C:\Taylor_maps\Permits\ResPermits.gdb\Export_Output"
    # create a field list with the relate field first (folio), 
    # followed by sort field(s) (Folio), then label field(s) (strap)
    relateFieldsList = [ "folio" , "acct", "permit_num", "dscr"]
    # 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 = [PARCEL_NO] 
  # 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 += '\r\n <FNT name="Arial" size="10"><BOL>Cross Street Count = {}</BOL></FNT>'.format(len(sortedList))
    # process the sorted list
    for fieldValues in sortedList:
       # append related data to the label expression
       # my label shows a list of related
       # cross streets and measures sorted in driving order
       expression += '\r\n{} - {:.4f}'.format(fieldValues[1], fieldValues[0])
    # clean up the list variables after completing the for loop
    del sortedList, fieldValues
  else:
    expression += '\r\n<FNT name="Arial" size="10"><BOL> </BOL></FNT>'
  # return the label expression to display
  return expression

I really appreciate your help on this Richard! I'm slowing learning Python but this project would help me out a ton.

RichardFairhurst
MVP Honored Contributor

The error message is due to the fact that the format of my example label is based on different field types and needs to be reformatted to match the fields you are using.  The expression variable that formats your data into a label has to be customized.  That part of the code is not generic and only works without modification for fields that match the data type or my original example fields.

So what should your label look like?  Write out an example label that presents the data the way you want it to appear, including any new line breaks, separator characters, and fixed text.  Include a list of the actual values stored in each of the 4 fields so I know which parts of the label come from the fields and which parts come from a formatting string.

TaylorKravits
Occasional Contributor

Truth be told, I just need a simple label something like:

Parcel_no (folio)

Acct

permit_no

dscr

New lines for each label, that's all.

The actual values in the fields are these examples. I have about 60 of them each with similar parcel numbers but different acct, permit_no_, and dscr

Parcel_no (folio)

999320100085

acct

A9928543

Permit_no

ABD2011-25964

dscr

DEMOLITION

Hope this helps!

RichardFairhurst
MVP Honored Contributor

The format won't work as described with all of those line returns.  The label has to have the parcel as a header and then give a list of all related records below it in a single label.  All of those returns will make the label too long if you have more than 2 or 3 related records under a given parcel.

I coded a format that should look something like this:

Parcel_no (folio): 999320100085

Related Record Count: 3

acct: A9928543

Permit_no: ABD2011-25964

dscr: DEMOLITION

acct: A9928544

Permit_no: ABD2011-25965

dscr: DEMOLITION

...

If you want to learn how to change the label format to suit your own tastes, you should search the web for Python string formatting examples that use the .format() method for building standard ArcMap labels and strings.  All customization of the label occurs in Lines 37, 43 and 49.  The record count in Line 43 is optional and can be commented out if you do not want it, and then only the Parcel_no (folio) will appear as the label if there are no related records.

# Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [PARCEL_NO] ):
  # 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 to the relate feature class/table
    relateFC = r"C:\Taylor_maps\Permits\ResPermits.gdb\Export_Output"
    # create a field list with the relate field first (folio), 
    # followed by sort field(s) (acct), then label field(s) (permit_no, dscr)
    relateFieldsList = [ "folio" , "acct", "permit_num", "dscr"]
    # 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 = [PARCEL_NO] 
  # start building a label expression.
  # My label has a bold key value header in a larger font
  expression = '<FNT name="Arial" size="12"><BOL>Parcel_No (folio)\r\n{}</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 += '\r\n<FNT name="Arial" size="10"><BOL>Related Record Count = {}</BOL></FNT>'.format(len(sortedList))
    # process the sorted list
    for fieldValues in sortedList:
       # append related data to the label expression
       # my label shows a list of related
       # cross streets and measures sorted in driving order
       expression += '\r\nacct: {}\r\nPermit_no: {}\r\ndscr: {}'.format(fieldValues[1], fieldValues[2], fieldValues[3])
    # clean up the list variables after completing the for loop
    del sortedList, fieldValues
  else:
    expression += '\r\n<FNT name="Arial" size="10"><BOL> </BOL></FNT>'
  # return the label expression to display
  return expression‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
TaylorKravits
Occasional Contributor

Thank you so so so so much!! I really appreciate your help and will look into that .format() method for building standard ArcMap labels and strings!

by Anonymous User
Not applicable

Good stuff although the bummer on my end is python labels on related tables are not supported in a mapservice. No fix in sight. 

Bug:

Map service does not display labels from related table through the Python script.

Nevertheless, I spent some time today reviewing the code and tinkering with index and format functions and have hit a minor wall attempting to determine how I can pull the last time a catch basin was cleaned from the dictionary assembled. Is there a way to select a value for the date with the highest value based on the key?

# Initialize a global dictionary for a related feature class/table
relateDict = {}
import datetime
def FindLabel (  [CATCH_BASIN_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 to the relate feature class/table
    relateFC =  r"Database Connections\Connection to STGGIS14_L-CBEDIT.sde\PLBGIS.SDE.CatchBasinDebrisRemoval"
    # create a field list with the relate field first (CATCH_BASIN_ID), 
    # followed by sort field(s) (MEASURE), then label field(s) (CROSS_STREET)
    relateFieldsList = ["CATCH_BASIN_ID", "CLEANOUT_DATE"]
    # 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[0:]]
        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[0:])
    # 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 = [CATCH_BASIN_ID]
  # start building a label expression.
  expression = ''
  # 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], reverse = True )
     #Take the sorted list and select the first value
     
    # process the sorted list
    for fieldValues in sortedList:
       # append related data to the label expression
       expression += '\n {1}'.format(fieldValues[0], fieldValues[1].strftime("%m/%Y"))
    # clean up the list variables after completing the for loop
    del sortedList, fieldValues
  else:
    0
  # return the label expression to display
  return expression
RichardFairhurst
MVP Honored Contributor

The code was designed to compile a list of values, but it can be adjusted to list only a single value based on the maximum from a set of values.  Basically line 26 and 30 in your code would change.  You would have change line 26 to be an elif statement that compares the current date stored in the dictionary with the date of the relate row currently being processed and if it is, replace the value of the dictionary rather than append the value.  Something like:

        elif relateDict[relateKey][0][1] < relateRow[1]:
          # if the relate key is already in the dictionary 
          # and the current date is greater than the dictionary date
          # replace the value associated with the key
          relateDict[relateKey] = [relateRow[0:]]
‍‍‍‍‍‍‍‍‍‍‍

I did not test the code, so I am not entirely sure I got the dictionary indexing right and I am not fully revamping the code to gain greater efficiency by eliminating the list within a list structure that is no longer really needed for the dictionary value, but this should give you the idea of how to adapt the logic to your needs.

by Anonymous User
Not applicable

Thanks Richard and it works. I need to continue educating myself on lists as this is a great resource to utilize. Thanks again.

GregoryJohnson1
Emerging Contributor

Good afternoon, Richard,

I have the first two examples in this fine article displaying labels in ArcMap 10.7.1. However, when I publish REST services, nothing is appearing in my Javascript SDK 3.x app. Should I expect something, or is this going to only appear in ArcMap? I've noted the bug # 000111747 referenced above from June.

Thank you,

Greg Johnson

JasonWilder
Occasional Contributor

Hi Richard. Great work. I managed to get your code running on my feature class without error, but the related part of the label is blank. The counts, in this case species count, for every polygon is 0. I wonder if the reason is a memory limit you mentioned in your write up since the geodatabase is fairly large. But more likely a code modification error on my part. Anyways, I've post a link to the geodatabase I am working with if you have time to take a quick look. Any help/advice is much appreciated. I'll will continue to troubleshoot. Thanks!

Landscape 3.3 Data for Delaware Bay Region of New Jersey 

# Provide the path and table name to the relate feature class/table
    relateFC = r"D:\delbay_v3_3.gdb\Envr_hab_ls_v3_3_delbay_sp_02"
    # create a field list with the relate field first (LINKID),
    # followed by sort field(s) (COMNAME), then label field(s) (COMNAME, "FEAT_LABEL")
    relateFieldsList = ["LINKID", "COMNAME", "COMNAME","FEAT_LABEL"]

Ultimately, going for a simple label like below.

Species Count = 5
_________________________________
Bald Eagle...............Foraging
Bald Eagle...............Nest
Northern Myotis..........Nest
Rapids Clubtail..........Nest
Cliff Swallow............Breeding Sighting‍‍‍‍‍‍‍‍
RichardFairhurst
MVP Honored Contributor

Jason:

Can you please post your code.  I may be able to see what modifications are causing the output to not count any features.  It is critical that the LINKID field values in the related table and the field you used for the labeled features contain matching values.  Using the wrong field from the feature class as your relate field that does not actually match any of the values in the LINKID field would likely not cause an error, but it would never find any related records in the related table and would produce labels without any related values listed and a related record count of 0.

JasonWilder
Occasional Contributor

Hi Richard. Thanks for reply! Please see below. The Geodatabase comes with the relationship class built already, but are they are in a Feature Dataset, so I wonder if this is causing the issue somehow. Here is my working code:

# Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [LINKID] ):
 # 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 to the relate feature class/table
 # Provide the path and table name to the relate feature class/table
 relateFC = r"D:\delbay_v3_3.gdb\Envr_hab_ls_v3_3_delbay_sp_02"
 # create a field list with the relate field first (LINKID),
 # followed by sort field(s) (COMNAME), then label field(s) (COMNAME, "FEAT_LABEL")
 relateFieldsList = ["LINKID", "COMNAME", "COMNAME", "FEAT_LABEL"]
 # 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 = [LINKID]
 # variables to adjust table cell sizes
 iMaxLbl1Sz = 0
 iMaxLbl2Sz = 0
 iSpace = 5
 # 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])
 # process the sorted list to determine cell spacing
 for fieldValues in sortedList:
 strLabel1 = fieldValues[1]
 strLabel2 = '{:.4f}'.format(fieldValues[0])
 if (len(strLabel1) > iMaxLbl1Sz):
 iMaxLbl1Sz = len(strLabel1)
 if (len(strLabel2) > iMaxLbl2Sz):
 iMaxLbl2Sz = len(strLabel2)
 # clean up the fieldValues variable once the for loop is done
 del fieldValues
 # My label has a key value header followed by a record count
 expression = labelKey
 expression += '\n<UND>Species Count = {}</UND>'.format(len(sortedList)) + '_' * (iMaxLbl1Sz + iMaxLbl2Sz + iSpace + 1 - len('Species = {}'.format(len(sortedList))))
 # process the sorted list
 for fieldValues in sortedList:
 strLabel1 = fieldValues[1]
 strLabel2 = '{:.4f}'.format(fieldValues[0])
 k1 = (iMaxLbl1Sz - len(strLabel1)) + 2
 k2 = iSpace + (iMaxLbl2Sz - len(strLabel2)) - 3
 # append related data to the label expression
 # my label shows a list of related
 # cross streets and measures sorted in driving order
 expression += '\n' + strLabel1 + "." * k1
 expression += "|"
 expression += "." * k2 + strLabel2 + "|"
 # clean up all list variables after completing the for loops
 del sortedList, fieldValues
 else:
 # My label has a key value header followed by a record count
 expression = labelKey
 expression += '\n<UND>Species Count = 0</UND>'
 # return the label expression to display
 return expression

RichardFairhurst
MVP Honored Contributor

I can't interpret the code, since the indentation is gone.  Please indent the code properly.

I can say that line 47 and 60 need to change to just the field value, since your data in the field you are referencing is not a float value.

RichardFairhurst
MVP Honored Contributor

Try the code below.  I have adjusted the label expressions to fit your field values, which are both string, and to sort and print the Comname then the Feat_Label.  The feature dataset of the layer should not matter.  I also have changed the label for the case where no related record is found to report how many records are in the dictionary to help diagnose the problem.

# Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [LINKID] ):
  # 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 to the relate feature class/table
    relateFC = r"D:\delbay_v3_3.gdb\Envr_hab_ls_v3_3_delbay_sp_02"
    # create a field list with the relate field first (ROUTE_NAME), 
    # followed by sort field(s) (COMNAME), then label field(s) (FEAT_LABEL)
    relateFieldsList = ["LINKID", "COMNAME", "FEAT_LABEL"]
    # 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 = [LINKID]


  # variables to adjust table cell sizes
  iMaxLbl1Sz = 0  
  iMaxLbl2Sz = 0  
  iSpace = 5  


  # 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])
    # process the sorted list to determine cell spacing
    for fieldValues in sortedList:
      strLabel1 = fieldValues[0]  
      strLabel2 = fieldValues[1]  
      if (len(strLabel1) > iMaxLbl1Sz):
        iMaxLbl1Sz = len(strLabel1)  
      if (len(strLabel2) > iMaxLbl2Sz):
        iMaxLbl2Sz = len(strLabel2)  
    # clean up the fieldValues variable once the for loop is done
    del fieldValues


    # My label has a key value header followed by a record count
    expression = labelKey 
    expression += '\n<UND>Species Count = {}</UND>'.format(len(sortedList)) + '_' * (iMaxLbl1Sz + iMaxLbl2Sz + iSpace + 1 - len('Species Count = {}'.format(len(sortedList))))
    # process the sorted list
    for fieldValues in sortedList:
      strLabel1 = fieldValues[0]  
      strLabel2 = fieldValues[1]
      k1 = (iMaxLbl1Sz - len(strLabel1)) + 2
      k2 = iSpace + (iMaxLbl2Sz - len(strLabel2)) - 3
      # append related data to the label expression
      # my label shows a list of related
      # cross streets and measures sorted in driving order
      expression += '\n' + strLabel1 + "." * k1
      expression += "|"
      expression += "." * k2 + strLabel2 + "|"
    # clean up all list variables after completing the for loops
    del sortedList, fieldValues
  else:
    # My label has a key value header followed by a record count
    expression = labelKey 
    expression += '\n<UND>Species Count = 0</UND>'
    expression += 'Not in {} dictionary IDs'.format(len(relateDict))
  # return the label expression to display
  return expression‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
JasonWilder
Occasional Contributor

Thanks Richard! I appreciate your time. No errors, but still getting 0 counts. The related count diagnostics has helped. Both the Feature Class Primary Key (LINKID) and the Related Table Foreign Key (LINKID) match, but I pull back only a subset of the total rows in the Relate Table. However, when I change the following line 12 from:

relateFieldsList = ["LINKID", "COMNAME", "FEAT_LABEL"]

to

relateFieldsList = ["*"]

I get the count of the entire record set, which is what I would expect. Anyways, I'll keep at it. Thanks!

RichardFairhurst
MVP Honored Contributor

How big are the Dictionary Record sets being reported for the different field lists you have tried?  The record count of the dictionary should be less than the record count of the related table itself, because all records are being collapsed into just the count of unique LINKID values in your table.  Probably the record count is higher when you use

  • , because this would be summarized on the OBJECTID values, which are unique for every record, not the LINKIDs, which can be duplicated on multiple records.  Perform a summary on your LINKID field of your related table to find out if the count of the dictionary matches the count or records summarized on the LINKID field.  If they match the dictionary was loaded correctly.  If the record count of the summary is less than the number of features in your feature class, than some features have no related records in the table, assuming that the LINKID values of the features are unique for each feature.
  • Probably the label should avoid any special formatting or attempts to make it look like a table until you get a result.  Try the simple label code below.

    # Initialize a global dictionary for a related feature class/table
    relateDict = {}
    def FindLabel ( [LINKID] ):
      # 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 to the relate feature class/table
        relateFC = r"D:\delbay_v3_3.gdb\Envr_hab_ls_v3_3_delbay_sp_02"
        # create a field list with the relate field first (ROUTE_NAME), 
        # followed by sort field(s) (COMNAME), then label field(s) (FEAT_LABEL)
        relateFieldsList = ["LINKID", "COMNAME", "FEAT_LABEL"]
        # 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 = [LINKID]
      # 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>Species Count = {}</BOL></FNT>'.format(len(sortedList))
        # process the sorted list
        for fieldValues in sortedList:
           # append related data to the label expression
           # my label shows a list of related
           # cross streets and measures sorted in driving order
           expression += '\n{} - {}'.format(fieldValues[0], fieldValues[1])
        # clean up the list variables after completing the for loop
        del sortedList, fieldValues
      else:
        expression += '\n<FNT name="Arial" size="10"><BOL>Species Count = 0</BOL></FNT>'
        expression += 'Not in {} dictionary IDs'.format(len(relateDict))
      # return the label expression to display
      return expression‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

    Please screen shot a record in the table view from a selected feature you are trying to label in your map view with the LINKID field value showing and also the selected set of records that match it in the related table's table view with the 3 field values showing.

    JasonWilder
    Occasional Contributor

    Hi Richard. Please see screen captures. Thanks!

    RichardFairhurst
    MVP Honored Contributor

    Please show me the properties of the LINKID fields in both the feature class and the table.  I want to confirm they are both Long fields.  If one is Long and the other is Double, that would cause a problem, since Python would store the Long as 53861 and the double as 53861.0, and these two values do not match exactly.

    To make sure both are interpreted as Long values change Line 18 and Line 35 to:

    18        relateKey = int(relateRow[0])
    
    35  labelKey = int([LINKID]‍‍‍‍‍‍)
    JasonWilder
    Occasional Contributor

    That was it! Both LINKID are Long data types. As soon as I defined them both as "int" on lines 18 and 35, it worked!  Richard, thank you so much for your time on this. I learned a great deal. Below is my result.

    RichardFairhurst
    MVP Honored Contributor

    Glad to hear it.  You can experiment with the formatted label version now if you want.

    KariBuckvold
    Frequent Contributor

    Richard, this looks really awesome!  Have you tried to make a related annotation feature class using this method?

    RichardFairhurst
    MVP Honored Contributor

    I have not tried that.  I am sure that this type of label would not work for generating a feature-linked annotation feature class, but it might work for a standard annotation feature class or an annotation group in a map document.  All I could suggest is that you could try to create an annotation feature class or group from labels built using this technique to see if it generates an error.  I would not be surprised if an error is produced, but since I have never tried it I can't say it would for sure.

    DuncanKinnear
    Occasional Contributor

    Hey Richard this is awesome, thanks for sharing.

    As someone mentioned above, pity ArcMap doesn't support HTML formatting to create really nice tables with background fill etc. - but looks like this kind of thing may be possible in Pro? Good to find this solution until we migrate to Pro 🙂

     

    Cheers

     

    Duncan

    RyanOk
    by
    Occasional Explorer

    Hi Richard,

    thanks for sharing this! not sure you are still following this but would you be able to have a look my modifications?

    The issue is that not all labels are showing up. It only shows other label one by one when i change extent.

    RyanOk_0-1633723050327.png

     

    ### Reference
    ### https://community.esri.com/t5/python-blog/creating-labels-with-related-table-data/ba-p/884167
    ### Editing Version
    import arcpy
    # Initialize a global dictionary for a related feature class/table
    relateDict = {}
    def FindLabel ( [BH_Number] 😞  
        # 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 to the relate feature class/table    
            relateFC = r"D:\Test\TLS_011_PieSymbology\ProjectData.gdb\J6200641_ArcpyTestingTable"   
            # create a field list with the relate field first (ROUTE_NAME), 
            # followed by sort field(s) (MEASURE), then label field(s) (CROSS_STREET)    
            relateFieldsList = ["BH_Number", "Sample_Depth", "Benzene", "CCME_Fraction_2________________________nC10_nC16_"]    
            # 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 = [BH_Number]
            
            # 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)
            # expression = [BH_Number]
            
            # 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>Cross Street Count = {}</BOL></FNT>'.format(len(sortedList))
                # process the sorted list    
    
                HydroSurface = ["0.00-0.15", "0.45-0.60", "0.50-1.00", "0.50-1.70"]
                HydroSubsoil = ["1.00-1.15", "1.85-2.00", "2.85-3.00", "3.85-4.00", "1.00-1.70", "1.35-1.50", "1.30-1.90", "2.20-2.80", "6.00-6.60", "8.00-8.60", "4.35-4.50"]
                # SalinitySurface = "0.00-0.15"
            
                for fieldValues in sortedList:       
                    # append related data to the label expression       
                    # my label shows a list of related       
                    # cross streets and measures sorted in driving order
                
                    # Benzene
                    if fieldValues[1].startswith("<"):
                        pass
                    elif fieldValues[1] == "-":
                        pass
                    elif float(fieldValues[1]) < float(0.046):
                        pass
                    else:   
                        expression += "\nBenzene| <CLR red='250' blue='0'> {:.3f}  </CLR>  | <CLR red='0' blue='250'>0.046</CLR> |  {}".format(float(fieldValues[1]), fieldValues[0]) 
    
                    # CCME Fraction 2
                    if fieldValues[2].startswith("<"):
                            pass
                    elif fieldValues[2] == "-":
                        pass
                    elif ((float(fieldValues[2]) >float(150)) and (fieldValues[0] in HydroSurface)):
                        expression += "\nCCME Fraction 2| <CLR red='250' blue='0'>{}</CLR> | <CLR red='0' blue='250'>150</CLR> |  {}".format(int(fieldValues[2]), fieldValues[0])
                    elif ((float(fieldValues[2]) >float(300)) and (fieldValues[0] in HydroSubsoil)):
                        expression += "\nCCME Fraction 2| <CLR red='250' blue='0'>{}</CLR> | <CLR red='0' blue='250'>300</CLR> |  {}".format(int(fieldValues[2]), fieldValues[0]) 
                    else:
                        pass  
                                   
                # clean up the list variables after completing the for loop    
                del sortedList, fieldValues  
            else:    
                pass
                # expression += '\n<FNT name="Arial" size="10"><BOL>Cross Street Count = 0</BOL></FNT>' 
        # return the label expression to display  
        return expression

     

     

    one thing to comment, i added import arcpy line at very first. Again, not sure your code should've worked without it but i had to add this, otherwise i was getting error "NameError: name 'arcpy' is not defined".

    thanks so much!

    Ryan

    BlakeTerhune
    MVP Regular Contributor

    @RyanOk This may be a labeling issue in ArcMap. Try the option to place overlapping labels and troubleshoot the issue from there. Is the content of all the labels correct? If so, your script is fine and the issue is with the dynamic labeling options.

    RyanOk
    by
    Occasional Explorer

    Hi Blake,

    I'm doing this work with ArcGIS Pro so not sure i can say it's an issue in ArcMap.

    I tried with the place overlapping option too but didn't get luck.

    And yes, it shows all contents what it supposed.

    then if the script is fine, this could be a bug i guess?

     

    BlakeTerhune
    MVP Regular Contributor

    Place overlapping labels—ArcGIS Pro | Documentation

    That doesn't show all the labels?

    Again, if the contents of the labels that do appear is correct, the issue is likely with your labeling scheme. You could contact Esri support.

    RyanOk
    by
    Occasional Explorer

    That unfortunately did not work. I will contact Esri support to see if they can help with this. 

    thanks Blake

    Labels