nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Encuentra más publicaciones etiquetadas con

Accepted answers

RichardFairhurst

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.

All comments

JoymaxNascimento

Sorry for the delay, changed the code like this::

Function FindLabel ( [HOLEID] )

RelateFieldOfLayerValues = [HOLEID]

RelateFieldOfLayer = "HOLEID"

RelateFieldOfTable = "HOLEID"

strpTable = "DADO"

iMaxLbl1Sz = 0

iMaxLbl2Sz = 0

iMaxLbl3Sz =0

iMaxLbl4Sz = 0

iSpace = 3

Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")

strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = '" & RelateFieldOfLayerValues & "'"

Set prows = gp.searchcursor(strpTable,strWhereClause)

Set prow = prows.next

Do until prow is nothing

strLabel1 = prow.DE

strLabel2 = prow.INTERV

strLabel3 = prow.Fe_pct

strLabel4 = prow.GEO

If (Len(strLabel1) > iMaxLbl1Sz) Then

iMaxLbl1Sz = Len(strLabel1)

End If

If (Len(strLabel2) > iMaxLbl2Sz) Then

iMaxLbl2Sz = Len(strLabel2)

End If

If (Len(strLabel3) > iMaxLbl3Sz) Then

iMaxLbl3Sz = Len(strLabel3)

End If

If (Len(strLabel4) > iMaxLbl4Sz) Then

iMaxLbl4Sz = Len(strLabel4)

End If

Set prow = prows.next

Loop

Set prows = Nothing

if iMaxLbl1Sz + iMaxLbl2Sz + iMaxLbl3Sz + iMaxLbl4Sz + iSpace - Len(RelateFieldOfLayerValues) > 0 Then

'FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & String(14, "_") & vbnewline

FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & String(iMaxLbl1Sz + iMaxLbl2Sz + iMaxLbl3Sz + iMaxLbl4Sz + iSpace - Len(RelateFieldOfLayerValues), "_") & vbnewline

FindLabel = FindLabel & "<UND>" & "<CLR red='0' green='0' blue='255'>" & "|"& "FROM(m)"& "|"&"INT(m)"& vbtab & "|"&"Fe(%)" &"|"&"LIT"& String(iMaxLbl4Sz - Len("LIT"), "_") & "|"& "</CLR>"& "</UND>"& vbnewline

Else

FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & vbnewline

FindLabel = FindLabel & "<UND>" & "<CLR red='0' green='0' blue='255'>" & "|"& "FROM(m)"& "|"&"INT(m)"& vbtab & "|"&"Fe(%)" &"|"&"LIT"& "</CLR>"& "</UND>"& vbnewline

End If

Set prows = gp.searchcursor(strpTable,strWhereClause)

Set prow = prows.next

Do until prow is nothing

strLabel1 = prow.DE

strLabel2 = prow.INTERV

strLabel3 = prow.Fe_pct

strLabel4 = prow.GEO

k1 = iSpace + (iMaxLbl1Sz - Len(strLabel1))

k2 = iSpace + (iMaxLbl2Sz - Len(strLabel2))

k3 = iSpace + (iMaxLbl3Sz - Len(strLabel3))

k4 = iSpace + (iMaxLbl4Sz - Len(strLabel4)) -3

FindLabel = FindLabel & "|"

FindLabel = FindLabel & strLabel1 & "<CLR red='255' green='255' blue='210'>" & String(k1, ".") & "</CLR>"

FindLabel = FindLabel & "|"

FindLabel = FindLabel & strLabel2 & "<CLR red='255' green='255' blue='210'>" & String(k2, ".") & "</CLR>"

FindLabel = FindLabel & "|"

FindLabel = FindLabel & strLabel3 & "<CLR red='255' green='255' blue='210'>" & String(k3, ".") & "</CLR>"

FindLabel = FindLabel & "|"

FindLabel = FindLabel & "<CLR red='255' green='255' blue='210'>" & String(k4, ".") & "</CLR>" & strLabel4 & "|" &vbnewline

Set prow = prows.next

Loop

Set prows = Nothing

End Function

RichardFairhurst

I changed the font you suggested, but still with different tabs.
Masked name for confidentiality reasons.[ATTACH=CONFIG]33889[/ATTACH]

