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:

      KeywordDescription

      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

LabelExplanationData 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

LabelExplanationData 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

arcpy.management.ValidateJoin(in_layer_or_view, in_field, join_table, join_field, {output_msg})
NameExplanationData 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

NameExplanationData 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

Related topics