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; } } }

Joins do not work in Modelbuilder 10

TomMagdaleno

So far I have been working on fixing models almost exclusively for a week! I have models with multiple joins and calculations and ever since we upgraded to 10 they do not work. I have a "Remove Join" and then a second Join, but I get errors saying that my inputs are not current. When I do a validation it will tell me so and so table is already joined. So I have to exit out, delete the joined table then open the model again to validate the data. Now the data before the join won???t work because it thinks the ???field name does not exist??? because the table is no longer joined!

MODELBUILDER USED TO BE SMARTER THAN THIS!

Now I have to break up my models into several models and export out intermediate data so I can work with it. This is what I was told to do by tech support. This is driving me nuts!

Also, ???Make Table View??? changes every time the model is validated. If I have only a few columns checked in the table view, then run a validate it will recheck all the columns and screw up my output. Tech support tells me to not use .dbf tables, to put everything in a geodatabase, do permanent joins and export out intermediate data. This is getting to be WAY more steps then just doing a few simple joins and calculates in ArcMap. Is anyone else dealing with this crap?

タグ付けされた投稿をもっと見る

Accepted answers

All comments

ShitijMehta

Can you share your model and data?

JasonBalmut

It's now 2015 and this thread topic, along with its precursor, has no real solution. That's 11 years with no fix. A decade is a reasonable window for improvement and yet the geoprocessing tools still don't work as it should.

I added this to ArcGIS Idea today, March 6, 2015.

MarkYoung

you can add a remove join to the model on your join out put with the result of what uou use the join for as a precondition, see attached
'm hoping to find a way not to have the output set to table.field in the exported tables but haven't found it yey, if you find it please post

Hi,
I had the same problem with add Join but your diagram has fixed my issue - thanks for the post,
Mark

BillBranson

That's quite unfair of a response. I can assume you did not read this thread nor do you work much outside of the ESRI environment in databases.

Jim W,

We all feel the pain. Richard Fairhurst has drunk the kool-aid, and can one expect anything different? That's who pays him for shilling on here.

The Root Cause is how ESRI has chosen to approach DB Management. For some reason(s), back before ArcGIS 8, ESRI has taken their own approach to DB structure and rules of interaction. Not only that, these "rules" don't seem to persist in precisely the same approach across major software 'updates'.

Like any large organization, responsibility for keeping it together isn't hanging on any one person or steering committee and therefore it breaks a lot when it clearly shouldn't.

Add to that the fact that ESRI is the only player (like Auto CAD in their field) and you get a geometric increase in inertia to make sane and timely changes. Being the only game in town makes them immune to reasonable requests from their user base.

Also, I think since looking at the 10.2 stuff it seems that ESRI is focused on monetizing their business in every way possible. I can only assume this is so because people are using their product less and less and so they need to squeeze more $$ out of every user. That focus drops this kind of software updates/fixes from �??low priority�?� to �??No priority�?�.

Now every answer will be the same: <chipper voice>"You can do this fast, easy, super awesome thing in 10.2!!! Just log into your account at ArcGIS Online</chipper voice>

And all ArcGIS users everywhere say? Ughh!

JimW1

It is the default because I join tables with identical field names all the time and the only way to tell which field I am referencing is by using the full name. Also, ObjectID, which I also use in queries, is identically named in both tables. Context would not make the name clear to anyone without the fully qualified name. Beside, computers know nothing of context and suck at ambiguity, unless a programmer, who has no idea what your join actually looks like, programs it with intelligence that matches your logic if you are allowed to input ambiguous data. Every time such logic has to be added the speed of the program decreases, sometimes significantly.

Joins work wonderfully for me, but probably because I only use file geodatabases or SDE and not shapefiles or Excel. The Model Builder Environment can set outputs to be without the fully qualified name, which is necessary if you insist on being a dinosaur and using shapefiles. It is found in the Fields Environment settings in Model Builder. If you change that setting using the Geoprocessing > Environments menu that setting will become your default.

That's quite unfair of a response. I can assume you did not read this thread nor do you work much outside of the ESRI environment in databases.

RichardFairhurst

So much time has passed on this thread and I now sit in 10.1 with joined field names that will only 'Maintain Fully...'
Is there a practical use for this? Why is it the default?

The joins are still a mess and now Model Builder likes to lock itself. There's only one instance of ArcMap open but if I don't manually refresh the table views (by opening the tables from the TOC) it'll lock up on me.

