One to many labeling

[NaseefChowdhury]

I know this is something we have been asking for forever, but I do not know if there is a real solution for this yet? Did anyone ever manage to recreate the "One to Many Label" script from VB6? Did ESRI finally give us a real solution to do the labeling? I saw one option of doing it using pivot tables that would require ArcInfo license, but that is a very roundabout solution using a license most people don't have. If anyone knows of a working solution please let me know.

If you don't know what I am talking about, basically I am looking to make "callout box" type of labels such as:

MW01   DEPTH   BENZENE   XYLENE
           12        34            0.78
           18        102           9

[RichardFairhurst · Accepted solution]

I know this is something we have been asking for forever, but I do not know if there is a real solution for this yet? Did anyone ever manage to recreate the "One to Many Label" script from VB6? Did ESRI finally give us a real solution to do the labeling? I saw one option of doing it using pivot tables that would require ArcInfo license, but that is a very roundabout solution using a license most people don't have. If anyone knows of a working solution please let me know.

If you don't know what I am talking about, basically I am looking to make "callout box" type of labels such as:

MW01   DEPTH   BENZENE   XYLENE
           12        34            0.78
           18        102           9

I have not updated the VB6 script, but have created a Python script that can be used in an Advanced Label expression using the Python parser.  As written, thin code is designed for version 10.1 or later, since it used the data access module.  It could be adapted to work with 10.0 cursors, but I do not think Python labels are supported prior to 10.0.

At this point I have not attempting to develop a label anywhere near the complexity of the one you want.  That will come later.  But for now I have developed the core fundamental script that will build a simpler one-to-many relationship stacked label.  The code below will create a label that lists the input value of the relate field from the parent table and then stacks underneath it the values of another field in a related table from all of the records that matched the input value in the related table's relate field.

import arcpy # variables that need to be customized for your specific data def FindLabel ( [FULL_NAME] ): # input the field name of the parent table's relate field   inputValue = [FULL_NAME] # repeat the parent table's relate field   queryValue = "'" + inputValue + "'" # add query value delimiters for strings or dates here   # define the data source path and fields of the related table   featureClass = r'\\agency\agencydfs\Trans\rfairhur\Layers\PARCEL_LINES.gdb\ADDRESS_POINT_Locate_JURUP'   relatedFieldName = "FULL_NAME" # the relate field name in the related table that corresponds to the input field   valueFieldName = "HOUSE_NUMBER" # desired label value field name in the related table    # As long as you want a simple single field stacked label you should not need to change this code   whereArgs = [arcpy.AddFieldDelimiters(featureClass, relatedFieldName), queryValue]   whereClause = "%s = %s" % tuple(whereArgs)   labelList = []   rows = arcpy.da.SearchCursor(featureClass, (relatedFieldName, valueFieldName), whereClause)   for row in rows:     labelList.append(row[1])   labelList.sort() # uses correct sort order for numbers, dates or strings   labelList.insert(0, inputValue) # insert input value as a heading   return "\n".join(str(labelvalue) for labelvalue in labelList)

Just as with the VB6 program, the labels take longer to build than labels based on fields from a single table.  Python has several advantages over VB6 or any other form of VB.  It can easily sort lists with correct sort orders for the value type contained in the list (numeric, date or string).  And the Python data access cursors are faster than the cursors used with VB6.

So this fundamental script can now be adapted to develop more sophisticated labels and multi-field labels.  There are several challenges to doing a label as complex as the one you want, so I first wanted to develop a proof of concept script before trying to tackle those challenges.

[RichardFairhurst]

Here is a screen shot of the output where the street name is labeled with all of the House Numbers that occurred on that street in numeric sorted order.  The labels are not optimized for prettiness or presentation.  They just demonstrate that the code above created labels that used the road name values from the displayed lines and matched them to the house numbers from a related table to generate a label.

Obviously there are better ways to present house numbers, such a min, max, count and average rather than labeling with the entire house number list, but that is formatting and not really relavant to the question about whether there is an alternative way to generate one-to-many relationship labels.

[NaseefChowdhury]

That code works brilliantly and is a perfect start. Now I just need to figure out how to make the label a little more complicated rather than just stack lines. Going on work on figuring this out myself, but I will probably need your help if you are still willing. Thanks a lot for what you have already done.

[NaseefChowdhury]

Finally got it to work to look more like what I want, although running into a resource problem which is causing ArcMap to crash.

This code works, and does what I need it to do to display two fields instead of just the one

import arcpy
# variables that need to be customized for your specific data
def FindLabel ( [SampleName] ): # input the field name of the parent table's relate field
  inputValue = [SampleName] # repeat the parent table's relate field
  # define the data source path and fields of the related table
  featureClass = r'Chromium_Data'
  relatedFieldName = "sampleid" # the relate field name in the related table that corresponds to the input field
  valueFieldName = "depth" # desired label value field name in the related table
  valueFieldName2 = "result" # second field to label
  # As long as you want a simple single field stacked label you should not need to change this code
  whereArgs = [arcpy.AddFieldDelimiters(featureClass, relatedFieldName), "'" + inputValue + "'"]
  whereClause = "%s = %s" % tuple(whereArgs)
  labelList = []
  rows = arcpy.da.SearchCursor(featureClass, (relatedFieldName, valueFieldName, valueFieldName2), whereClause)
  for row in rows:
    finalString = (str(row[1]) + ": " + str(row[2])) # combining the label fields
    labelList.append(finalString)
  labelList.sort() # uses correct sort order for numbers, dates or strings
  labelList.insert(0, inputValue) # insert input value as a heading
  return "\n".join(str(labelvalue) for labelvalue in labelList)

