arcpy Make Feature Layer not showing fields from joined table

[LindsayRaabe_FPCWA]

I'm trying to update fields in a feature service from a table stored in a gdb. I've done a join using arcpys AddJoin (GlobalID fields), and then want to use Make Feature Layer to filter data by features where features in field source.A don't match join.A field. 

But, when I do the MakeFeature Layer step, all the join fields disappear. Why? How can I preserve the join fields, filter my data and calculate values across?

...
# Update feature service

# Join Table2 to fs2 feature service using GlobalID field
joined_table = arcpy.management.AddJoin(fs2, "GlobalID", Table2, "GlobalID")

# Print all field names after the join
fields = arcpy.ListFields(joined_table)
print("Fields after join:")
for field in fields:
    print(field.name)

# Iterate through each field in the lookup_list
for field in lookup_list:
    source_field = lookup_dict[field]
    join_field = f"PostPlantingInspectionTracking_TargetTable.{source_field}"
    fs2_field = f"L0Post_Planting_Inspection_Tracking___TEST.{source_field}"
    
    # Create a feature layer with mismatched features
    where_clause = (
        f"({fs2_field} IS NULL AND {join_field} IS NOT NULL) OR "
        f"({fs2_field} <> {join_field})"
    )
    print(where_clause)
    mismatched_features = arcpy.management.MakeFeatureLayer(
        joined_table,
        "mismatched_features",
        where_clause=where_clause
    )
    
    # Print all field names in the feature layer
    layer_fields = arcpy.ListFields("mismatched_features")
    print("Fields of feature layer:")
    for field in layer_fields:
        print(field.name)
    
    # Update the features in the feature service with values from the join table
    with arcpy.da.UpdateCursor("mismatched_features", [fs2_field, join_field]) as cursor:
        for row in cursor:
            row[0] = row[1]
            cursor.updateRow(row)

print("Features updated successfully.")

Print statements showing fields from Line 11 (after join) and again at Line 35 (after Make Feature Layer using joined data). 