Could someone please tell me why you'd want your field name to be something like [TEMP_PIVOT_GROUPBYFID.FID_Buildings] instead of [FID_Buildings]? I'm at a loss as to how anyone who'd be doing a join in Model Builder wouldn't know the source tables nor could look at the inputs to Model Builder to figure them out. Why is this the default since 10? What big ESRI customer asked for this change and why does it persist?

Enough ranting. Cheers to those that feel my pain.

It is the default because I join tables with identical field names all the time and the only way to tell which field I am referencing is by using the full name. Also, ObjectID, which I also use in queries, is identically named in both tables. Context would not make the name clear to anyone without the fully qualified name. Beside, computers know nothing of context and suck at ambiguity, unless a programmer, who has no idea what your join actually looks like, programs it with intelligence that matches your logic if you are allowed to input ambiguous data. Every time such logic has to be added the speed of the program decreases, sometimes significantly.

Joins work wonderfully for me, but probably because I only use file geodatabases or SDE and not shapefiles or Excel. The Model Builder Environment can set outputs to be without the fully qualified name, which is necessary if you insist on being a dinosaur and using shapefiles. It is found in the Fields Environment settings in Model Builder. If you change that setting using the Geoprocessing > Environments menu that setting will become your default.

JimW1

So much time has passed on this thread and I now sit in 10.1 with joined field names that will only 'Maintain Fully...'
Is there a practical use for this? Why is it the default?

The joins are still a mess and now Model Builder likes to lock itself. There's only one instance of ArcMap open but if I don't manually refresh the table views (by opening the tables from the TOC) it'll lock up on me.

Could someone please tell me why you'd want your field name to be something like [TEMP_PIVOT_GROUPBYFID.FID_Buildings] instead of [FID_Buildings]? I'm at a loss as to how anyone who'd be doing a join in Model Builder wouldn't know the source tables nor could look at the inputs to Model Builder to figure them out. Why is this the default since 10? What big ESRI customer asked for this change and why does it persist?

Enough ranting. Cheers to those that feel my pain.

MayaThomas

OMG, I'm new to modelbuilder and I think I made a huge error. I used the JoinField tool instead of the AddJoin tool because I couldn't figure out why I couldn't do what I thought the AddJoin tool should have been able to do. So, now I can't seem to RemoveJoin -- what's a newbie to do?

And thanks for all this other good stuff. I think I can set it up right now but it would be nice if the AddJoin worked correctly.

New to ModelBuilder not New to ArcGIS; I just finally have a little time to play and get in trouble!:)

I think I experienced this same frustration a few minutes ago and had an epiphany - you have to create a feature layer or table view. Then AddJoin and RemoveJoin work correctly. This is essentially the same as doing a tabular join in ArcMap. ArcMap creates these layers/table views automatically. In ModelBuilder, you have to make them yourself.

JimW1

OMG, I'm new to modelbuilder and I think I made a huge error. I used the JoinField tool instead of the AddJoin tool because I couldn't figure out why I couldn't do what I thought the AddJoin tool should have been able to do. So, now I can't seem to RemoveJoin -- what's a newbie to do?

And thanks for all this other good stuff. I think I can set it up right now but it would be nice if the AddJoin worked correctly.

New to ModelBuilder not New to ArcGIS; I just finally have a little time to play and get in trouble!:)

Oh I hate to say it but from my experience your only recourse is to delete the joined fields or revert back to an older version of your data. I've done the same thing one foggy Monday morning at least once 🙂

PatriciaMoehring

OMG, I'm new to modelbuilder and I think I made a huge error. I used the JoinField tool instead of the AddJoin tool because I couldn't figure out why I couldn't do what I thought the AddJoin tool should have been able to do. So, now I can't seem to RemoveJoin -- what's a newbie to do?

And thanks for all this other good stuff. I think I can set it up right now but it would be nice if the AddJoin worked correctly.

New to ModelBuilder not New to ArcGIS; I just finally have a little time to play and get in trouble!:)

ShitijMehta

Can you also share your model with your data? It would be useful in finding the problem and suggesting a solution.

NathenHarp

I also have problems with AddJoin/JoinField and CalculateField in ModelBuilder. The model works with ~20 records but hangs on ~1,000 or more. A workaround is exporting out the intermediate data as seperate steps and calculating the field in ArcMap. Failed model is attached.

