topic Re: Problem with Join Field geoprocessing tool _ Still an issue with ArcGIS 10 sp5 in Geoprocessing Questions

Problem with Join Field tool

AveryBowron — Wed, 15 Jun 2011 22:47:00 GMT

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

Re: Problem with Join Field tool

MostafaFouly1 — Thu, 16 Jun 2011 10:04:11 GMT

Hey Abowron,

I have just got a question...does the 2 tables that you would like to perform the 'Join Field Tool' on has matching number of records? if the input table has more records than the join table then you will get null values...simply because yet the IDs did match but the records in the join one is not enough for filling the Input table.

Fouly.

Re: Problem with Join Field tool

AveryBowron — Thu, 16 Jun 2011 15:51:22 GMT

Yeah, I know that. Many of the NULL values were expected for that reason. However, a number of the records that did have matches also failed to join, and that's what I'm trying to figure out since the tool didn't provide an error message and some of them joined properly. Sorry if I wasn't clear enough in my original post.

Re: Problem with Join Field tool

MostafaFouly1 — Mon, 27 Jun 2011 20:42:03 GMT

Hey Avery,

Actually that sounds weird but if it's a serious issue at your side and you still didn't find a solution for it may be we can find some time so as to check what happening at your side remotely!

Grtz,
Fouly.

Re: Problem with Join Field tool

StevePeaslee — Fri, 01 Jul 2011 18:03:54 GMT

Avery,

If some records join and some don't, I would examine the values in the join field a little more closely. Assuming that your join field is character, the most common problem I've run into are things like leading spaces or some other character that doesn't display. Those can be hard to spot when looking at the tables. Sometimes I've resorted to copying a pasting field values into Notepad where I can read them easier.

-Steve

Re: Problem with Join Field tool

ChristopherBlinn1 — Thu, 28 Jul 2011 14:16:15 GMT

Avery,

I am having a very similar issue. Did you ever find a solution to this? I have tried multiple workarounds but none seem to work.

-Chris

Re: Problem with Join Field tool

JeremyJurick — Mon, 01 Aug 2011 15:32:36 GMT

I have experienced problems with 'Join Field' as well. I was using this tool to join two tables that were in separate geodatabases. The tool would run to completion but about 15 of the 215,000+ records showed up as null, even though I could successfully join them in ArcMap.

I imported both tables into a the same file geodatabase and reran 'Join Field' and it worked as it should.

Re: Problem with Join Field tool

ShitijMehta — Mon, 01 Aug 2011 23:32:54 GMT

Hello All,

Our developers are looking into the joined fields with Null values problem. We will inform you in a day or two.

Re: Problem with Join Field tool

ShitijMehta — Tue, 02 Aug 2011 15:42:40 GMT

Hello abowron,
Hello jeremyjurick,

Can any one of you please share a subset of your data? This would make it easy for us to find the problem and suggest a solution.

Thanks!

Re: Problem with Join Field tool

EricMoody — Thu, 04 Aug 2011 13:16:30 GMT

Just ran into a similar issue... Tried using the Join Field Tool on a table in a fGD to feature class, same number of records (6585) in each, same data type (text) and had 1100 records not join... Tried it thru ArcMap and everything worked just fine...

Re: Problem with Join Field tool

KenHartling — Thu, 04 Aug 2011 17:06:33 GMT

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

Re: Problem with Join Field tool

ChrisCool — Wed, 14 Sep 2011 15:45:42 GMT

Hello,

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

Thanks ...

Chris

Re: Problem with Join Field tool

ShitijMehta — Wed, 14 Sep 2011 17:59:02 GMT

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.

Re: Problem with Join Field tool

ChrisCool — Thu, 15 Sep 2011 13:40:15 GMT

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

Re: Problem with Join Field tool

DianeMcConnaughey — Thu, 15 Sep 2011 18:11:28 GMT

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.

Re: Problem with Join Field tool

ChrisCool — Thu, 22 Sep 2011 12:15:41 GMT

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]

Re: Problem with Join Field tool

cle444 — Thu, 31 May 2012 22:35:33 GMT

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

Re: Problem with Join Field tool

ChrisCool — Fri, 01 Jun 2012 10:20:25 GMT

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

Re: Problem with Join Field tool

StevePeaslee — Fri, 01 Jun 2012 11:03:33 GMT

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

Re: Problem with Join Field tool

cle444 — Mon, 04 Jun 2012 23:28:57 GMT

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