topic Re: label related table - in one staked label in Transportation Questions

label related table - in one staked label

Anonymous User — Thu, 06 Dec 2012 20:57:32 GMT

Original User: alnesbit

Hello all,

I am hoping someone who knows python can figure this out. I have a similar posting here http://forums.arcgis.com/threads/68883-Related-table-labels . There was an old arcscripts that worked at 9.3 here http://arcscripts.esri.com/details.asp?dbid=12298

I have been able to create a new feature class from using the Make Query Table tool that has all of my related values for my points in one feature class. What I really need know is a way to create one balloon label for each point that is a staked label of all of the related table values. Can I do this with python code or a label expression somehow? In some cases I have 6 or more balloon labels for each of my points and they are overlapping and hard to read.

I am attaching a screen shot of my map book as it is labeled with the many labels for each point. Then I am showing the table that the labels are coming from - showing the many assemblies for each pole.

The last thing I will add is an idea of python labeling code. This python label expression doesn't work but I thought maybe someone out there who knew code better than me could get it to work.

def FindLabel ( [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT], [POLE_PRIKEY]  ):   if long( [POLE_PRIKEY] ) = long( [POLE_PRIKEY] ):      return [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT] + '\n' + [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT] + '\n'  + [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT]    else:      return [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT]

I appreciate any help!
Thank you,

Re: label related table - in one staked label

MathewCoyle — Sat, 11 Dec 2021 18:54:26 GMT

I'm not sure I understand what you are trying to do with this line (besides you needing == isntead of =).

if long( [POLE_PRIKEY] ) = long( [POLE_PRIKEY] ):

Something like this should work though.

def FindLabel([FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT], [POLE_PRIKEY]):
    if long( [POLE_PRIKEY] ) == long( [POLE_PRIKEY] ):
        labelText = '{0}\n{1}\n{2}'.format([FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT], [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT], [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT])
        return labelText
    else:
        labelText = [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT]
        return labelText

In terms of accessing the data from a relate, I think you will need to look into using a join.

Re: label related table - in one staked label

Anonymous User — Fri, 07 Dec 2012 12:01:45 GMT

Original User: alnesbit

Mathew,

Thank you for the response. Your code did change my labels but not exactly what I was looking for. I will explain a little bit more about what I'm trying to do.

I think I need some sort of loop through the attribute table to find when the Pole.Prikey is the same, those are the assembly units I want labeled all together in one balloon. We ended up just making 3 lines of the same label. I will attach a screen shot. So for example, in the first screen shot of my attribute table I would like 204, PM2A, HAFO (4-10T), PF3-3, PM11, and PE1-2 all together staked in one balloon label.

I will attach a screen shot of more of my feature class attribute table. In the previous posting I only showed one selected pole.

And to give some background, I started out with a point feature class that had a related table of 1-M Pole.Prikey to Assembly units. Then after I used the Make Query Table tool (in ArcToolbox) to create a 1-M join, I got this new feature class which is a 1-M join. It's a pretty slick advancement for 10 or 10.1. Talked about here http://blogs.esri.com/esri/supportcenter/2012/05/10/a-quick-tip-on-performing-a-1m-join/ So now I'm just trying to label this new table in the best way.

Thank you. I appreciate any help,

Re: label related table - in one staked label

MathewCoyle — Mon, 10 Dec 2012 14:19:00 GMT

There is no way I can think of to reference multiple rows as a single label using label expressions. You might be able to get around it by having a separate script that creates the labels in a text file and then the label expression references that. I can't imagine it would be very good performance wise though, especially if there are a lot of features. A better way may be to create a table that has a single field for each label line. So for our sample row field1 = 204, field2 = PM2A, field 3 = HAFO (4-10T) etc. Depending on how your data is set up you could try pivot tables
http://resources.arcgis.com/en/help/main/10.1/index.html#//0017000000n8000000

Either way you'd probably want to create an annotation layer to reference instead of dynamically labelling each time.

Re: label related table - in one staked label

Anonymous User — Mon, 10 Dec 2012 14:30:14 GMT

Original User: alnesbit

There is no way I can think of to reference multiple rows as a single label using label expressions.

Ok I was afraid that was the case. Yes having my table set up differently, like you mentioned, would allow me to label then. I don't know of a fast way to get the table in that form, other than manually calculating the new fields one pole at a time. There are a lot of point features so that might be a crazy task. I'll try the pivot table link and see if that helps. I'll just hope that sometime in the future someone can come up with some new code to do this.