Fields after join:
L0Post_Planting_Inspection_Tracking___TEST.OBJECTID
L0Post_Planting_Inspection_Tracking___TEST.Plantation
L0Post_Planting_Inspection_Tracking___TEST.Forest
L0Post_Planting_Inspection_Tracking___TEST.Species
L0Post_Planting_Inspection_Tracking___TEST.PlantingYear
L0Post_Planting_Inspection_Tracking___TEST.RENDER_LABEL
L0Post_Planting_Inspection_Tracking___TEST.insp_2_weeks
L0Post_Planting_Inspection_Tracking___TEST.insp_4_weeks
L0Post_Planting_Inspection_Tracking___TEST.insp_6_weeks
L0Post_Planting_Inspection_Tracking___TEST.insp_8_weeks
L0Post_Planting_Inspection_Tracking___TEST.insp_10_weeks
L0Post_Planting_Inspection_Tracking___TEST.insp_12_weeks
L0Post_Planting_Inspection_Tracking___TEST.insp_4_months
L0Post_Planting_Inspection_Tracking___TEST.insp_5_months
L0Post_Planting_Inspection_Tracking___TEST.insp_6_months
L0Post_Planting_Inspection_Tracking___TEST.insp_9_months
L0Post_Planting_Inspection_Tracking___TEST.insp_12_months
L0Post_Planting_Inspection_Tracking___TEST.insp_15_months
L0Post_Planting_Inspection_Tracking___TEST.insp_18_months
L0Post_Planting_Inspection_Tracking___TEST.insp_21_months
L0Post_Planting_Inspection_Tracking___TEST.insp_24_months
L0Post_Planting_Inspection_Tracking___TEST.GlobalID
L0Post_Planting_Inspection_Tracking___TEST.PlantingFinished
L0Post_Planting_Inspection_Tracking___TEST.Creator
L0Post_Planting_Inspection_Tracking___TEST.CreationDate
L0Post_Planting_Inspection_Tracking___TEST.Editor
L0Post_Planting_Inspection_Tracking___TEST.EditDate
L0Post_Planting_Inspection_Tracking___TEST.Shape__Area
L0Post_Planting_Inspection_Tracking___TEST.Shape__Length
L0Post_Planting_Inspection_Tracking___TEST.Shape
PostPlantingInspectionTracking_TargetTable.OBJECTID
PostPlantingInspectionTracking_TargetTable.Plantation
PostPlantingInspectionTracking_TargetTable.Forest
PostPlantingInspectionTracking_TargetTable.Species
PostPlantingInspectionTracking_TargetTable.PlantingYear
PostPlantingInspectionTracking_TargetTable.RENDER_LABEL
PostPlantingInspectionTracking_TargetTable.insp_2_weeks
PostPlantingInspectionTracking_TargetTable.insp_4_weeks
PostPlantingInspectionTracking_TargetTable.insp_6_weeks
PostPlantingInspectionTracking_TargetTable.insp_8_weeks
PostPlantingInspectionTracking_TargetTable.insp_10_weeks
PostPlantingInspectionTracking_TargetTable.insp_12_weeks
PostPlantingInspectionTracking_TargetTable.insp_4_months
PostPlantingInspectionTracking_TargetTable.insp_5_months
PostPlantingInspectionTracking_TargetTable.insp_6_months
PostPlantingInspectionTracking_TargetTable.insp_9_months
PostPlantingInspectionTracking_TargetTable.insp_12_months
PostPlantingInspectionTracking_TargetTable.insp_15_months
PostPlantingInspectionTracking_TargetTable.insp_18_months
PostPlantingInspectionTracking_TargetTable.insp_21_months
PostPlantingInspectionTracking_TargetTable.insp_24_months
PostPlantingInspectionTracking_TargetTable.GlobalID
PostPlantingInspectionTracking_TargetTable.PlantingFinished
PostPlantingInspectionTracking_TargetTable.Creator
PostPlantingInspectionTracking_TargetTable.CreationDate
PostPlantingInspectionTracking_TargetTable.Editor
PostPlantingInspectionTracking_TargetTable.EditDate
PostPlantingInspectionTracking_TargetTable.Shape__Area
PostPlantingInspectionTracking_TargetTable.Shape__Length
PostPlantingInspectionTracking_TargetTable.join_field

where_clause:

(L0Post_Planting_Inspection_Tracking___TEST.insp_2_weeks IS NULL AND PostPlantingInspectionTracking_TargetTable.insp_2_weeks IS NOT NULL) OR (L0Post_Planting_Inspection_Tracking___TEST.insp_2_weeks <> PostPlantingInspectionTracking_TargetTable.insp_2_weeks)

Fields of feature layer:
OBJECTID
Plantation
Forest
Species
PlantingYear
RENDER_LABEL
insp_2_weeks
insp_4_weeks
insp_6_weeks
insp_8_weeks
insp_10_weeks
insp_12_weeks
insp_4_months
insp_5_months
insp_6_months
insp_9_months
insp_12_months
insp_15_months
insp_18_months
insp_21_months
insp_24_months
GlobalID
PlantingFinished
Creator
CreationDate
Editor
EditDate
Shape__Area
Shape__Length
Shape

Traceback (most recent call last):
File "C:\Temp\development.py", line 238, in <module>
for row in cursor:
RuntimeError: Cannot find field 'L0Post_Planting_Inspection_Tracking___TEST.insp_2_weeks'

[HaydenWelch]

I'm a bit late, but you should be able to just Select -> Copy

# Create the Join    
joined_table = arcpy.management.AddJoin(fs2, "GlobalID", Table2, "GlobalID")

# Iterate through each field in the lookup_list
for field in lookup_list:
    source_field = lookup_dict[field]
    join_field = f"PostPlantingInspectionTracking_TargetTable.{source_field}"
    fs2_field = f"L0Post_Planting_Inspection_Tracking___TEST.{source_field}"
    
    # Define mismatch clause
    where_clause = (
        f"({fs2_field} IS NULL AND {join_field} IS NOT NULL) OR "
        f"({fs2_field} <> {join_field})"
    )
    
    # Select the mismatched features
    selected_layer, _ = arcpy.management.SelectLayerByAttribute(joined_table, "NEW_SELECTION", where_clause)
    
    # Copy the mismatched features to a new feature layer
    mismatched_features = arcpy.management.CopyFeatures(selected_layer, "mismatched_features")
    
    # Update the features in the feature service with values from the join table
    with arcpy.da.UpdateCursor("mismatched_features", [fs2_field, join_field]) as cursor:
        for row in cursor:
            row[0] = row[1]
            cursor.updateRow(row)
