Validate Join (Data Management)

Summary

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

This tool does not produce a join; it analyzes a potential join with the current data. Since all joins can potentially become one-to-many, the layer properties will always show cardinality one-to-many. A join can change from one-to-one to one-to-many if the data changes. The join being validated by this tool can be created using the Add Join or Join Field tool. This tool will report the join validation results as messages and optionally as an output table.

Usage

• This tool will create messages to indicate characteristics of the validated join, including the following:

  • The row count and match count of the specified join between the input layer or table view and the join table. If the join does not produce any matches, a warning message will be included. The row count and match count are also returned as derived outputs of the tool.
  • If the join tables do not have Object IDs, a warning message will be included.
  • The cardinality of the join (if it produces one-to-one results or one-to-many results).
  • If the join fields have invalid starting characters or problematic characters anywhere in their names, a warning message will be included.
  • If the join fields use reserved SQL keywords, a warning message will be included.
  • The join fields are indexed or do not have an attribute index. Field indexes provide optimal performance for some data formats.
  • The join tables are from the same workspace. When the input layer or table view and the join table are stored in the same workspace or database, the performance of the join will be considerably faster. Joins between tables in different databases is possible but performance will be reduced, as the database cannot be used to perform the join.

• This tool can produce an optional output table listing the problems found in the validated join. The output table will have the following fields:

  • TYPE—A keyword used to indicate a specific characteristic or issue found in the validated join. Values include the following:

    Keyword	Description
    GPM_INVALID_CHARACTER_IN_NAME	The field has an invalid character in its name.
    GPM_INVALID_FIRST_CHARACTERS_MSG	The field has an invalid first character.
    GPM_RESERVED_SQL_KEYWORD	The field includes reserved SQL keywords in its name.
    GPM_NO_MATCH_JOIN	The join does not produce any matches.
    GPM_NO_OBJECTID_JOIN	The layer or table view does not have an Object ID field.
    GPM_NOT_INDEX_FIELD	The field is not indexed.
    DIFFERENT_WORKSPACE	The tables are stored in different workspaces or databases.

  • TABLE_NAME—The name of the table that produced the join validation messages or warning.
  • FIELD_NAME—The name of the field in the input layer, table view, or join table that produced the join validation message or warning.
  • DESC—Further description of the validation message or warning, including information about how to resolve a problem.

• The cardinality of the join (whether it is one-to-one or one-to-many) will be validated by this tool. Records from the join table can be matched to more than one record in the input layer or table view. Likewise, multiple records from the join table can be matched to one record in the input layer or table view, producing a one-to-many join.

• When a one-to-many join is produced by the join, the result of the join can be viewed in the attribute table, where a warning message will indicate if the table has duplicate Object IDs. Because many geoprocessing tools do not support data with duplicate Object IDs and processing such data can produce unexpected results, it is recommended that you first copy the joined layer to a new feature class using the Export Features tool. Then use the new feature class as input to other geoprocessing tools.

Parameters

Dialog

Label	Explanation	Data Type
Input Layer or Table View	The layer or table view with the join to the join table that will be validated.	Mosaic Layer; Raster Layer; Table View
Input Join Field	The field in the input layer or table view on which the join will be based.	Field
Join Table	The table or table view with the join to the input layer or table view that will be validated.	Mosaic Layer; Raster Layer; Table View
Join Table Field	The field in the join table that contains the values on which the join will be based.	Field
Output Table (Optional)	The output table containing the validation messages in a tabular form.	Table

Derived Output

Label	Explanation	Data Type
Match Count	The number of unique records from the input layer or table matching a record in the join layer or table.	Long
Row Count	The number of records produced by the join between the input and join layers or tables. One-to-many joins will count each matching pair of records. Due to one-to-many joins, the output row count may be larger than the match count.	Long

Python

arcpy.management.ValidateJoin(in_layer_or_view, in_field, join_table, join_field, {output_msg})

Name	Explanation	Data Type
in_layer_or_view	The layer or table view with the join to the join table that will be validated.	Mosaic Layer; Raster Layer; Table View
in_field	The field in the input layer or table view on which the join will be based.	Field
join_table	The table or table view with the join to the input layer or table view that will be validated.	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
output_msg (Optional)	The output table containing the validation messages in a tabular form.	Table

Derived Output

Name	Explanation	Data Type
match_count	The number of unique records from the input layer or table matching a record in the join layer or table.	Long
row_count	The number of records produced by the join between the input and join layers or tables. One-to-many joins will count each matching pair of records. Due to one-to-many joins, the output row count may be larger than the match count.	Long

Code sample

ValidateJoin example 1 (Python window)

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

import arcpy
arcpy.management.ValidateJoin("vegetation", "HOLLAND95", "vegtable", "HOLLAND95")
arcpy.management.AddJoin("vegetation", "HOLLAND95", "vegtable", "HOLLAND95")

ValidateJoin example 2 (stand-alone script)

This stand-alone script shows the ValidateJoin function as part of a workflow to join a table to a feature class.

# Name: AttributeJoin.py
# Purpose: Join a table to a feature class and find one-to-many matches

# Import system modules
import arcpy

# Set environment settings
arcpy.env.workspace = "C:/data/Habitat_Analysis.gdb"
arcpy.env.qualifiedFieldNames = False

# Set local variables
inFeatures = "vegtype"
joinTable = "vegtable"
joinField = "HOLLAND95"  # Both tables have HOLLAND95 field
outFeatures = "Vegtype_Joined"

# Join the feature layer to a table
val_res = arcpy.management.ValidateJoin(inFeatures, joinField, joinTable, joinField)
matched = int(val_res[0]) 
row_count = int(val_res[1])

print(arcpy.GetMessages())  # Tool messages about the Join

# Validate the join returns matched rows before proceeding
if matched >= 1:
    joined = arcpy.management.AddJoin(inFeatures, joinField, joinTable, joinField)

    # Copy the joined layer to a new permanent feature class
    arcpy.management.CopyFeatures(joined, outFeatures)

print(f"Output Features: {outFeatures} had matches {matched} and created {row_count} records")

Environments

Current Workspace