Thanks,

Re: label related table - in one staked label

MathewCoyle — Mon, 10 Dec 2012 14:37:45 GMT

I also came across this method. Seems like a similar issue. It's a process you'd probably want to automate as well if your labels ever change.

http://forums.arcgis.com/threads/4675-Stacking-labels-multiple-records-single-point-two-attributes?p=13957&viewfull=1#post13957

Re: label related table - in one staked label

MathewCoyle — Sat, 11 Dec 2021 18:54:29 GMT

I possibly found a way to do it. Not all that fast though. You'll have to change all the field names to your data. It will return a label for every feature populated by every item in your target field that match the key field value.

def FindLabel([QS], [TWP]):
    val_list = []
    keyField = "TWP"
    keyVal = [TWP]
    field = "QS"
    fieldVal = [QS]
    layer = "ats"
    with arcpy.da.SearchCursor(
            layer, field, "{0} = {1}".format(keyField, keyVal)) as cursor:
        for row in cursor:
            val1 = row[0]
            val_list.append(val1)
    returnVal = "\n".join(val_list)

    return returnVal

Re: label related table - in one staked label

Anonymous User — Sat, 11 Dec 2021 18:54:32 GMT

Original User: alnesbit

I possibly found a way to do it. Not all that fast though. You'll have to change all the field names to your data. It will return a label for every feature populated by every item in your target field that match the key field value.

Well I changed the code to match my data but we still have the problem of it labeling for each line of the attribute table - not just giving me one label per Pole.Prikey. This is the code I used:

def FindLabel( [POLE_PRIKEY] , [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT] ):
    val_list = []
    keyField = "POLE_PRIKEY"
    keyVal = [POLE_PRIKEY] 
    field = "FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT"
    fieldVal = [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT]
    layer = "TEST"
    with arcpy.da.SearchCursor(
            layer, field, "{0} = {1}".format(keyField, keyVal)) as cursor:
        for row in cursor:
            val1 = row[0]
            val_list.append(val1)
    returnVal = "\n".join(val_list)

    return returnVal

I will attach a screen shot of the labels as well.

But I am having some success with your other post and link to the other forum http://forums.arcgis.com/threads/4675-Stacking-labels-multiple-records-single-point-two-attributes . I hadn't seen that forum posting before and it's an interesting idea that I am tweeking and working with. If I get it to work I will let you know. I'm really close to getting it.

What's stopping me so far is an automated way to Delete Identical. I know there is an awesome new tool at 10 but I only have ArcEditor, not ArcInfo as required for that tool. Would you know of a way to Delete Identical without having ArcInfo?

Re: label related table - in one staked label

MathewCoyle — Mon, 10 Dec 2012 18:04:56 GMT

In the label placement properties did you try checking "remove duplicates"?

Here is a link to finding duplicates without ArcInfo. You would then need to delete or export non-identical features to a new table.
http://forums.arcgis.com/threads/4021-ArcGIS-10-Pre-Release-finding-duplicates?p=12085&viewfull=1#post12085

Re: label related table - in one staked label

Anonymous User — Mon, 10 Dec 2012 19:48:36 GMT

Original User: alnesbit

Mathew,

I got it! Thank you so much for all of your help! Here is my workflow - it's crazy but it works. (Oh and that code you gave me that I tried did work after I said to Remove Duplicates labels, however, once I put it to the test on my whole data, not just a 2 pole subset, it was labeling and labeling and...so you were right, it was going to take too long. If I let it label all day it might have worked.)

I select out a subset of my data - a service area, and export it to a new feature class naming it "Data1". Then I export it again, the same thing, and name it "Data2".

Then I use the Spatial Join tool in the toolbox and follow the steps explained by Chris Fox in the forum you mentioned http://forums.arcgis.com/threads/4675-Stacking-labels-multiple-records-single-point-two-attributes . I use my Data1 and my Data2 feature classes, my merge rule is Join, my delimiter is a comma and at the bottom I picked "Are Identical To". That gives me a new feature class which I named "Data_spj".

Then in Data_spj I delete duplicate POLE.PRIKEY records by using the python field calculator code explained by Kent Marten here http://forums.arcgis.com/threads/4021-ArcGIS-10-Pre-Release-finding-duplicates and in the Knowledge Base Article 38700.

