Join Field (Data Management)

Summary

Permanently joins the contents of a table to another table based on a common attribute field. The input table is updated to contain the fields from the join table. You can select which fields from the join table will be added to the input table.

Usage

• The records in the Input Table value are matched to the records in the Join Table value based on the values of the Input Join Field and Join Table Field parameters. You can also select specific fields from the Join Table value to be appended to the Input Table value during the join.

• The Input Table value can be a feature class (including a shapefile) or a table.

• All fields in the Input Table value will be kept during the join. You can also select specific fields from the Join Table value to be added to the output. Use the Transfer Fields parameter to add these fields.

• Records from the Join Table value can be matched to more than one record in the Input Table value.

• If no fields are selected for the optional Transfer Fields parameter, all fields from the Join Table value will be joined to the output. To alter field names, aliases, or properties, set the Transfer Method parameter to Use field mapping.

• Joins can be based on fields of type text, date, or number.

• Joins based on text fields are case sensitive.

• Fields of different number formats can be joined as long as the values are equal. For example, a field of type float can be joined to a short integer field.

• When joined to an input table, fields from the join table with a Global ID type or an Object ID type will not be transferred.

  The Input Join Field value and the Join Table Field value can have different field names.

• If a join field has the same name as a field from the input table, the joined field will be appended with _1 (or _2, or _3, and so on) to make it unique.

• If the Input Table and Join Table parameter values have the same name, clicking the Validate Join button will fail with an error. This is a known limitation. However, the tool will run successfully when you click the Run button.

• If the Select transfer fields option is specified for the Transfer Method parameter and field values in the Join Table Field parameter value are not unique, only the first occurrence of each value will be used. To account for values other than the first occurrence (a one-to-many join), set the Transfer Method parameter to Use field mapping. To perform a one-to-many join, the Input Table parameter value must have an Object ID field and be in the same workspace as the Join Table parameter value.

• Use the Field Map parameter to manage the fields and their content in the output dataset.

  • Add and remove fields from the fields list, reorder the fields list, and rename fields.
  • The default data type of an output field is the same as the data type of the first input field (of that name) it encounters. You can change the data type to another valid data type.
  • Use an action to determine how the values from one or multiple input fields will be merged into a single output field. The available actions are First, Last, Concatenate, Sum, Mean, Median, Mode, Minimum, Maximum, Standard Deviation, and Count.
  • When using the Concatenate action, you can specify a delimiter such as a comma or other characters. Click the start of the Delimiter text box to add the delimiter characters.
  • Standard Deviation is not a valid option for single input values.
  • Use the Export option to save a field map as a .fieldmap file.
  • Use the Load option to load a .fieldmap file. The feature layer or dataset specified in the file must match the dataset used in the tool. Otherwise, the Field Map parameter will be reset.
  • Use the Slice Text button on text source fields to choose which characters from an input value will be extracted to the output field. To access the Slice Text button, hover over a text field in the input fields list; then specify the start and end character positions.
  • Fields can also be mapped in Python script.

• If the Field Map parameter is specified with the Join merge rule, there is no way to guarantee that the order of the joined values will be consistent with the row order of the Join Table Field parameter if the values are not unique. For example, if three features with the ANIMAL attribute values of mouse, cat, and dog are joined, the result will not necessarily be in the order mouse, cat, and dog.

• The Validate Join tool can be used to validate a join between two layers or tables to determine if the layers or tables have valid field names and Object ID fields, if the join produces matching records, if the join is a one-to-one or one-to-many join, and other properties of the join.

  A button to validate the join is available on the tool dialog box for ease of use.

• Indexing the input field and join field can improve performance. Use the Index Join Fields parameter to add or replace indexes.

• If the join results are unexpected or incomplete, review whether the input field and join field are indexed. If the fields are not indexed, try adding an index. If the fields are already indexed, try deleting and re-adding the index to correct any problems with the index. Use the Index Join Fields parameter to manage indexes while running the tool.

Parameters

Dialog

