Finding all records related to selected records in ArcGIS Desktop using ModelBuilder?

Question

I have two layers that are related. My current manual process is as follow;

a. Add the layer 1 to the table of contents

b. Open the attribute table then Switch selection to select all layer 1 records

c. Click relates and select layer 2 This will select all layer 2 records related to the layer 1

d. Switch selection This will display all layer 2 records not related to the layer 1

e. Export the table to excel

I want to automate this using ModelBuilder.

This is where I am using ModelBuilder;

Layer 1 > Select Layer By Attribute (Switch selection) > Calculate Field (I require a python script that is able select all related layer 2 records) > Select Layer By Attribute (Switch Selection) > Export to Excel

Answer 1

You can solve your task using Python without the need for relates or joins. Function below will select rows from layer 2 that are not in layer 1 based on a common field. The selection can then be exported to Excel. Let me know if you only want to use ModelBuilder and I will delete my answer.

import arcpy

def findunique(layer1, layer2, idfield):
    #Select rows from layer2 that are not in layer1 based on a idfield
    layer1_ids = [i[0] for i in arcpy.da.SearchCursor(layer1, idfield)]
    oids_to_select = []
    with arcpy.da.SearchCursor(layer2,['OID@',idfield]) as cursor:
        for row in cursor:
            if row[1] not in layer1_ids:
                oids_to_select.append(row[0])
    sql = """{0} IN ({1})""".format(arcpy.AddFieldDelimiters(layer2,arcpy.Describe(layer2).OIDFieldName),", ".join(map(str,oids_to_select)))
    arcpy.SelectLayerByAttribute_management(in_layer_or_view=layer2, 
                                           where_clause=sql)