That gives me my feature class with one field for labeling. I use Maplex to label staked labels force split at comma, no split at space, constrain to left, minimum characters per line 7, max number of lines 8, place overlapping, reduce font size, offset 10, allow labels to overlap border.

I'm using data driven pages so I will add the rest of my steps in case anyone can use it. I add a new field to my Data_spj feature class called "Grid" and do a spatial join to get the Grid that each point is in into that field. Then I go to Layers - Properties - Data Frame - Clip options - Clip to current Data driven page extent. Exclude layers - my grid and my "Data_spj". Then go to the Properties of my "Data_spj" feature class - Definition Query - click on Page Definition - Enable - Page Name field "Grid", Show features that Match.

So after all of that, I get the perfect labels. Screen shot attached. Hope this helps someone in the future.

Thanks again!!

Re: label related table - in one staked label

TimSexton1 — Fri, 10 Oct 2014 19:57:17 GMT

I know this is old, but I came across this link that helped me label from a related table with an advanced expression.

Re: label related table - in one staked label

Anonymous User — Mon, 20 Apr 2015 20:01:27 GMT

Tim,

I tried the code you linked to but it doesn't work for me. Any idea why?

Function FindLabel ( [UniqueID] )

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

strWhereClause = chr(34) & "UniqueID" & chr(34) & " = '" & [UniqueID] & "'"

strpTable = C:\Users\abohn\Documents\RALLS_TEST.gdb\FIBER_ASSEMBLY_UNITS_AEG_gdb

Set prows = gp.searchcursor(strpTable,strWhereClause)

Set prow = prows.next

Do until prow is nothing

strLabel = prow.ASSEBMLY_UNIT

FindLabel = FindLabel & strLabel & vbnewline

Set prow = prows.next

Loop

End Function

Re: label related table - in one staked label

TimSexton1 — Mon, 20 Apr 2015 20:18:27 GMT

The related table has to be added to the TOC. Make sure you are setting your strpTable variable to the actual table name (without the GDB path). I could not get it to work the way your are trying by using the full file path to the table. Also make sure the whereclause is querying on the correct foreign key (in your code it looks like your primary key and foreign key are named the same - which may be the case, just verify). Also make sure the "Advanced - VBscript" expression is used.

Sample code:

Function FindLabel ( [GlobalID] )

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

strWhereClause = "OperationGUID = '" & [GlobalID] & "'"

strpTable = "RelatedTableNameHere"

Set prows = gp.searchcursor(strpTable,strWhereClause)

Set prow = prows.next

Do until prow is nothing

strLabel = prow.Description

FindLabel = FindLabel & strLabel & vbnewline

Set prow = prows.next

Loop

End Function

Hope this helps!

Re: label related table - in one staked label

Anonymous User — Tue, 21 Apr 2015 16:05:11 GMT

Awesome! It works!! I can't believe it! So cool.

I didn't have quotes around my "PathToRelatedTable" so that works now and I don't have to have the table in my TOC then. But in production I actually want to use an SDE table so I will have that in my TOC so I will just use the "RelatedTableName" like you mentioned. Thank you!

I have a further question though. In my code below I added a Quantity field to my label. But when the Quantity is 1 I don't want it to put the "(1)" there. I just want the "(2)" quantity label when the Quantity > 1. Do you know how to add in this logic?

Code:

Function FindLabel ( [UniqueID] )

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

strWhereClause = chr(34) & "UniqueID" & chr(34) & " = '" & [UniqueID] & "'"

strpTable = "FIBER_ASSEMBLY_UNITS"

Set prows = gp.searchcursor(strpTable,strWhereClause)

Set prow = prows.next

Do until prow is nothing

strLabel = prow.ASSEMBLY_UNIT & " (" & prow.Quantity & ")"

FindLabel = FindLabel & strLabel & vbnewline

Set prow = prows.next

Loop

End Function

Thank you!

Re: label related table - in one staked label

TimSexton1 — Wed, 22 Apr 2015 13:38:11 GMT

Simply add an IF statement inside the loop prior to setting the FindLabel similar to below:

Do until prow is nothing

if prow.QuantityUsed <= 1 then

strLabel = prow.Description

else

strLabel = prow.Description & "(" & prow.QuantityUsed & ")"

end if

FindLabel = FindLabel & strLabel & vbnewline

Set prow = prows.next

Loop

Re: label related table - in one staked label

Anonymous User — Wed, 22 Apr 2015 16:45:30 GMT

Works perfect! Thank you!!