SampleModel.png

mapdata.csv

ShitijMehta

From within ModelBuilder:
You have to use the Remove Join tool to remove the join. If you save the model after running once it will save the state of all the tools and the intermediate data unless you go click model menu> delete intermediate data.

or

Rerun the model uptill the tool before the Add Join tool and hit validate. Now rerun the whole model.

AZendel

I'm using ArcGIS 10 SP2 and I encountered a problem with the join table tool today (9/9/11). This problem occurred when rerunning the model from the model builder window, not when running the model from the dialog that opens when you double click the model in a toolbox. My model crashed downstream of the join tool due to an error of mine in the calculate field tool. So I closed the dialogue that prints all of the data from the geoprocessing model as it runs and returned to the model builder itself. I corrected the error in the calculate field tool and validated the model so I could re-run it. But the join tool and everything downstream turned white - indicating an error at the add join tool. I double clicked the add join tool and the red error symbol was present in the add join dialog box. When I hovered over it, it reported that the join table was already joined to the target feature class.

It seems to me that the validate model process doesn't clear out joins from previous sessions. I think it should. When you validate an entire model, it's going to run from the very beginning next time that you run it. So shouldn't model builder detect this and remove all of the joined tables as it validates the model?

I also tried saving, closing, and rerunning the model - no dice. So I closed and restarted ArcCatalog, thinking that something in memory was hanging onto the join. No dice. Then I added the remove join tool immediately upstream of the add join tool and that worked. Of course, I had to remove it for subsequent runs of the model. This seems like a bug to me, but maybe I'm missing something. I hope this helps anybody who is encountering a similar issue.

Corey_C_Denninger

Just an update, perhaps this has been fixed in the later ArcGIS 10 versions (we just got it), but I unchecked the 'Maintain fully qualified field names' and it seemed to work. I had an Add Join, even though I performed a Remove Join it was keeping the full field names. Once I unchecked, it worked great.

For those still banging their heads against the wall:

I have a call into ESRI about this issue but I've made so headway for those that need a quick fix.
On my ArcGIS 10 SP2 the 'Add Join' tool will always add fully qualified field names (aka alias, tablename.fieldname, tablename_fieldname) regardless of what the environment settings are in the Canvas view.

If I right click on the model in the toolbox and got to model properties, then 'Maintain fully qualified field names' is unchecked - see attached. Running the model results in the tablename.fieldname outputs.

Now if I run the model from ArcCatalog and on the parameters page look at the environment settings the 'Maintain fully...' is checked - see second attached. If I uncheck this box and run the model then the fields are the fieldnames without the tablename. This setting has to be manually changed each time the model is run.

The two areas to set environment parameters do not respect eachother. I have also tried 10+ combinations of check/uncheck/save/apply/uncheck/save/validate/run/save in the Canvas view without anything working.

This is the only way I can get 'Add join' to work the way I want it to in model builder 10.
This is of course a huge pain as I can no longer step through models in the canvas view as I did so effortlessly in 9.3.1 but hopefully this helps anyone else experiencing my frustrations with model builder 10 and 'Add Join'. I'll update if ESRI Canada finds a solution for me.

JimW1

I got a workaround from ESRI Canada (from ESRI Inc.) and it's actually pretty decent.

In the canvas view add a model parameter to the tool after the 'Add Join'. In the attached image I have it on 'Feature Class to Shapefile' but it was tested on 'Copy Features'.

Make Variable>From Environment>Fields>Maintain Fully Qualified Names>

When the model is run in the canvas view it will respect the True/False for that variable now. If you delete this visible parameter it reverts back to the tablename.fieldname name mangling.

addjoinbugmostlyworksnow.JPG

Form 2.xlsx

curtvprice

I found a possible workaround in the old forums. I realize this involves re-mapping field names but it may be the only way.

http://forums.esri.com/thread.asp?c=93&f=1728&t=224243

Subject Re: "Export" a table in Modelbuilder
Author Karl Brotzman
Date Jun 19, 2007
Message Take a look at DataManagementTools -> General -> Merge. I've used it to clean up a file after three joins. If you double click the tool after you've added it to a model in the model builder, you'll see the "Field Map" at the bottom of the dialog. You can delete and rename fields there. Worked great for me. The tool accepts a single feature class as well as multiple. That's how I use it.

