Updating attribute fields in ModelBuilder using joins

[KarlKeough]

I am trying to update several data fields in a feature class using ModelBuilder. To update I need to join 5 different join layers in succession to the target feature class. Each of the 5 join layers have the same field names. The first join/Calculate Field works as expected. But successive joins/Calculate Fields seem to default back to the 1st join layer such that the fields in the target layer always get updated with only the data from the first join layer, I assume because it looks for the appropriate field name in the full joined target layer and finds the first field with that name. I have tried removing the joins after the Calculate Field operation is completed for each join layer and then creating a new join and Calculate Field for each successive join layer. I have also tried referencing the full field name (ie. Layername.Fieldname) to distinguish between each of the field names in the successive join layers. It still seems to want to use only the first referenced join layer/field in the 2-5 joins.

[shan_sarkar]

Are you performing one to one join or many to one join?

The 5 tables that you are trying to be join how many records does each of shapefiles/feature class or standalone tables have and how many records are you expecting the after the final join has been executed?

[KarlKeough]

Each feature class has approx. 2000 records, as does the final joined feature class.

[curtvprice]

Are these datasets named the same? Are you doing a Remove Join in between?

This is the sort of situation where you may want to create a sub model that takes your input table and a join table and does the join and calculate, followed by remove join, you could then set up an iterator to call the submodel.

Perhaps you could zip up your tbx file and share it here, it's a little unclear what your issue is.

[KarlKeough]

The target feature class and each of the join feature layers have different names. The fieldnames in each of the join feature layers are the same. I have tried doing a Remove Join but received an error message. Creation of a sub-model sounds interesting.

[KarlKeough · Accepted solution]

I was originally using the Join Field command. I tried using Add Join instead and it worked. It seems the Add Join command retains the full fieldname format (LayerName.Fieldname), which is required to distinguish each of the join layers since they all have the same fieldnames. The Join Field command seemed to refer only to FieldName, without reference to the Join Layer.

Thanks for all responses.  

[curtvprice]

You threw us off by mentioning Calculate Field, as the Join Field tool both adds a field (permanently) and calculates it.  

Add Join has the benefit that it is temporary and also tends to run a lot faster (especially if the join field is indexed). Remove Join makes no sense with Join Field because the Join Field tool makes a permanent join (nothing to remove!).

[KarlKeough]

Thanks for the response, Curtis.

I realized after a bit of digging that Join Field gives you a permanent join and as you point out, does not make sense with Remove Join. I’m a little confused on your point about Add Join both adding the join and calculating a field. I don’t see an option in the Add Join dialog for calculating a field. I used Add Join and then Calculate Field and things seem to be working fine.

Karl