It looks like you have 4 fields in your layout. I would have to see your code for how you created those columns, since the code was not designed for more than 2 fields. I would not expect the 4 fields to align correctly without significant code modification. So are you now happy with what you got? It appears your first column needs more space to deal with all values being less than 4 characters long. Anyway, getting column headers and values to align was not part of the code I really changed or examined. It does not appear to have a sophisticated enough algorithm to automatically adapt to any set of value and column name lengths.

Actually, I knew the code does not create alignments of the column headers relative to the values, so I am not surprised by the results. I would like to see your code before trying to make the header value alignment algorithm better.

JoymaxNascimento

I changed the font you suggested, but still with different tabs.
Masked name for confidentiality reasons.[ATTACH=CONFIG]33889[/ATTACH]

Did you use a uniform width font like Lucidia Console? You could also try Courior, Courier New, Letter Gothic Std, OCR A Std, Orator Std, Prestige Elite Std, and Simplified Arabic Fixed. Only fonts where every letter is the same width will come close to working.

Fonts like Arial won't work, because the character widths vary too much. There is no way to control letter spacing of variable width character fonts to make a decent looking table.

If the font does not fix the problem I would need to see a screen shot of what you are seeing. If the header row width is longer than your value pairs the spacing algorithm would need to be adjusted slightly to account for the extra width.

Anyway, no matter what they probably won't be perfect. If you look carefully at my screen shot the vertical separators do vary slightly, but the variance is not enough to make the table layout fail. Since these labels remain dynamic the fact that they only achieve a reasonable table effect with minor imperfections is a decent trade off over other methods that may achieve perfect tables, but that require the labels to become static.

RichardFairhurst

Perfect, thank you.

If my responses have been helpful please up vote the posts that helped you out. See this link for more information.

RichardFairhurst

Perfect, thank you.
One more thing, is presented in table form? with rows and columns perfect?
Mine were not with correct alignment.

Did you use a uniform width font like Lucidia Console? You could also try Courior, Courier New, Letter Gothic Std, OCR A Std, Orator Std, Prestige Elite Std, and Simplified Arabic Fixed. Only fonts where every letter is the same width will come close to working.

Fonts like Arial won't work, because the character widths vary too much. There is no way to control letter spacing of variable width character fonts to make a decent looking table.

If the font does not fix the problem I would need to see a screen shot of what you are seeing. If the header row width is longer than your value pairs the spacing algorithm would need to be adjusted slightly to account for the extra width.

Anyway, no matter what they probably won't be perfect. If you look carefully at my screen shot the vertical separators do vary slightly, but the variance is not enough to make the table layout fail. Since these labels remain dynamic the fact that they only achieve a reasonable table effect with minor imperfections is a decent trade off over other methods that may achieve perfect tables, but that require the labels to become static.

JoymaxNascimento

Perfect, thank you.
One more thing, is presented in table form? with rows and columns perfect?
Mine were not with correct alignment.

Here is the code I use to build my labels. X_Y_LINK is a text field. There are no NULL values in any of the data. The related table named CL_INTERSECTIONS_PAIRS was already in my map. PAIR_STNAMES is a text field and INT_SPREAD is a double field in the related table. The inputs I changed to fit my data are shown in red.

I did modify the code slightly to handle an error that occurs if the header is longer than the detail value pairs or where there are no related records in the relate table. My previous test of my header line revision did not cover these possibilities. The revised lines that corrected the error are shown in bold green italics. The bold green italic lines did not need customization to fit the input data, they just needed to be added.

