One-to-many and many-to-many relationships

2681
5
07-01-2013 11:41 AM
JasonCleaver
Occasional Contributor
I am not seeing the behavior described in red below.  I have a point feature class related to many records in a table.  After performing the AddJoin, and then a FeatureClassToFeatureClass_conversion, I am not getting multiple point records as expected.  I thought there was  a NIM relating to this, however I am not able to find it.

One-to-many and many-to-many relationships

When using data where a one-to-many or many-to-many relationship exists, you should use a relate or relationship class to establish the relationship between the datasets.  However, it is possible to create a join under these circumstances.  When you create a join in such a case, there are differences between how tools and other layer-specific settings work depending on the data source.  If you are using geodatabase data to create the join, all matching records are returned.  If you are using nondatabase data, like shapefiles or dBASE tables, to create the join, only the first matching record is returned.

This means that if you have created a 1:M or M:M join with geodatabase data and you generate a report, you see multiple records in the report, one for each corresponding match.  The multiple matches are also seen when using a join field while symbolizing a joined layer, labeling, identifying features, generating a graph, and using either the Find or Hyperlink tool. If you are using the joined layer as input to a geoprocessing tool or in an export operation, the multiple matching records are used.
0 Kudos
5 Replies
RichardFairhurst
MVP Honored Contributor
I am not seeing the behavior described in red below.  I have a point feature class related to many records in a table.  After performing the AddJoin, and then a FeatureClassToFeatureClass_conversion, I am not getting multiple point records as expected.  I thought there was  a NIM relating to this, however I am not able to find it.

One-to-many and many-to-many relationships

When using data where a one-to-many or many-to-many relationship exists, you should use a relate or relationship class to establish the relationship between the datasets.  However, it is possible to create a join under these circumstances.  When you create a join in such a case, there are differences between how tools and other layer-specific settings work depending on the data source.  If you are using geodatabase data to create the join, all matching records are returned.  If you are using nondatabase data, like shapefiles or dBASE tables, to create the join, only the first matching record is returned.

This means that if you have created a 1:M or M:M join with geodatabase data and you generate a report, you see multiple records in the report, one for each corresponding match.  The multiple matches are also seen when using a join field while symbolizing a joined layer, labeling, identifying features, generating a graph, and using either the Find or Hyperlink tool. If you are using the joined layer as input to a geoprocessing tool or in an export operation, the multiple matching records are used.


This is the first time I have ever seen that.  I am using 10.1.  I just tried it and it worked as it says.  I created a model where I used a geodatabase feature class as an input to the Male Feature Layer tool, used a geodatabase table with a 1:M relationship with the Join tool, used the Select by Attributes tool to select a set of the joined records, and then used the Copy Features tool to output a new feature class.  The 1:M relationship outputted points that had become a 1:1 set of points relative to the joined table.

This is something I had never seen before, but I was so used to this not working at prior versions I never thought I would see the day this happened, so I had never tried it before with 10.1.  I have no idea how many versions back this applies to, but at 10.1 with build 3143 and the Advanced license it worked.

It worked the same way using a joined layer in ArcMap with the same selection that I exported to a geodatabase feature class using the layer's Export context menu.  So now there is finally a reason to do a 1:M or M:M join that I can use.
0 Kudos
RichardFairhurst
MVP Honored Contributor
I just checked the 10.0 help and that paragraph is new at 10.1.  It was not there at 10.0.  This should have been in the what is new with 10.1, but it isn't.  For me this feature is a big deal.

I redid my model to use the FeatureClassToFeatureClass_conversion tool instead of the Copy Features tool and it worked also.  I outputted to the local Default.gdb, in case that makes a difference, but the inputs were from a network gdb.
0 Kudos
JasonCleaver
Occasional Contributor
I tried again, this time on a different computer and it worked.   Same version of ArcMap 10.1 SP1, however, the computer that it didn't work on had the ArcMap 64bit background geoprocessing program installed.  I removed the program, tried again and same result.  I am going to try and repair the install to see if this helps.
0 Kudos
JasonCleaver
Occasional Contributor
Ok.  So I am a little closer determining what the cause is.  It appears to be a custom class extension added by ArcFM.  After removing the ArcFM class extensions, I am getting the expected results.
0 Kudos
DaveOrlando
Occasional Contributor III

This is a great technique to turn one feature into many, if multiple joined records exist.

My procedure is similar:

1) MakeFeatureLayer

2) AddJoin using 'Keep_Common' (assuming you are expecting a 1:M join)

3) CopyFeatures or FCtoFC

you can't see the 1:M multiple records after step2, but they will export to a new polygon / point etc in step3

NOTE, I can't get this to work in SDE, only GDB's

It is also a throwback to the coverages / regions / aml days where we did this quite regularly.

0 Kudos