Issue with Many to Many (?) Join/Relate

[EmilyC]

Good morning all,

I'm a relatively new ArcGIS user, so please excuse if this is an easy fix, but I can't seem to find any documentation for this online. 

I'm trying to make a list of contractors in every county within a U.S. state. Ideally, the goal would be to click on a county within the state, and the list of available contractors would appear. I have a csv file that has a row for each county/contractor combo. However, when I try to join or relate this to the TIGER file using GEOID (in order to visualize the data not as a point, but rather as the county itself), I either only get one contractor per county, or I get none of the data actually appearing (ie all rows for 'Contractor' show as null). I can't seem to get multiple contractors per row. A few things that may be happening, that I'm not sure how to get around:

1. When I convert the input table from an .xlsx file to a .csv file to import it as a standalone table, the leading zeros in the GEOID are erased, so there could be issues matching the GEOID from the county map layer and the table layer? However, I'm not sure how to keep this leading zero in a csv file, and the suggestions I'm finding online aren't working. This could not be the reason there's no join matches, but I feel like this is a good place to start.

2. It seems like what I need is a 'many to many' join, but that option almost never appears on the dropdown in the 'Add join' feature. I'm not sure why.

I do have some limitations on sharing the contractor data due to privacy reasons, I've tried to attach example data regardless, but it doesn't seem like many of my file types are supported. Please let me know any data I can upload, and if I can provide anything else! Thanks!

[DavidSolari · Accepted solution]

1. You can use the Excel to Table tool to skip the csv step, if you make the Excel column use Text formatting that should preserve the data type and the leading zeroes. If that doesn't work, you can Calculate Field the zeroes back in using Python. For example, if there should always be 8 digits in the code, the expression is "{:08d}".format(!GEOID!)

2. You can't do a Many-to-Many table join because there has to be an intermediate table to hold the relationships. If you have a true M-N relationship you have to add a Relate.

[EmilyC]

Thank you David, the Table to Excel worked great. Once they are related, I'm able to get the count of contractors within each county, but I still can't seem to list them each individually. Do you know how I'll do this? 

For instance, in the pop-up, I am able to output 'Count' and get the correct number of contractors, but I cannot see the names of each, which are in the standalone table. I'd really like to have these names if possible. Please let me know if I can clarify anything! Thanks!

[EmilyC]

As a bit of an update, I see that this is because text fields did not get added to the relate, just numeric fields. How can I change this?

[EmilyC]

For those looking for the full result, instead of a join/relate, I ended up creating a Relationship Class as well as an intermediate 'Junction' table that had a column from both of the other layers. With this, I did a One-to-Many Relationship class between the Junction table and each of the original tables. To do this, I made sure to create a new .gdb to put all three of these layers in, so they could communicate with each other. Once this was done, the two original layers could talk to themselves seamlessly, and I was able to configure pop-ups on my map layer to include data from both. Also, as a caution, make sure the map layer you are editing is the one from the new .gdb (right click layer -> add to current map). Even if you copied this new file from the original map data, you still need to add the new data to the Contents pane and then configure that new layer.