Label	Explanation	Data Type
Input Table	The table or feature class to which the join table will be joined.	Mosaic Layer; Raster Layer; Table View
Input Field	The field in the input table on which the join will be based.	Field
Join Table	The table that will be joined to the input table.	Mosaic Layer; Raster Layer; Table View
Join Field	The field in the join table that contains the values on which the join will be based.	Field
Transfer Fields (Optional)	The fields from the join table that will be transferred to the input table based on a join between the input table and the join table.	Field
Transfer Method (Optional)	Specifies how joining fields and field types will be transferred to the output. Select transfer fields—Fields and field types from the joined table will be transferred to the output. This is the default. Use field mapping—The transfer of fields and field types from the joined table to the output will be controlled by the Field Map parameter.	String
Field Map (Optional)	The fields that will be joined to the input table with their respective properties and source fields. All fields from the join table will be included by default. Use the field map to add, delete, rename, and reorder fields, as well as change other field properties. The field map can be used to combine values from two or more input fields into a single output field.	Field Mappings
Index Join Fields (Optional)	Specifies whether attribute indexes will be added or replaced for the input field and join field. Do not add indexes—Attribute indexes will not be added. This is the default. Add an attribute index for fields that do not have an existing index—An attribute index will be added for any field that does not have an index. Existing attribute indexes will be retained. Replace indexes for all fields—An attribute index will be added for any field that does not have an index. Existing attribute indexes will be replaced.	String

Derived Output

Label	Explanation	Data Type
Updated Input Table	The updated input dataset.	Table View; Raster Layer; Mosaic Layer

Python

arcpy.management.JoinField(in_data, in_field, join_table, join_field, {fields}, {fm_option}, {field_mapping}, {index_join_fields})

Name	Explanation	Data Type
in_data	The table or feature class to which the join table will be joined.	Mosaic Layer; Raster Layer; Table View
in_field	The field in the input table on which the join will be based.	Field
join_table	The table that will be joined to the input table.	Mosaic Layer; Raster Layer; Table View
join_field	The field in the join table that contains the values on which the join will be based.	Field
fields [fields,...] (Optional)	The fields from the join table that will be transferred to the input table based on a join between the input table and the join table.	Field
fm_option (Optional)	Specifies how joining fields and field types will be transferred to the output. NOT_USE_FM—Fields and field types from the joined table will be transferred to the output. This is the default. USE_FM—The transfer of fields and field types from the joined table to the output will be controlled by the field_mapping parameter.	String
field_mapping (Optional)	The fields that will be joined to the input table with their respective properties and source fields. All fields from the join table will be included by default. Use the field map to add, delete, rename, and reorder fields, as well as change other field properties. The field map can be used to combine values from two or more input fields into a single output field. In Python, use the FieldMappings class to define this parameter.	Field Mappings
index_join_fields (Optional)	Specifies whether attribute indexes will be added or replaced for the input field and join field. NO_INDEXES—Attribute indexes will not be added. This is the default. NEW_INDEXES—An attribute index will be added for any field that does not have an index. Existing attribute indexes will be retained. REPLACE_INDEXES—An attribute index will be added for any field that does not have an index. Existing attribute indexes will be replaced.	String

Derived Output

Name	Explanation	Data Type
out_layer_or_view	The updated input dataset.	Table View; Raster Layer; Mosaic Layer

Code sample

JoinField example 1 (Python window)

The following Python window script demonstrates how to use the JoinField function in immediate mode.

import arcpy
arcpy.env.workspace = "C:/data/data.gdb"
arcpy.management.JoinField("zion_park", "zonecode", "zion_zoning", "zonecode", 
                           ["land_use", "land_cover"])

JoinField example 2 (stand-alone script)

This stand-alone Python script shows the JoinField function used to join a table to a feature class and only include two of the table's fields in the join.

# PermanentJoin.py
# Purpose: Join two fields from a table to a feature class 

# Import system modules
import arcpy

# Set the current workspace 
arcpy.env.workspace = "c:/data/data.gdb"

# Set the local parameters
inFeatures = "zion_park"
joinField = "zonecode"
joinTable = "zion_zoning"
fieldList = ["land_use", "land_cover"]

# Join two feature classes by the zonecode field and only carry 
# over the land use and land cover fields
arcpy.management.JoinField(inFeatures, joinField, joinTable, joinField, 
                           fieldList)

Environments

Auto Commit, Current Workspace