This code theoretically works using 3 label fields, the code verification process displays the correct result, but the processing is too much and causes ArcMap to crash.

import arcpy
# variables that need to be customized for your specific data
def FindLabel ( [SampleName] ): # input the field name of the parent table's relate field
  inputValue = [SampleName] # repeat the parent table's relate field
  # define the data source path and fields of the related table
  featureClass = r'Chromium_Data'
  relatedFieldName = "sampleid" # the relate field name in the related table that corresponds to the input field
  valueFieldName = "param" # desired label value field name in the related table
  valueFieldName2 = "depth" # second field to label
  valueFieldName3 = "result" # third field to label
  # As long as you want a simple single field stacked label you should not need to change this code
  whereArgs = [arcpy.AddFieldDelimiters(featureClass, relatedFieldName), "'" + inputValue + "'"]
  whereClause = "%s = %s" % tuple(whereArgs)
  labelList = []
  rows = arcpy.da.SearchCursor(featureClass, (relatedFieldName, valueFieldName, valueFieldName2, valueFieldName3), whereClause)
  for row in rows:
    finalString = (str(row[1]) + " - " + str(row[2]) + " - " + str(row[3])) # combining the label fields
    labelList.append(finalString)
  labelList.sort() # uses correct sort order for numbers, dates or strings
  labelList.insert(0, inputValue) # insert input value as a heading
  return "\n".join(str(labelvalue) for labelvalue in labelList)

It would be nice if ArcMap could handle this by itself, but I could work around it by pre-concatnating my 3 fields into one within the database. Of course editing the database is not an ideal solution, but at least we got a working solution.

Side question, do you know if there is a way to run a SQL statement within the code to filter the results, such as only displaying results where "valueFieldName3 = "desiredValue""?

[RichardFairhurst]

Finally got it to work to look more like what I want, although running into a resource problem which is causing ArcMap to crash.

This code works, and does what I need it to do to display two fields instead of just the one

import arcpy
# variables that need to be customized for your specific data
def FindLabel ( [SampleName] ): # input the field name of the parent table's relate field
  inputValue = [SampleName] # repeat the parent table's relate field
  # define the data source path and fields of the related table
  featureClass = r'Chromium_Data'
  relatedFieldName = "sampleid" # the relate field name in the related table that corresponds to the input field
  valueFieldName = "depth" # desired label value field name in the related table
  valueFieldName2 = "result" # second field to label
  # As long as you want a simple single field stacked label you should not need to change this code
  whereArgs = [arcpy.AddFieldDelimiters(featureClass, relatedFieldName), "'" + inputValue + "'"]
  whereClause = "%s = %s" % tuple(whereArgs)
  labelList = []
  rows = arcpy.da.SearchCursor(featureClass, (relatedFieldName, valueFieldName, valueFieldName2), whereClause)
  for row in rows:
    finalString = (str(row[1]) + ": " + str(row[2])) # combining the label fields
    labelList.append(finalString)
  labelList.sort() # uses correct sort order for numbers, dates or strings
  labelList.insert(0, inputValue) # insert input value as a heading
  return "\n".join(str(labelvalue) for labelvalue in labelList)

This code theoretically works using 3 label fields, the code verification process displays the correct result, but the processing is too much and causes ArcMap to crash.

import arcpy
# variables that need to be customized for your specific data
def FindLabel ( [SampleName] ): # input the field name of the parent table's relate field
  inputValue = [SampleName] # repeat the parent table's relate field
  # define the data source path and fields of the related table
  featureClass = r'Chromium_Data'
  relatedFieldName = "sampleid" # the relate field name in the related table that corresponds to the input field
  valueFieldName = "param" # desired label value field name in the related table
  valueFieldName2 = "depth" # second field to label
  valueFieldName3 = "result" # third field to label
  # As long as you want a simple single field stacked label you should not need to change this code
  whereArgs = [arcpy.AddFieldDelimiters(featureClass, relatedFieldName), "'" + inputValue + "'"]
  whereClause = "%s = %s" % tuple(whereArgs)
  labelList = []
  rows = arcpy.da.SearchCursor(featureClass, (relatedFieldName, valueFieldName, valueFieldName2, valueFieldName3), whereClause)
  for row in rows:
    finalString = (str(row[1]) + " - " + str(row[2]) + " - " + str(row[3])) # combining the label fields
    labelList.append(finalString)
  labelList.sort() # uses correct sort order for numbers, dates or strings
  labelList.insert(0, inputValue) # insert input value as a heading
  return "\n".join(str(labelvalue) for labelvalue in labelList)