Function FindLabel ( [X_Y_LINK] )
  RelateFieldOfLayerValues = [X_Y_LINK]
  RelateFieldOfLayer = "X_Y_LINK"
  RelateFieldOfTable = "X_Y_LINK"
  strpTable = "CL_INTERSECTIONS_PAIRS"
  iMaxLbl1Sz = 0
  iMaxLbl2Sz = 0
  iSpace = 5
  Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")
  strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = '" & RelateFieldOfLayerValues & "'" 

  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.PAIR_STNAMES
  strLabel2 = prow.INT_SPREAD
  If (Len(strLabel1) > iMaxLbl1Sz) Then
    iMaxLbl1Sz = Len(strLabel1)
  End If
  If (Len(strLabel2) > iMaxLbl2Sz) Then
    iMaxLbl2Sz = Len(strLabel2)
  End If
  Set prow = prows.next
  Loop
  Set prows = Nothing

  if iMaxLbl1Sz + iMaxLbl2Sz + iSpace - Len(RelateFieldOfLayerValues) > 0 Then
    FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & String(iMaxLbl1Sz + iMaxLbl2Sz + iSpace - Len(RelateFieldOfLayerValues), "_") & vbnewline
  Else
 FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & vbnewline
 End If
  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.PAIR_STNAMES
  strLabel2 = prow.INT_SPREAD
  k1 = (iMaxLbl1Sz - Len(strLabel1)) + 2
  k2 = iSpace + (iMaxLbl2Sz - Len(strLabel2)) - 3
  FindLabel = FindLabel & strLabel1 & "<CLR red='255' green='255' blue='255'>" & String(k1, ".") & "</CLR>"
  FindLabel = FindLabel & "|"
  FindLabel = FindLabel & "<CLR red='255' green='255' blue='255'>" & String(k2, ".") & "</CLR>" & strLabel2 & vbnewline
  Set prow = prows.next
  Loop
  Set prows = Nothing
End Function

RichardFairhurst

Remains the same.
I can send you the. "Mdb" for you to check?

Here is the code I use to build my labels. X_Y_LINK is a text field. There are no NULL values in any of the data. The related table named CL_INTERSECTIONS_PAIRS was already in my map. PAIR_STNAMES is a text field and INT_SPREAD is a double field in the related table. The inputs I changed to fit my data are shown in red.

I did modify the code slightly to handle an error that occurs if the header is longer than the detail value pairs or where there are no related records in the relate table. My previous test of my header line revision did not cover these possibilities. The revised lines that corrected the error are shown in bold green italics. The bold green italic lines did not need customization to fit the input data, they just needed to be added.

Function FindLabel ( [X_Y_LINK] )
  RelateFieldOfLayerValues = [X_Y_LINK]
  RelateFieldOfLayer = "X_Y_LINK"
  RelateFieldOfTable = "X_Y_LINK"
  strpTable = "CL_INTERSECTIONS_PAIRS"
  iMaxLbl1Sz = 0
  iMaxLbl2Sz = 0
  iSpace = 5
  Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")
  strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = '" & RelateFieldOfLayerValues & "'" 

  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.PAIR_STNAMES
  strLabel2 = prow.INT_SPREAD
  If (Len(strLabel1) > iMaxLbl1Sz) Then
    iMaxLbl1Sz = Len(strLabel1)
  End If
  If (Len(strLabel2) > iMaxLbl2Sz) Then
    iMaxLbl2Sz = Len(strLabel2)
  End If
  Set prow = prows.next
  Loop
  Set prows = Nothing

  if iMaxLbl1Sz + iMaxLbl2Sz + iSpace - Len(RelateFieldOfLayerValues) > 0 Then
    FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & String(iMaxLbl1Sz + iMaxLbl2Sz + iSpace - Len(RelateFieldOfLayerValues), "_") & vbnewline
  Else
 FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & vbnewline
 End If
  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.PAIR_STNAMES
  strLabel2 = prow.INT_SPREAD
  k1 = (iMaxLbl1Sz - Len(strLabel1)) + 2
  k2 = iSpace + (iMaxLbl2Sz - Len(strLabel2)) - 3
  FindLabel = FindLabel & strLabel1 & "<CLR red='255' green='255' blue='255'>" & String(k1, ".") & "</CLR>"
  FindLabel = FindLabel & "|"
  FindLabel = FindLabel & "<CLR red='255' green='255' blue='255'>" & String(k2, ".") & "</CLR>" & strLabel2 & vbnewline
  Set prow = prows.next
  Loop
  Set prows = Nothing
End Function

JoymaxNascimento

Remains the same.
I can send you the. "Mdb" for you to check?

You have no basis for thinking the "where" is the problem. The code works when the inputs are correct, otherwise I could not have made the screenshot. The only problems are tied to the inputs you are supplying. Your path is likely the problem. The backslash is an escape character and not being interpreted as a backslash. You must make it a raw string to correct that.

