topic Re: One to many labeling in ArcGIS Enterprise Portal Questions

One to many labeling

NaseefChowdhury — Tue, 10 Sep 2013 19:51:06 GMT

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

Re: One to many labeling

RichardFairhurst — Wed, 11 Sep 2013 14:36:18 GMT

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.

Re: One to many labeling

RichardFairhurst — Wed, 11 Sep 2013 19:02:07 GMT

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.

Re: One to many labeling

NaseefChowdhury — Wed, 11 Sep 2013 20:04:32 GMT

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.

Re: One to many labeling

NaseefChowdhury — Sat, 11 Dec 2021 06:37:37 GMT

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

Re: One to many labeling

RichardFairhurst — Sat, 11 Dec 2021 06:37:40 GMT

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)

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)

Re: One to many labeling

NaseefChowdhury — Sat, 11 Dec 2021 06:37:43 GMT

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)

Re: One to many labeling

RichardFairhurst — Sat, 11 Dec 2021 06:37:46 GMT

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)

Re: One to many labeling

NaseefChowdhury — Wed, 11 Sep 2013 23:51:14 GMT

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.

Re: One to many labeling

RichardFairhurst — Wed, 11 Sep 2013 23:54:31 GMT

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)

Re: One to many labeling

RichardFairhurst — Thu, 12 Sep 2013 00:01:35 GMT

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.

Re: One to many labeling

JenniferHorsman2 — Sat, 11 Dec 2021 06:37:48 GMT

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

Re: One to many labeling

JenniferHorsman2 — Sat, 11 Dec 2021 06:37:51 GMT

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

Re: One to many labeling

RichardFairhurst — Sun, 12 Dec 2021 16:01:45 GMT

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

Re: One to many labeling

JoymaxNascimento — Sat, 11 Dec 2021 06:37:54 GMT

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

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

Re: One to many labeling

RichardFairhurst — Wed, 14 May 2014 15:36:14 GMT

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.

Re: One to many labeling

JoymaxNascimento — Wed, 14 May 2014 16:25:01 GMT

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.

Re: One to many labeling

RichardFairhurst — Sat, 11 Dec 2021 06:37:57 GMT

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

Re: One to many labeling

JoymaxNascimento — Sat, 11 Dec 2021 06:38:00 GMT

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

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

Re: One to many labeling

RichardFairhurst — Sat, 11 Dec 2021 06:38:02 GMT

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