Need solution for labeling point features with One -to- Many relationships

Question

I know there once existed a tool/script titled "OnetoManyLabels" which created labels based on a one to many relationship. Does anybody know a workaround/script/add in to produce labels from such a relationship? I have been trying to build some advanced label expressions but is just getting ridiculous! I have found so many posts related to this topic so hopefully somebody knows of or can help me find a solution!

RichardFairhurst · Answer

Here is a sample label expression that works with the Pivot Table approach when a maximum or 4 potential pivoted text values are joined to a feature class:Function FindLabel ([LABEL_Pivot.LABELS1], [LABEL_Pivot.LABELS2], [LABEL_Pivot.LABELS3], [LABEL_Pivot.LABELS4])
  Output = ""
  If [LABEL_Pivot.LABELS1] > " " Then Output = Output & [LABEL_Pivot.LABELS1] & vbcrlf
  If [LABEL_Pivot.LABELS2] > " " Then Output = Output & [LABEL_Pivot.LABELS2] & vbcrlf 
  If [LABEL_Pivot.LABELS3] > " " Then Output = Output & [LABEL_Pivot.LABELS3] & vbcrlf
  If [LABEL_Pivot.LABELS4] > " " Then Output = Output & [LABEL_Pivot.LABELS4] 
  FindLabel = Output
End Function

Only the minimum number of label rows would be created in the stacked label to show text values that are not blank.  The patterns just repeat to expand the label to handle additional label fields generated by the pivot.  The labeling speed is not really noticeably different from labeling using the fields of the point features themselves.

RichardFairhurst · Answer

I have seen the code for the OneToManyLabels developer sample.  If anybody could find me a screen shot of the property page form layout from the original VB6 project I may be able to convert it to an Add-In extension for ArcGIS 10.1 using VB.Net.  I have never had VB6 and I have never seen the original code in use, so I currently I would have to attempt to reverse engineer the property page form based on the code without a screen shot.  I have not attempted to create property pages using VB.Net add-ins before, so I don't know what challenges updating the code will cause, but in theory this code should be able to be converted to an add-in.  Additionally, reviewing the comments on the tool it looks like some bugs were already appearing by ArcGIS 9.2 sp4.  I am not sure if the bug in the ArcObjects class has been resolved by 10.1 or not.

However, for those with an advanced license, performance of the Pivot table approach is likely to perform much better, since the 1:1 join should be faster than the multiple queries run using the OneToManyLabels tool.  For end users that only view data that is not being updated live, the Pivot table approach is better.  If an editor can live with labels that are not refreshed until he does the above script as part of the editing process, the Pivot Table approach is probably also better.  However, if an editor needs labels generated on the fly as they create features or related records, the OneToManyLabel approach is the only one that would maintain a live label association.

The Pivot Table approach also generates better labels in my opinion, because it always sorts the records of the related table with many records into a meaningful order.  The OneToManyLabels script does not do this step, because a sort would significantly slow down the labeling process.  As a result the many related records are labeled only in the order they are stored in the related table when that script is used, which can be entirely arbitrary and random.  The only way to correct that behavior effectively with the OneToManyLabels script would be to replace the original related table with a permanently sorted version that is updated from time to time (which would involve a process identical to that part of my process for the Pivot Table).

RichardFairhurst · Answer

I know there once existed a tool/script titled "OnetoManyLabels" which created labels based on a one to many relationship. Does anybody know a workaround/script/add in to produce labels from such a relationship? I have been trying to build some advanced label expressions but is just getting ridiculous! I have found so many posts related to this topic so hopefully somebody knows of or can help me find a solution!

Since I have an advanced license I use the Pivot Table tool to transform a 1:M relationship into a 1:1 relationship where the rows are converted to field columns for each feature.  Here are all the steps in Model Builder to automate it.

1.  The join field and all of the label field values need to be sorted, so first I sort the rows to a new table by using either the Sort tool or the Summary Statistics using both the join field and the label field(s) I want pivoted as unique case fields and at least one summarized field.

2.  I add a Long field to the sort/summary with a name that will make sense as a field name when the pivot occurs and calculate the objectIDs of each summary row into that field.

3.  I Summarize the previous Sort/Summary again with the Summary Statistics field, but this time I make just the join field a unique value per row and get the Min value of the ObjectIDs values I calculated in the new field.

4.  I create a Tableview of the first sort/summary.

5.  I add an attribute index on the join field of the tableview of the first sort/summary.

6.  I add an attribute index of the join field of the second summary.

7.  I perform a join of the first sort/summary Tableview to the second summary on the join fields.

8.  I use the field calculator and recalculate the field I added to be sequential numbers using the formula:

First_Summary.Labels - Second_Summary.Min_Labels + 1

9.  I use the Remove Join tool to break the join of the two summaries.

10.  I use the Pivot table tool to transform the rows into columns using the join field as the unique case, the Labels field with the sequential numbers to create the field names, and the actual values I want to appear in the labels as the pivot value field.

11.  Add an attribute index on the join field in the pivot table.

12.  Join to the original feature class that is the 1 portion of the 1:M relationship and build a label expression that uses the multiple field columns of the pivot to create a label.