print("Features updated successfully.")

Be warned, this will create a new featureclass per field in your lookup list. I'd do what @TonyAlmeida did and write these to an in memory featureclass, then insert the mismatched values into a more permanent 'Mismatch' featureclass that says what the mismatches are. If you're gonna do that though, you can skip the feature layer copy entirely and just iterate the selected layer with a da.SearchCursor

[RhettZufelt]

Could be somewhere else in the code?  Without the iterations and just using arcpy to join the tables and make a feature layer from it, it appears to be working as expected as well as maintaining the joined field FQ names.

Python 3.9.16 [MSC v.1931 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license()" for more information.
>>> import arcpy


>>> infc = r'Database Connections\PublicWorks.sde\PublicWorks.DBO.Streets\PublicWorks.DBO.Ramps_working'
>>> intbl = r'Database Connections\PublicWorks.sde\PublicWorks.DBO.ADA_Table'

>>> veg_joined_table = arcpy.management.AddJoin(infc, 'RecField', intbl,'Recnum')

>>> fields = arcpy.ListFields(veg_joined_table)


>>> for f in fields:
	  print (f.name)

	
PublicWorks.DBO.Ramps_working.OBJECTID
PublicWorks.DBO.Ramps_working.Corner
PublicWorks.DBO.Ramps_working.recnum
PublicWorks.DBO.Ramps_working.RecField
PublicWorks.DBO.Ramps_working.Note
PublicWorks.DBO.Ramps_working.checked
PublicWorks.DBO.Ramps_working.AsBuilt1
PublicWorks.DBO.Ramps_working.Photo
PublicWorks.DBO.Ramps_working.SHAPE
PublicWorks.DBO.ADA_Table.OBJECTID
PublicWorks.DBO.ADA_Table.Recnum
PublicWorks.DBO.ADA_Table.InvDate
PublicWorks.DBO.ADA_Table.Functional_Class
PublicWorks.DBO.ADA_Table.Cencus_Tract
PublicWorks.DBO.ADA_Table.Block
PublicWorks.DBO.ADA_Table.School_Walk_Route
PublicWorks.DBO.ADA_Table.Government_Facilities
PublicWorks.DBO.ADA_Table.MEF
PublicWorks.DBO.ADA_Table.Severity



>>> arcpy.management.MakeFeatureLayer(veg_joined_table, 'testjoin')
<Result 'testjoin'>

>>> fields2 = arcpy.ListFields('testjoin')

>>> for f in fields2:
	  print (f.name)

	
PublicWorks.DBO.Ramps_working.OBJECTID
PublicWorks.DBO.Ramps_working.Corner
PublicWorks.DBO.Ramps_working.recnum
PublicWorks.DBO.Ramps_working.RecField
PublicWorks.DBO.Ramps_working.Note
PublicWorks.DBO.Ramps_working.checked
PublicWorks.DBO.Ramps_working.AsBuilt1
PublicWorks.DBO.Ramps_working.Photo
PublicWorks.DBO.Ramps_working.SHAPE
PublicWorks.DBO.ADA_Table.OBJECTID
PublicWorks.DBO.ADA_Table.Recnum
PublicWorks.DBO.ADA_Table.InvDate
PublicWorks.DBO.ADA_Table.Functional_Class
PublicWorks.DBO.ADA_Table.Cencus_Tract
PublicWorks.DBO.ADA_Table.Block
PublicWorks.DBO.ADA_Table.School_Walk_Route
PublicWorks.DBO.ADA_Table.Government_Facilities
PublicWorks.DBO.ADA_Table.MEF
PublicWorks.DBO.ADA_Table.Severity

>>>

R_