Using One to Many Relationships - Labeling and Symbology - in ArcGIS 10

[rbanick]

I was wondering if anyone knew of any workarounds or developer tools for building labels and symbology off of fields in a 1:M relationship? I deal with data wherein one area frequently plays host to multiple programs, which I need to label / symbolize appropriately. Barring the tedium of manual labeling and symbolizing, I can't find any way to reflect 1:M relationships in ArcGIS 10.

I know this has been something of an ongoing problem with ArcGIS and was wondering if anyone had seen or developed workarounds for 10. There are some developer tools for older versions of ArcGIS but none that I've tried have worked with Arc 10.

If anyone could help with this I'd be incredibly appreciative, I deal with related datasets all the time and at present am stuck doing a lot of cartographic work by hand.

[kker490]

Try using the make query table tool from the data management toolbox - then use the resulting layer for labeling and symbology.

[deeptivb]

How is the data currently represented in the attribute table? Do you do a join? Where is the feature class stored - sde or file/personal geodatabase?

I have a similar 1-M feature class which resides in SDE. What I do is create a new table on SDE running a SQL statement against the feature class. This table has two fields. In your case first field will be Area, and the second field will be Programs. In the Program field I concatenate all the Programs linked to that area with a space or any other delimiter in between.

Then bring both the feature class and the newly created table into ArcMap, do a join on the Area field, label using the Programs field of the table(not feature class) and use a vbscript expression to split the concatenated string, which will split into different programs for that Area in separate lines(single label.)

[rbanick]

How is the data currently represented in the attribute table? Do you do a join? Where is the feature class stored - sde or file/personal geodatabase?

I have a similar 1-M feature class which resides in SDE. What I do is create a new table on SDE running a SQL statement against the feature class. This table has two fields. In your case first field will be Area, and the second field will be Programs. In the Program field I concatenate all the Programs linked to that area with a space or any other delimiter in between.

Then bring both the feature class and the newly created table into ArcMap, do a join on the Area field, label using the Programs field of the table(not feature class) and use a vbscript expression to split the concatenated string, which will split into different programs for that Area in separate lines(single label.)

Interesting. Our spatial data infrastructure is still a little fragmentary and we aren't using SDE or any complex geodatabase systems yet. I unfortunately have to work off of Excel spreadsheets until we implement either SDE or an Access database.

I was trying to avoid performing a join like this due to the clumsiness of relational functions/the lack of SQL in Excel, but it appears this may be the best solution.

What vbscript do you use to break up an individual string into single lines? I'm not a strong scripter and would love to make use of whatever you're using.

[deeptivb]

Simpler the setup the better. Excel means you do not have to use any sql scripts to create table. I think you should be able to use the concatenate function in excel. =+CONCATENATE(A1, ",", A2,",", A3) (In the example function above I have used coma as the delimiter. A1, A2, A3 are cells containing the Programs for the same Area.)


VBscript (paste it into the label expression text box in ArcMap labeling engine, Programs is the name of the attribute field with concatenated string, coma delimiter)



Function FindLabel ( [Programs] )
Dim Name, Temp, S
Temp = [Programs]
S=Split(Temp,",")

Dim counter
for counter = 0 to UBound(S)
Name = Name & S(counter) & vbCrLf
next
FindLabel = Name

End Function

[rbanick]

Simpler the setup the better. Excel means you do not have to use any sql scripts to create table. I think you should be able to use the concatenate function in excel. =+CONCATENATE(A1, ",", A2,",", A3) (In the example function above I have used coma as the delimiter. A1, A2, A3 are cells containing the Programs for the same Area.)


VBscript (paste it into the label expression text box in ArcMap labeling engine, Programs is the name of the attribute field with concatenated string, coma delimiter)



Function FindLabel ( [Programs] )
Dim Name, Temp, S
Temp = [Programs]
S=Split(Temp,",")

Dim counter
for counter = 0 to UBound(S)
Name = Name & S(counter) & vbCrLf
next
FindLabel = Name

End Function

Great! Thank you very much, I'll test this out tomorrow and let you know how it works.