topic Re: Joins do not work in Modelbuilder 10 in Geoprocessing Questions

Joins do not work in Modelbuilder 10

TomMagdaleno — Mon, 07 Feb 2011 21:07:54 GMT

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?

Re: Joins do not work in Modelbuilder 10

KatieBrien — Wed, 09 Feb 2011 21:19:13 GMT

Is anyone else dealing with this crap?

Yes!!! It's also driving me absolutely batty! I currently have an add join with spatial indexes and attribute indexes applied to both input tables and Arc10 is still telling to me create attribute indexes...

When will ESRI realise to perform a proper bug check before releasing this crap???? We only just moved over to Arc10 last week (thinking that after 6 months the first service pack would solve a lot of trivial bugs).... Seems it was a mistake....

By the way, I have found that if you have any Appends or Copy Features inside models they will also contribute to it falling over. Solution: Remove those tools and re-add them in... Yet again an annoy and time consuming job to do.

Re: Joins do not work in Modelbuilder 10

FrankVignati — Thu, 10 Feb 2011 11:50:21 GMT

YES dealing with that here too, what also works, but waste time, is to remove the link to the join, run the model to create a new table, validate, then re-link the join to the output
waste of time

Make sure to add a remove join to the output after calculating fields if you do not want the other tables fields in the final output
is any one else experiencing the out puts resulting in added fields like 1, 12, 12 13, 12 13 14 after testing models ?

Re: Joins do not work in Modelbuilder 10

KenHartling — Tue, 15 Feb 2011 19:53:44 GMT

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

Re: Joins do not work in Modelbuilder 10

TomMagdaleno — Wed, 02 Mar 2011 14:27:38 GMT

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.

Re: Joins do not work in Modelbuilder 10

JamesLandwehr — Mon, 07 Mar 2011 20:11:03 GMT

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.

Re: Joins do not work in Modelbuilder 10

FrankVignati — Tue, 08 Mar 2011 13:52:58 GMT

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

Re: Joins do not work in Modelbuilder 10

GavinJackson1 — Wed, 09 Mar 2011 10:27:46 GMT

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)

Re: Joins do not work in Modelbuilder 10

FrankVignati — Wed, 09 Mar 2011 16:56:04 GMT

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.

Re: Joins do not work in Modelbuilder 10

GavinJackson1 — Fri, 11 Mar 2011 08:40:54 GMT

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

Re: Joins do not work in Modelbuilder 10

RyanColeman — Tue, 15 Mar 2011 13:43:51 GMT

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

Re: Joins do not work in Modelbuilder 10

JoshWhite — Tue, 22 Mar 2011 17:51:29 GMT

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?

Re: Joins do not work in Modelbuilder 10

JimW1 — Thu, 19 May 2011 13:34:58 GMT

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.

Re: Joins do not work in Modelbuilder 10

GavinJackson1 — Thu, 02 Jun 2011 11:24:12 GMT

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.

Re: Joins do not work in Modelbuilder 10

JimW1 — Thu, 02 Jun 2011 12:17:37 GMT

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.

Re: Joins do not work in Modelbuilder 10

JimW1 — Thu, 09 Jun 2011 11:48:11 GMT

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

Re: Joins do not work in Modelbuilder 10

curtvprice — Thu, 09 Jun 2011 14:02:04 GMT

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!

Re: Joins do not work in Modelbuilder 10

JimW1 — Thu, 09 Jun 2011 19:48:29 GMT

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

Re: Joins do not work in Modelbuilder 10

curtvprice — Fri, 10 Jun 2011 16:29:58 GMT

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!

Re: Joins do not work in Modelbuilder 10

JimW1 — Tue, 14 Jun 2011 18:02:58 GMT

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.