strpTable = r"A:\Desktop\New Folder\New Folder\BNDS.mdb\TEOR"

You can reference a table that is already in your map without using any path and it will work. So add TEOR to the map and make the path:

strpTable = "TEOR"

You also did not make the two sets of field pairs identical. That is corrected below where I assumed you wanted ATE and GEO listed as paired values under the HOLEID header.
Function FindLabel ( [HOLEID] )
  RelateFieldOfLayerValues = [HOLEID]
  RelateFieldOfLayer = "HOLEID"
  RelateFieldOfTable = "HOLEID"
  strpTable = r"A:\Desktop\New Folder\New Folder\BNDS.mdb\TEOR"
iMaxLbl1Sz = 10
  iMaxLbl2Sz = 0
  iSpace = 8
  Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")
strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = '" & RelateFieldOfLayerValues & "'"

  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.ATE
  strLabel2 = prow.GEO
  If (Len(strLabel1) > iMaxLbl1Sz) Then
    iMaxLbl1Sz = Len(strLabel1)
  End If
  If (Len(strLabel2) > iMaxLbl2Sz) Then
    iMaxLbl2Sz = Len(strLabel2)
  End If
  Set prow = prows.next
  Loop
  Set prows = Nothing

  FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & String(iMaxLbl1Sz + iMaxLbl2Sz + iSpace - Len(RelateFieldOfLayerValues), "_") & vbnewline
  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.ATE
  strLabel2 = prow.GEO
  k1 = (iMaxLbl1Sz - Len(strLabel1)) + 2
  k2 = iSpace + (iMaxLbl2Sz - Len(strLabel2)) - 3
  FindLabel = FindLabel & strLabel1 & "<CLR red='0' green='0' blue='0'>" & String(k1, ".") & "</CLR>"
  FindLabel = FindLabel & "|"
  FindLabel = FindLabel & "<CLR red='0' green='0' blue='0'>" & String(k2, ".") & "</CLR>" & strLabel2 & vbnewline
  Set prow = prows.next
  Loop
  Set prows = Nothing
End Function

RichardFairhurst

It did not work.
Is to write the "where" directly?

You have no basis for thinking the "where" is the problem. The code works when the inputs are correct, otherwise I could not have made the screenshot. The only problems are tied to the inputs you are supplying. Your path is likely the problem. The backslash is an escape character and not being interpreted as a backslash. You must make it a raw string to correct that.

strpTable = r"A:\Desktop\New Folder\New Folder\BNDS.mdb\TEOR"

You can reference a table that is already in your map without using any path and it will work. So add TEOR to the map and make the path:

strpTable = "TEOR"

You also did not make the two sets of field pairs identical. That is corrected below where I assumed you wanted ATE and GEO listed as paired values under the HOLEID header.

Function FindLabel ( [HOLEID] )
  RelateFieldOfLayerValues = [HOLEID]
  RelateFieldOfLayer = "HOLEID"
  RelateFieldOfTable = "HOLEID"
  strpTable = r"A:\Desktop\New Folder\New Folder\BNDS.mdb\TEOR"
iMaxLbl1Sz = 10
  iMaxLbl2Sz = 0
  iSpace = 8
  Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")
strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = '" & RelateFieldOfLayerValues & "'"

  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.ATE
  strLabel2 = prow.GEO
  If (Len(strLabel1) > iMaxLbl1Sz) Then
    iMaxLbl1Sz = Len(strLabel1)
  End If
  If (Len(strLabel2) > iMaxLbl2Sz) Then
    iMaxLbl2Sz = Len(strLabel2)
  End If
  Set prow = prows.next
  Loop
  Set prows = Nothing

  FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & String(iMaxLbl1Sz + iMaxLbl2Sz + iSpace - Len(RelateFieldOfLayerValues), "_") & vbnewline
  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.ATE
  strLabel2 = prow.GEO
  k1 = (iMaxLbl1Sz - Len(strLabel1)) + 2
  k2 = iSpace + (iMaxLbl2Sz - Len(strLabel2)) - 3
  FindLabel = FindLabel & strLabel1 & "<CLR red='0' green='0' blue='0'>" & String(k1, ".") & "</CLR>"
  FindLabel = FindLabel & "|"
  FindLabel = FindLabel & "<CLR red='0' green='0' blue='0'>" & String(k2, ".") & "</CLR>" & strLabel2 & vbnewline
  Set prow = prows.next
  Loop
  Set prows = Nothing
