nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Finde mehr Beiträge mit dem Tag

Accepted answers

All comments

curtvprice

I highly recommend (plead with you really) to report the bug to Esri support. Reproducible user-reported bugs are the ones that get fixed.

James_Whitacre

I just had an issue with joins that aligns with this post, but I was able to figure it out. I will explain below; read bold text for quick issue and solution. I was using ArcGIS Pro 2.5.

I was trying to join data TO a standalone table FROM a geocoded feature class. I don't do this often, but I find it helpful when needing to geocode many records to cities/POI that have multiple duplicate geocoding locations in the data. By running Summary Statistics, I get unique locations and significantly save AGO credits when I geocode them (e.g. I had ~3.1 million records which cost ~124K credits - which I do not have - and reduced the locations to less than ~5,500 records at 220 credits, which I can manage!!). I then join the XY fields and map all original records the using the XY Table to Point tool. In theory, you can also use Make Query Table, but it isn't consistent either...and can be slow with larger datasets. This joining workflow seems to work better.

When I tried to join the data, using either the Add Join or the Join Field tools, it resulted in the joined fields from the geocoded FC to return <Null> for all values in the join fields. I think this should be allowed, so this may be a bug/issue for Esri to explore. My workaround was to convert the geocoded FC to a standalone table then join, and everything worked fine.

Hope this is helpful for this thread.

NickJones7

I'm still seeing problems with this as of 10.6. I still have to use a numeric field for it to run as expected.

curtvprice

Suggest patience (Join Field is fairly slow with large tables, and Pro runs all tools in the background so the application remains responsive) or do a dynamic join with Add Join followed by Copy Features (much faster).

If you still have a problem, I beg of you to contact Esri support, because believe it or not, that's how things get fixed, when people send real data to Esri that does not work. (Without 'real data' that fails, it is very hard for them to make progress improving quality.)

JurisZarins

Still the same problem. After joining two tables (feature and table) in the same fGD no information in table, grayed menus and running circle in the corner. It seams that development is like a cycle. Arcview good and usable was from version 3.0. Maybe with ArcGIS PRO v. 3 will be the same.

DrewFlater

I'm sorry you feel that way.

We went back to the original NIMBUS bug case listed above and tested with the same data using new software versions (namely ArcGIS Pro), and the Join Field tool correctly joins all attribute values from the join table to the input table, without nulls. The only nulls are those which were already in the join table. If you have a case that still isn't working please upload it here or contact tech support.

The top table is the input to Join Field (a feature class), bottom table is the join table. All the joined fields in the top have values, except where the join table has pre-existing nulls.

ArmandGerstenberger

How bad is this software that it can't join two databases together? How bad is this company that 8 years go by with no fix?

curtvprice

If you really are seeing crashes, i would contact Esri support. Though you are likely to find out it is a data specific issue. I have not non-data specific issues with Join Field except of course its legendary slowness.

SSURGO data retrievals can be very complicated, if you are using the soils records you can have a double one to many relationship between the map unit table, soil records, and layer records. Here are some pre-digested datasets that may save you some pain:

SSURGO Data Downloader (beta)

Area- and Depth-Weighted Averages of Selected SSURGO Variables for the Conterminous United States and District of Columb…

Anonymous User

Ah yes. Thank you! I'm conflating forum posts after surfing a bunch related to same thing. The tables, filepath, field names all check out. Haven't changed anything from raw SSURGO tables, no hidden spaces, and the table that successfully joined using join field tool is in exact same location/gDB/filepath as the table that would not join successfully to the target table... Standard Join tool works from right click menu. Join Field tool through toolbox does not... I can't think of anything else that points to anything other than the tool itself? Though I am new. Any ideas if it's not the filepath/fieldnames etc?

I also did have a glitch earlier in the same process where I had to start again with raw data because the Join Field tool resulted in large amounts of blank (not Null but truly blank) cells that I knew should have data in them post-Join. At the time, I assumed it was me, but after ongoing issues and seeing this thread, I'm starting to think it may be more than just user-error... Thanks in advance for any possible help!

Lorne_Dmitruk_0-1770393797572.png

Lorne_Dmitruk_1-1770394017199.png

Bud_0-1770394126815.png

curtvprice

Nothing above in this thread about 999999 crashes, just matches not working.

999999 crashes often are related to system related issues, often invalid path names, table names, and field names.

Anonymous User

Just bumped into this tonight. Working with SSURGO data, the primary key is a numeric value, though stored in the field as a string... No idea why... With the exact same table, I was able to use Join Field to permanently append the fields from another table. Went to do it a second time. All settings the same... Failed every time I tried. I had the full target table selected, I was doing it manually directly through toolbox, and I was nowhere near the ~255 column cap... Tried it with only one field, still not working. Was able to use Join function through right-click menu just fine. Only error generated was 999999 generic error code. No other messages, or indication in logs of what might have produced this failure. Saw a few other posts addressing this issue for slightly different reasons. Wanted to tag onto this post to let people know it may still be an ongoing issue? No clear indication here if it was ever resolved...

NickJones

Still nothing? Since I last replied to this thread in February, I've found that using number values as opposed to alpha numeric or and kind of string works much better. It's sort of a hassle though. So I create a table in the beginning that has the string values and a new number value. I use this table to do a regular join later on, but any time I use "join field" I use the number values instead. I wish they 'd fix this or respond. It's gotten me in hot water a few times.

Nick

NickJones

Has anybody ever figured this out? It's pretty rampant. I try to use this tool a lot. I have the same service pack as Ralf right above me. I find that I never have this issue with a numeric field, but now I'm kind of wondering if "alpha-numeric" values throw it off for some reason. I know it's not unknown spaces or anything like that, because the join works just fine the old fashioned way.

Thanks,

Nick

Ralf-D_Schroers

Any solution to this?? I'm using modelbuilder and ArcGIS 10 (ArcInfo), SP5.

-Ralf

http://support.esri.com/en/bugs/nimbus/TklNMDc2MTg2

using JoinField tool from the toolbox or model builder, it is still returning NULL values for some part of the data unexpectedly.
I am running ArcGIS 10.0 sp5. It is very disappointing that it is Not fixed in sp5!

GrahamMacGregor

I am running into the same join field issue in python that says above would be fixed in service pack 5. I am running service pack 5. Joinfield runs but only joins some of the rows while leaving the others null. Spaces in my join field "No". Finally resorted in python to using adjoin tool, with no qualified field names and then copied that join to a new feature class in python.

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

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

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

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

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]

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

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

test_new.zip

Multipatch_ArcScene.JPG

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,

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

Thanks ...

Chris

test.zip

Search setup1.JPG

KenHartling

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

EricMoody

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...

ShitijMehta

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!

ShitijMehta

Hello All,

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

IMG_0713.jpeg

JeremyJurick

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.

ChristopherBlinn1

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

StevePeaslee

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

MostafaFouly1

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.

AveryBowron

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.

MostafaFouly1

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.

Nimm an der Unterhaltung teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.

Schnellzugriffe

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.