Join Field changed the join table values.

[JeromeWu]

Hello everyone. I am trying to join a field from a standalone table (PopWithin_FG) to a shapefile layer (FINAL). I used Add Attribute Index for both tables and then used Join Field. I am trying to add TRACT_FG to FINAL shapefile. 

After I run the tool, I noticed that the values in FINAL is completely different than the values in PopWithin_FG.

Eg. Observe FID_1*  114, the value for TRACT_FG in PopWithin_FG is completely different than TRACT_FG in FINAL. Does anyone know why this is happening? Is there a workaround for this issue?

Thank you.

[Nicole_Ueberschär]

Hello @JeromeWu,

Which field were you using to join the TRACT_FG to FINAL? Is it possible that you accidentally used the wrong ID, e.g. the default setting?

Feel free to share the data with me to see if I can reproduce the issue.

[JeromeWu]

Sorry, I'm not sure what you mean by field in this context. Here is the parameters that I used for the Join Field tool though. Did I input it incorrectly? 

Thank you!

[Nicole_Ueberschär]

I meant the Input Join Field and Joined Table Field in the tool settings but they look correct to me. Strange.

Could you try to Add join instead of Join Field to see if that brings the correct result?

[JeromeWu]

So I tried Add Join earlier and it kind of worked. It created three records for FID_1 114 in FINAL (I highlighted the three records in FINAL table). The first record is the correct one but the following two records is not what I need, not sure where it pulled those TRACT_FG values as well. I would use this solution, however, I feel like  it would be pretty unproductive to go through 1200+ records and delete the incorrect ones. Do you know of any workaround for this?

[Nicole_Ueberschär · Accepted solution]

Can you check if your PopWithin_FG table has duplicates in the FID_1 field?

The result looks to me like there are three rows in PopWithin_FG with the value 114 in the FID_1 field. When joining the field it takes only the first row it finds (with the value 0.403064), when doing the Add join it joins all rows, creating duplicates of the Input row. Maybe try to sort the Pop table by FID_1 and see if you have duplicates here. If the duplicates are correct from the data side (meaning you need those in the Pop table), I would try to find a way to query those rows you want to write the values for into the Final and then do the join.

[JeromeWu]

Looks like you were right. Seems like I had some issues with my Tabulate Intersection analysis earlier, I fixed it and it is working now.

Thank you!