End Function

JoymaxNascimento

It did not work.
Is to write the "where" directly?

The field names for the cursor are not supposed to be surrounded by brackets. (I would have put brackets in my example around the red text if I meant for you to enter a field name within brackets for the lines of code below)

Change this:

strLabel1 = prow.[HOLEID]
strLabel2 = prow.[ATE]

to this:

strLabel1 = prow.HOLEID
strLabel2 = prow.ATE

HOLEID should be a text field to work with this where clause and should not have Null values.

Function FindLabel ( [HOLEID] )
  RelateFieldOfLayerValues = [HOLEID]
  RelateFieldOfLayer = "HOLEID"
  RelateFieldOfTable = "HOLEID"
  strpTable = "A:\Desktop\New Folder\New Folder\BNDS.mdb\TEOR"
iMaxLbl1Sz = 10
  iMaxLbl2Sz = 0
  iSpace = 8
  Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")
strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = '" & RelateFieldOfLayerValues & "'"

  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.HOLEID
  strLabel2 = prow.ATE
  If (Len(strLabel1) > iMaxLbl1Sz) Then
    iMaxLbl1Sz = Len(strLabel1)
  End If
  If (Len(strLabel2) > iMaxLbl2Sz) Then
    iMaxLbl2Sz = Len(strLabel2)
  End If
  Set prow = prows.next
  Loop
  Set prows = Nothing

  FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & String(iMaxLbl1Sz + iMaxLbl2Sz + iSpace - Len(RelateFieldOfLayerValues), "_") & vbnewline
  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.HOLEID
  strLabel2 = prow.GEO
  k1 = (iMaxLbl1Sz - Len(strLabel1)) + 2
  k2 = iSpace + (iMaxLbl2Sz - Len(strLabel2)) - 3
  FindLabel = FindLabel & strLabel1 & "<CLR red='0' green='0' blue='0'>" & String(k1, ".") & "</CLR>"
  FindLabel = FindLabel & "|"
  FindLabel = FindLabel & "<CLR red='0' green='0' blue='0'>" & String(k2, ".") & "</CLR>" & strLabel2 & vbnewline
  Set prow = prows.next
  Loop
  Set prows = Nothing
End Function

RichardFairhurst

The field names for the cursor are not supposed to be surrounded by brackets. (I would have put brackets in my example around the red text if I meant for you to enter a field name within brackets for the lines of code below)

Change this:

strLabel1 = prow.[HOLEID]
strLabel2 = prow.[ATE]

to this:

strLabel1 = prow.HOLEID
strLabel2 = prow.ATE

Both sets of these lines of code have to be identical. You have to use ATE or both have to use GEO for the second field. Or else both sets could be:

strLabel1 = prow.ATE
strLabel2 = prow.GEO

HoleID will be included in the header line built by this line of code:

FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & String(iMaxLbl1Sz + iMaxLbl2Sz + iSpace - Len(RelateFieldOfLayerValues), "_") & vbnewline

HOLEID should be a text field to work with this where clause and should not have Null values.

Function FindLabel ( [HOLEID] )
  RelateFieldOfLayerValues = [HOLEID]
  RelateFieldOfLayer = "HOLEID"
  RelateFieldOfTable = "HOLEID"
  strpTable = "A:\Desktop\New Folder\New Folder\BNDS.mdb\TEOR"
iMaxLbl1Sz = 10
  iMaxLbl2Sz = 0
  iSpace = 8
  Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")
strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = '" & RelateFieldOfLayerValues & "'"

  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.ATE
  strLabel2 = prow.GEO
  If (Len(strLabel1) > iMaxLbl1Sz) Then
    iMaxLbl1Sz = Len(strLabel1)
  End If
  If (Len(strLabel2) > iMaxLbl2Sz) Then
    iMaxLbl2Sz = Len(strLabel2)
  End If
  Set prow = prows.next
  Loop
  Set prows = Nothing

  FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & String(iMaxLbl1Sz + iMaxLbl2Sz + iSpace - Len(RelateFieldOfLayerValues), "_") & vbnewline
  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.ATE
  strLabel2 = prow.GEO
  k1 = (iMaxLbl1Sz - Len(strLabel1)) + 2
  k2 = iSpace + (iMaxLbl2Sz - Len(strLabel2)) - 3
  FindLabel = FindLabel & strLabel1 & "<CLR red='0' green='0' blue='0'>" & String(k1, ".") & "</CLR>"
  FindLabel = FindLabel & "|"
  FindLabel = FindLabel & "<CLR red='0' green='0' blue='0'>" & String(k2, ".") & "</CLR>" & strLabel2 & vbnewline
  Set prow = prows.next
  Loop
  Set prows = Nothing
End Function

JoymaxNascimento

In my case, just does not work.
Unable enteder why.

Function FindLabel ( [HOLEID] )
RelateFieldOfLayerValues = [HOLEID]
RelateFieldOfLayer = "HOLEID"
RelateFieldOfTable = "HOLEID"
strpTable = "A:\Desktop\New Folder\New Folder\BNDS.mdb\TEOR"
iMaxLbl1Sz = 10
iMaxLbl2Sz = 0
iSpace = 8
Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")
strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = '" & RelateFieldOfLayerValues & "'"

Set prows = gp.searchcursor(strpTable,strWhereClause)
Set prow = prows.next
Do until prow is nothing
strLabel1 = prow.[HOLEID]
strLabel2 = prow.[ATE]
If (Len(strLabel1) > iMaxLbl1Sz) Then
iMaxLbl1Sz = Len(strLabel1)
End If
If (Len(strLabel2) > iMaxLbl2Sz) Then
iMaxLbl2Sz = Len(strLabel2)
End If
Set prow = prows.next
Loop
Set prows = Nothing

FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & String(iMaxLbl1Sz + iMaxLbl2Sz + iSpace - Len(RelateFieldOfLayerValues), "_") & vbnewline
Set prows = gp.searchcursor(strpTable,strWhereClause)
Set prow = prows.next
Do until prow is nothing
strLabel1 = prow.[HOLEID]
strLabel2 = prow.[GEO]
k1 = (iMaxLbl1Sz - Len(strLabel1)) + 2
k2 = iSpace + (iMaxLbl2Sz - Len(strLabel2)) - 3
FindLabel = FindLabel & strLabel1 & "<CLR red='0' green='0' blue='0'>" & String(k1, ".") & "</CLR>"
FindLabel = FindLabel & "|"
FindLabel = FindLabel & "<CLR red='0' green='0' blue='0'>" & String(k2, ".") & "</CLR>" & strLabel2 & vbnewline
Set prow = prows.next
Loop
Set prows = Nothing
End Function

Just returned is the [HOLEID].[ATTACH=CONFIG]33821[/ATTACH]

[ATTACH=CONFIG]0[/ATTACH]

The code assumed the related field was a string field, because single quotes are surrounding the value in the where clause variable.

strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = '" & RelateFieldOfLayerValues & "'"

If the relate field was actually a numeric field the code would have to be changed to remove the single quotes from the line of code that builds the where clause.

strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = " & RelateFieldOfLayerValues

If this does not answer your question, please copy the specific lines of code into your message that you are concerned about to better help me understand your question.

RichardFairhurst

How would the "where" in string type primary keys?

The code assumed the related field was a string field, because single quotes are surrounding the value in the where clause variable.

strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = '" & RelateFieldOfLayerValues & "'"

If the relate field was actually a numeric field the code would have to be changed to remove the single quotes from the line of code that builds the where clause.

strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = " & RelateFieldOfLayerValues

If this does not answer your question, please copy the specific lines of code into your message that you are concerned about to better help me understand your question.

JoymaxNascimento

How would the "where" in string type primary keys?

Jennifer:

Thanks for the code. I have modified it slightly to make it slightly easier to change the inputs of the script that need to be customized for user specific data by adding variables in the top lines of the Function. The field names still have to be modified in the body of the text. The inputs are shown in Red, Bold and Italics similar in your code.