The only setback I've had is with the persistence of the "Field Map". It was working great, but somehow the model reverted to the default or original Field Map which is all the fully qualified and mangled names. So, I had to retype the map. I tried without success to reproduce that problem.

Those of us at the UC should make a point to ask about this at the geoprocessing island. (I've found this is a very effective way to get things on their radar. I try to sweeten the pie by giving them well-deserved thanks for successes while I'm there -- for example, I absolutely love the new iterator functionality - it's very useful and has worked very well for me.)

Joins in model builder have been a hassle from the beginning. We need it to work better than this!

JimW1

I sure hope they marked it Critical!

I'm not hoping too hard here. I had my ticket closed on me without any useful resolution beyond it is a bug and I should contact ESRI to find out the status of the bug.

I wrote back asking for a workaround before closing my ticket and got a new ticket number auto-response. And so I get even more jaded...

I'll keep you posted if I get a critically needed workaround for this issue that does not involve re-mapping field names

curtvprice

I got back from ESRI:

NIM069071 The environment setting to 'Maintain fully qualified field names' is not honored when performing an Add Join in Modelbuilder

Thank you for getting that logged and sharing that NIM# here. Everyone please use all avenues at your disposal (ideas.esri.com, support tickets, ESRI doctor's office) to push this getting fixed and soon. If you have Premium Support please ask for a hotfix so we can get a patch online!

I sure hope they marked it Critical!

JimW1

I got back from ESRI:

NIM069071 The environment setting to 'Maintain fully qualified field names' is not honored when performing an Add Join in Modelbuilder

JimW1

Just a question: When you check/uncheck maintain fully qualified field names, is it an environment setting in your model, or in the arccatalog program or at the tool level because there is an overwriting sequence that goes: arccatlog > model > tool, with each environment setting "overwriting" the previous if the same 'option' is used.

So the only check that will respond is at the ArcCatalog level when run from the 'Contents' tab. The model parameter window has to be open and the envirnment setting in there is the only one that will respond.

If I right click in catalog and go to properties of the model the 'maintain...' is unchecked. When I run the model it acts as if it is checked. If I run the model in canvas view the check also has no impact. If I change it at the tool level there is also no effect.

The only place that 'maintain fully...' is actually checked is when I run the model from catalog and in the model parameter interface I go to environment settings there. In that place the box is checked and once unchecked the model works as intended - no tablename.fieldname

In the sequence the option is being set at the ArcCatalog level but only in the environment settings when the tool is run and not from the properties of the model when right-clicked in catalog.

You reminded me I have a support case in with ESRI. They replicated the bug there and it's been 2 weeks since I heard from them.

GavinJackson1

Just a question: When you check/uncheck maintain fully qualified field names, is it an environment setting in your model, or in the arccatalog program or at the tool level because there is an overwriting sequence that goes: arccatlog > model > tool, with each environment setting "overwriting" the previous if the same 'option' is used.

JimW1

For those still banging their heads against the wall:

I have a call into ESRI about this issue but I've made so headway for those that need a quick fix.
On my ArcGIS 10 SP2 the 'Add Join' tool will always add fully qualified field names (aka alias, tablename.fieldname, tablename_fieldname) regardless of what the environment settings are in the Canvas view.

If I right click on the model in the toolbox and got to model properties, then 'Maintain fully qualified field names' is unchecked - see attached. Running the model results in the tablename.fieldname outputs.

Now if I run the model from ArcCatalog and on the parameters page look at the environment settings the 'Maintain fully...' is checked - see second attached. If I uncheck this box and run the model then the fields are the fieldnames without the tablename. This setting has to be manually changed each time the model is run.

The two areas to set environment parameters do not respect eachother. I have also tried 10+ combinations of check/uncheck/save/apply/uncheck/save/validate/run/save in the Canvas view without anything working.

This is the only way I can get 'Add join' to work the way I want it to in model builder 10.
This is of course a huge pain as I can no longer step through models in the canvas view as I did so effortlessly in 9.3.1 but hopefully this helps anyone else experiencing my frustrations with model builder 10 and 'Add Join'. I'll update if ESRI Canada finds a solution for me.

addjoinbug1.JPG

addjoinbug2.JPG

SMV_B.png

SMV_C.png

JoshWhite

My problem is not as complicated. I am appending to a feature class that requires a join before the append. I used to do this manually but wanted to automate and speed up the process a bit by using model builder. I only have ArcEditor so there are some options unavailable to me. Is there a way to join a table in model builder before appending the resulting feature class to the previously mentioned empty feature class?

RyanColeman

I'm having the same problem as above. Spatial join doesn't result in tablename.fieldname only "add join" does this

GavinJackson1

Hi Frank,

To my knowledge, spatial joins will always result in tablename.fieldname.

If you have a common unique identifier in both tables/fc's you could use a "join field" which, unfortunately requires an ArcINFO license, because it allows you to output only the fields you want to join.

Hopefully that helps.

Gavin

FrankVignati

Hi,

Under geoprocessing environments you will find an option tocheck/uncheck "maintain fully qualified field names" or something to that degree. This will allow you to perform joins/remove joins without having to let ArcGIS/ModelBuilder find the fully qualified field name (e.g. fieldname not tablename.fieldname)

Thank you, even though that is unchecked, the results in the model show the Table.Field name. In the pictures attached you can see that the table exported from the model has the simple fieldnames but objects in the model do not. With the results in the model having the full qualified path other tools can not be run from them, like Make XY Layer, and if the resulting tables are used, like in the example, other processes will not work from them. The spatial join I am trying to accomplish does not get the fields from the join on the result in the model, the features output have to used in order for it to work.
This is very clunky and frustrating.

M.gdb.zip

gdb.JPG

templ.jpg

parcel.png.zip

GavinJackson1

you can add a remove join to the model on your join out put with the result of what uou use the join for as a precondition, see attached
'm hoping to find a way not to have the output set to table.field in the exported tables but haven't found it yey, if you find it please post

Hi,

Under geoprocessing environments you will find an option tocheck/uncheck "maintain fully qualified field names" or something to that degree. This will allow you to perform joins/remove joins without having to let ArcGIS/ModelBuilder find the fully qualified field name (e.g. fieldname not tablename.fieldname)

FrankVignati

I too am frustrated with Model Builder in 10 (Build 2800). I consistently get an error that my join table is already joined. The troublesome part is that I don't know how to "unjoin" it, and feel I shouldn't have to within a model as that's inefficient (to say nothing of bad logic.)

Between that and the fact that after you rebuild a join, you have to re-truncate your output fields from jointable1.Field1 to Field1, jointtable.Field2 to Field2. (examples) for every field, I am incredibly disappointed with the way it works now. This used to work in 9.3.1.

you can add a remove join to the model on your join out put with the result of what uou use the join for as a precondition, see attached
'm hoping to find a way not to have the output set to table.field in the exported tables but haven't found it yey, if you find it please post

RemoveJoin.png

CustomTimeSlider.zip

jlandwehrwaukeshacountygovnull

I too am frustrated with Model Builder in 10 (Build 2800). I consistently get an error that my join table is already joined. The troublesome part is that I don't know how to "unjoin" it, and feel I shouldn't have to within a model as that's inefficient (to say nothing of bad logic.)

Between that and the fact that after you rebuild a join, you have to re-truncate your output fields from jointable1.Field1 to Field1, jointtable.Field2 to Field2. (examples) for every field, I am incredibly disappointed with the way it works now. This used to work in 9.3.1.

TomMagdaleno

Hi,

Would you be able to provide me with an exact reproducible case for the AddJoin problem you are seeing? I've been trying a number of things based on the information you have provided but so far have not seen the exact behavior you are describing.

Please include the toolbox, model and a small dataset.
Thanks,
Ken
ESRI Product Engineer - Geoprocessing

Hi Ken, thanks for getting back to me. This model takes data we get from the county and scrubs it based on our error log. So if we put "nil" in a cell in the errorlog it will clean out the data in the county data. Conversely if the data in the cell does not match what we have in the error log, it will overwrite the cell in the county data with what we have in our errorlog. Finally if the county has corrected the error, the model will clean the errorlog by blanking out that cell in the errorlog.
Can you give me a private email so everyone can't get our city data.

KenHartling

Hi,

Would you be able to provide me with an exact reproducible case for the AddJoin problem you are seeing? I've been trying a number of things based on the information you have provided but so far have not seen the exact behavior you are describing.

Please include the toolbox, model and a small dataset.

Thanks,
Ken
ESRI Product Engineer - Geoprocessing

会話に参加する

サインインしたメンバーは投稿、更新のフォローなどができます。初めてですか？無料アカウントを登録してください。

クイックリンク

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