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]
Solved! Go to Solution.
I know this is old, but I came across this link that helped me label from a related table with an advanced expression.
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
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!
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!
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
Works perfect! Thank you!!