I also modified the label style created by the code by eliminating the horizontal line inserted below the table header by using an underline of the header instead to reduce the amount of vertical space needed for each label. As you mentioned, the label font needs to have uniform spaced characters to work correctly.

Function FindLabel ( [RelateFieldOfLayer] )
  RelateFieldOfLayerValues = [RelateFieldOfLayer]
  RelateFieldOfLayer = "RelateFieldOfLayer"
  RelateFieldOfTable = "RelateFieldOfTable"
  strpTable = "PathToRelateTable"
  iMaxLbl1Sz = 0
  iMaxLbl2Sz = 0
  iSpace = 5
  Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")
  strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = '" & RelateFieldOfLayerValues & "'" 

  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.LabelField1
  strLabel2 = prow.LabelField2
  If (Len(strLabel1) > iMaxLbl1Sz) Then
    iMaxLbl1Sz = Len(strLabel1)
  End If
  If (Len(strLabel2) > iMaxLbl2Sz) Then
    iMaxLbl2Sz = Len(strLabel2)
  End If
  Set prow = prows.next
  Loop
  Set prows = Nothing

  FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & String(iMaxLbl1Sz + iMaxLbl2Sz + iSpace - Len(RelateFieldOfLayerValues), "_") & vbnewline
  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.LabelField1
  strLabel2 = prow.LabelField2
  k1 = (iMaxLbl1Sz - Len(strLabel1)) + 2
  k2 = iSpace + (iMaxLbl2Sz - Len(strLabel2)) - 3
  FindLabel = FindLabel & strLabel1 & "<CLR red='255' green='255' blue='255'>" & String(k1, ".") & "</CLR>"
  FindLabel = FindLabel & "|"
  FindLabel = FindLabel & "<CLR red='255' green='255' blue='255'>" & String(k2, ".") & "</CLR>" & strLabel2 & vbnewline
  Set prow = prows.next
  Loop
  Set prows = Nothing
End Function

RichardFairhurst

Jennifer:

Thanks for the code. I have modified it slightly to make it slightly easier to change the inputs of the script that need to be customized for user specific data by adding variables in the top lines of the Function. The field names still have to be modified in the body of the text. The inputs are shown in Red, Bold and Italics similar in your code.

I also modified the label style created by the code by eliminating the horizontal line inserted below the table header by using an underline of the header instead to reduce the amount of vertical space needed for each label. As you mentioned, the label font needs to have uniform spaced characters to work correctly.

Function FindLabel ( [RelateFieldOfLayer] )
  RelateFieldOfLayerValues = [RelateFieldOfLayer]
  RelateFieldOfLayer = "RelateFieldOfLayer"
  RelateFieldOfTable = "RelateFieldOfTable"
  strpTable = "PathToRelateTable"
  iMaxLbl1Sz = 0
  iMaxLbl2Sz = 0
  iSpace = 5
  Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")
  strWhereClause = chr(34) & RelateFieldOfTable & chr(34) & " = '" & RelateFieldOfLayerValues & "'" 

  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.LabelField1
  strLabel2 = prow.LabelField2
  If (Len(strLabel1) > iMaxLbl1Sz) Then
    iMaxLbl1Sz = Len(strLabel1)
  End If
  If (Len(strLabel2) > iMaxLbl2Sz) Then
    iMaxLbl2Sz = Len(strLabel2)
  End If
  Set prow = prows.next
  Loop
  Set prows = Nothing

  FindLabel = FindLabel & "<UND>" & RelateFieldOfLayerValues & "</UND>" & String(iMaxLbl1Sz + iMaxLbl2Sz + iSpace - Len(RelateFieldOfLayerValues), "_") & vbnewline
  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.LabelField1
  strLabel2 = prow.LabelField2
  k1 = (iMaxLbl1Sz - Len(strLabel1)) + 2
  k2 = iSpace + (iMaxLbl2Sz - Len(strLabel2)) - 3
  FindLabel = FindLabel & strLabel1 & "<CLR red='255' green='255' blue='255'>" & String(k1, ".") & "</CLR>"
  FindLabel = FindLabel & "|"
  FindLabel = FindLabel & "<CLR red='255' green='255' blue='255'>" & String(k2, ".") & "</CLR>" & strLabel2 & vbnewline
  Set prow = prows.next
  Loop
  Set prows = Nothing
