Problem with Join Field tool

[AveryBowron]

I've been using the Join Field tool to add two fields from a .dbf table that describe ownership groups (i.e. Weyerhaueser, etc) and group types (i.e. public, private, conservation, etc) to county-wide tax parcel features that are part of a personal geodatabase.  This worked fine for six counties, but for some reason I can't get the final county I'm working with to join properly.  The tool runs without any error messages, but the resulting table has NULL values in most (but not all) of its records.  This is partially expected (as the join table doesn't have a matching record for every record of the input table), but there are many records which should have joined, but did not, and ended up with a NULL value.  Neither the input nor the join tables had any records selected when I performed the join.

Any ideas on what could have caused this?

Thanks,
Avery Bowron

ArcGIS 10 ArcINFO student license
Windows 7

[KenHartling]

Can you send us your data and exact use of the JoinField tool so we can investigate?
Thanks,
Ken
ESRI
Geoprocessing Product Engineer

[ChrisCool]

Hello,

I have the same problem. Please find enclosed an exemple of my data.

Thanks ...

Chris

[ShitijMehta]

Hello Chris,

Can you give some details such as:

• Are you using 10.0 sp2?


• Which field are you joining on?


• You are joining what to what?


• After joining what do you see.

From my investigation:

With Add Join tool:
From your data I tried to join the table to a feature class using the idvoie field. The Null that you see are because of the the existing Null values in the feature classes idvoie field before join. Using the classant field results in just one field with Null as there is no field value "Commune de La Garenne Colombes" in the ref_voie table.

With Join Field tool:
I joined the table to the feature class based on the classant field and one or the records values (Sadi Carnot (rue)) which exists in both the feature class and table and yet had Null Value after Join.

The reason for that is this value - Sadi Carnot (rue) has a space at the end in the feature class and no space in the ref_voie table.

How to find - Start the edit session and go to that field value. Put your cursor at the start of the field value and then use the arrow to go right till the end. You will notice that at the end of the field value i.e .......) there is a trailing space like this - Sadi Carnot (rue) (<SPACE)

Do the same with the table field value. You will notice there is no space - - Sadi Carnot (rue)(<NOSPACE)

Trailing space differences will cause the output to have NULL values after Join.

[ChrisCool]

Hello

First of all, great thanks for your quick feedback and i apologise for the first mail which was not really detailed

In fact, by using the �??joinfield�?� function, I like to add the field �??idvoie�?� (issued from the �??ref_voie�?� table) to the feature class �??Equip�?�.
To realize the join, I rely the field �??classant�?� which is commun to both tables.

In the attached data, the result of this function is the field �??idvoie�?� in the feature class �??Equip�?�; in this field �??idvoie�?� you can easily note that many values are null.
The tests of the �??joinfield�?� function are failing by using neither ArcGIS 10 SP2 FR nor ArcGIS 10 US.
Contrariwise, the tests are successful by using the �??joinfield�?� function with ArcGIS 9.3.1.
By using the �??add join�?� function with ArcGIS 10 SP2 FR, all the records are matched.

For the record �??Sadi Carnot (rue)�?�, my problem is solved: you were right by noting a space at the end of the value
Unfortunately, 98 others records are not solved yet!

Thank you for your precious help

[DianeMcConnaughey]

I have had a similar problem with the Join Tool when working with GRIDs.  ArcMap in 9.3.1 would add the fields from the joined table, but all of the records were empty for the added fields.  To populate them, I had to do a virtual join, then calculate the fields.  Initally I thought this was a refresh issue, but the fields were still empty after removing and readding the GRID.  In  9.3.1 ArcCatalog the  join table was successfull.  I�??m testing this in 10, but the remote server connection is very slow.  In ArcCatalog it was successful, ArcMap is still battling the connection speed, and may fail because of that.

[ChrisCool]

Hello,

Esri French Support gave me an answer. Now it only remains to wait !

[INDENT]Bug NIM068933
Nimbus ID  NIM068933
Submitted  Jun 2, 2011 5:27 PM
Severity  High
Applies To  ArcGIS
Version Found  10.0 SP2
Prog Language  N/A
Server Platform  N/A
Client Platform  Windows
Database  N/A
Locale  N/A
Status  Open
Version Fixed  N/A
SP Fixed  N/A
Modified  Sep 19, 2011 2:39 PM
Language  English
IDE  N/A
Description  Data Management Tools
Synopsis
The Join Field tool returns incorrect results, but Add Join tool is returning the right results.
Problem Description
Steps for Add Join tool:

1. Add TestData and TestTab from the attached fgdb to an empty map
2. Open Add Join Tool in the ArcToolbox > Data Management Tools > Joins toolset.
3. Layer Name or Table View would be Test Data
4. Input join Field is LIC_NAME
5. Join Table would be TestTab
6. Output Join Field is LIC_NAME
7. Check "Keep All"
8. And press OK.

Now open the attribute table and you will find there are 12 records returned with the attributes in all 12 records. Remove the joins by Right clicking on TestData > Joins and Relates > Remove all joins.

Steps for Join Field Tool:

1. Add TestData and TestTab from the attached fgdb to an empty map
2. Open Join Field Tool in the ArcToolbox > Data Management Tools > Joins toolset.
3. Input Table would be Test Data
4. Input join Field is LIC_NAME
5. Join Table would be TestTab
6. Output Join Field is LIC_NAME
7. And press OK.

Now open the attribute table and check there are only 4 attributes joined properly and the others are null. According to the usage of both of the tools, the Add Join Tool will create a join which is temporary and Join Field tool will create a join that will be appended to the feature and it is permanent.

Please find the attached screen shots that explains the issue more effectively.

Note: This worked fine in the 9.x versions. And I tested it and it is reproducible.
Additional Status Information
N/A
Alternate Solution
Join Field tool followed by Copy Features tool in the model builder may give expected results.[/INDENT]

[cle444]

Hi all,

I have had this problem too. Table temporary join is all good, but Join Field is not working properly as there are records should match but didn't. Nothing to do with the space thing as two joining fields are cleaned with str.strip() and tested.

Also did an experiment of making two tables with only one record from those unmatched ones. Run Join Field and it is fine. So I believe it is a bug.

I am on ArcGIS Desktop 10, SP4, Windows 7. Any updates/comments on this from ESRI?

-Hua

[ChrisCool]

Hi,

Some links for this problem :

[INDENT]http://support.esri.com/en/bugs/nimbus/TklNMDY4OTMz[/INDENT]

[INDENT]http://support.esri.com/en/bugs/nimbus/TklNMDcxOTY0[/INDENT]

[INDENT]http://support.esri.com/en/bugs/nimbus/TklNMDc2MTg2[/INDENT]

Bye

Chris

[StevePeaslee]

Something else to try...

With ArcGIS 9.3.1 SP2, I find it helps to select all the records in the target table before running the JoinField tools. I haven't tried this in 10.0.

-Steve

[cle444]

Hi,

Some links for this problem :

[INDENT]http://support.esri.com/en/bugs/nimbus/TklNMDY4OTMz[/INDENT]

[INDENT]http://support.esri.com/en/bugs/nimbus/TklNMDcxOTY0[/INDENT]

[INDENT]http://support.esri.com/en/bugs/nimbus/TklNMDc2MTg2[/INDENT]

Bye

Chris

Thanks Chris. The solution of "Join Field tool followed by Copy Features tool in the model builder may give expected results" works.

-Hua