It would be nice if ArcMap could handle this by itself, but I could work around it by pre-concatnating my 3 fields into one within the database. Of course editing the database is not an ideal solution, but at least we got a working solution.

Side question, do you know if there is a way to run a SQL statement within the code to filter the results, such as only displaying results where "valueFieldName3 = "desiredValue""?

I am curious.  Did you try limiting the visible scale of the labels to see if that has any influence on ArcMap crashing or not?

I am not clear on what you mean in your last paragraph.  If there are two criteria field values in the parent table that give the desired value for field 3 then that would be a modification to the def to include 2 fields separated by commas and constructing a whereclause with an AND condition.  So it that what you want?  Or is the desired value hard coded, i.e. all values > 0?

Something like:

def FindLabel ( [SampleName], [SampleName3] 😞
...
  whereArgs = [arcpy.AddFieldDelimiters(featureClass, relatedFieldName), "'" + inputValue + "'", arcpy.AddFieldDelimiters(featureClass, valueFieldName3), "'" + [SampleName3] + "'"]
  whereClause = "%s = %s AND %s = %s" % tuple(whereArgs)

Also probably you need to include del statements.  I.e.:

    del row
  del rows # delete cursor after building the list to free resources
  labelList.sort() # uses correct sort order for numbers, dates or strings
  labelList.insert(0, inputValue) # insert input value as a heading
  return "\n".join(str(labelvalue) for labelvalue in labelList)

[NaseefChowdhury]

Ah, interesting, zooming in did take prevent the crash. Didn't think that would matter since the labels would have to be created anyway. I am still a little hesitant that it might crash the system anytime but it does work.

For the sql statement, I just wanted a data filter, which I worked around by putting it in the code with an if statement instead of using a sql clause

  for row in rows:
    if row[1] == "CHROMIUM": # data filter
      finalString = (str(row[1]) + ": " + str(row[2]) + ": " + str(row[3])) # combining the label fields
      labelList.append(finalString)

[RichardFairhurst]

Ah, interesting, zooming in did take prevent the crash. Didn't think that would matter since the labels would have to be created anyway. I am still a little hesitant that it might crash the system anytime but it does work.

For the sql statement, I just wanted a data filter, which I worked around by putting it in the code with an if statement instead of using a sql clause

  for row in rows:
    if row[1] == "CHROMIUM": # data filter
      finalString = (str(row[1]) + ": " + str(row[2]) + ": " + str(row[3])) # combining the label fields
      labelList.append(finalString)

You are wrong.  It only processes labels on features in the visible extent, so the list is reduced by zooming in.  Also you want that filter in the SQL, since the number of rows returned affects the resources used.  But I think the real resource issue is that I did not include the del statements.  So add those and then zoom out.

import arcpy
# variables that need to be customized for your specific data
def FindLabel ( [SampleName] ): # input the field name of the parent table's relate field
  inputValue = [SampleName] # repeat the parent table's relate field
  # define the data source path and fields of the related table
  featureClass = r'Chromium_Data'
  relatedFieldName = "sampleid" # the relate field name in the related table that corresponds to the input field
  valueFieldName = "param" # desired label value field name in the related table
  valueFieldName2 = "depth" # second field to label
  valueFieldName3 = "result" # third field to label
  # As long as you want a simple single field stacked label you should not need to change this code
  whereArgs = [arcpy.AddFieldDelimiters(featureClass, relatedFieldName), "'" + inputValue + "'", arcpy.AddFieldDelimiters(featureClass, valueFieldName), "'CHROMIUM'"]
  whereClause = "%s = %s AND %s = %s" % tuple(whereArgs)
  labelList = []
  rows = arcpy.da.SearchCursor(featureClass, (relatedFieldName, valueFieldName, valueFieldName2, valueFieldName3), whereClause)
  for row in rows:
    finalString = (str(row[1]) + " - " + str(row[2]) + " - " + str(row[3])) # combining the label fields
    del row
    labelList.append(finalString)
  del rows
  labelList.sort() # uses correct sort order for numbers, dates or strings
  labelList.insert(0, inputValue) # insert input value as a heading
  return "\n".join(str(labelvalue) for labelvalue in labelList)

[NaseefChowdhury]

Yeah I figured it was not good coding to put that if statement in there but it is what I could do with my limited knowledge 😞

I think the del is working well and it now loads all the labels fine.

There is something wrong with your SQL statement though, that code returns an error and I only got it to work by removing the new data filter you added in. An error about not being able to convert all arguments during string converting.

Thanks again for all your efforts.

[RichardFairhurst]

You should set a visible scale range limit on the label properties so that if you zoom out too far the labels will not be displayed.   Also I zoomed out to see what would happen without a visible scale range after adding the del statements.  Here is the result at 2 different scale ranges to generate a large set of labels.  It took a long time to draw, especially for the second example, but it did not crash.

The error was my fault for not editing this line:

  whereClause = "%s = %s" % tuple(whereArgs)

it should be:

  whereClause = "%s = %s AND %s = %s" % tuple(whereArgs)