End Function

OneToManyTableLabels.jpg

JenniferHorsman2

Here is another example that looks even more like a table. The text in bold italics must be modified to suit your data.
[ATTACH=CONFIG]29762[/ATTACH]
Again, I used a .csv file as my relate table.

Function FindLabel ( [RelateFieldOfLayer] )
  iMaxLbl1Sz = 0
  iMaxLbl2Sz = 0
  iSpace = 5
  Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")
  strWhereClause = chr(34) & "RelateFieldOfTable" & chr(34) & " = '" & [RelateFieldOfLayer] & "'" 
  strpTable = "PathToRelateTable"

  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.LabelField1
  strLabel2 = prow.LabelField2
  If (Len(strLabel1) > iMaxLbl1Sz) Then
    iMaxLbl1Sz = Len(strLabel1)
  End If
  If (Len(strLabel2) > iMaxLbl2Sz) Then
    iMaxLbl2Sz = Len(strLabel2)
  End If
  Set prow = prows.next
  Loop

  FindLabel = FindLabel & "<FNT name='Arial' size='6.25'>" & [RelateFieldOfLayer] & "</FNT>" & vbnewline
  FindLabel = FindLabel & String(iMaxLbl1Sz + iMaxLbl2Sz + iSpace, "_") & vbnewline
  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.LabelField1
  strLabel2 = prow.LabelField2
  k1 = (iMaxLbl1Sz - Len(strLabel1)) + 2
  k2 = iSpace + (iMaxLbl2Sz - Len(strLabel2)) - 3
  FindLabel = FindLabel & strLabel1 & "<CLR red='255' green='255' blue='255'>" & String(k1, ".") & "</CLR>"
  FindLabel = FindLabel & "|"
  FindLabel = FindLabel & "<CLR red='255' green='255' blue='255'>" & String(k2, ".") & "</CLR>" & strLabel2 & vbnewline
  Set prow = prows.next
  Loop
End Function

DawsonCoveChemistry.jpg

JenniferHorsman2

Here is some vbscript code I created to label using related (one-to-many) tables based on tips found here: http://www.esri.com/news/arcuser/1104/files/vbscript_label.pdf and here http://gisnuts.com/terra/blog/2012/05/16/label-features-with-attributes-from-related-records.
You must change the items in italics to suit your datasets. My relate table happened to be a .csv file. And I used a font (Lucida Console) in which every character is exactly the same width so that my numbers (see pic) would line up on the right.

Function FindLabel ( [RelateField] )
  iMaxLbl1Sz = 0
  iMaxLbl2Sz = 0
  iSpace = 3
  Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")
  strWhereClause = chr(34) & "RelateFieldinTable" & chr(34) & " = '" & [RelateField] & "'" 
  strpTable = "path to your relate table"
  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.Depths
  strLabel2 = prow.TotalPAHs
  If (Len(strLabel1) > iMaxLbl1Sz) Then
    iMaxLbl1Sz = Len(strLabel1)
  End If
  If (Len(strLabel2) > iMaxLbl2Sz) Then
    iMaxLbl2Sz = Len(strLabel2)
  End If
  Set prow = prows.next
  Loop  FindLabel = "<UND>" & FindLabel & [RelateField] & "</UND>" & vbnewline
  Set prows = gp.searchcursor(strpTable,strWhereClause)
  Set prow = prows.next
  Do until prow is nothing
  strLabel1 = prow.Depths
  strLabel2 = prow.TotalPAHs
  k = (iMaxLbl1Sz - Len(strLabel1)) + iSpace + (iMaxLbl2Sz - Len(strLabel2))
  FindLabel = FindLabel & strLabel1 & String(k, ".") & strLabel2 & vbnewline
  Set prow = prows.next
  Loop
End Function

RichardFairhurst

Here is the second shot just to show it could deal with a lot of labels. Please assign points to my posts by pressing the up arrow on the right side of each post above the number.

LabelExample3.jpg

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)

LabelExample2.jpg

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

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

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

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

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""?

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.

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.

LabelExample.jpg

RichardFairhurst

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.

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.