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'

[DanPatterson]

Add Join (Data Management)—ArcGIS Pro | Documentation

suggests

To make a permanent join, either use the Join Field tool or use the joined layer as input to one of the following tools: Copy Features, Copy Rows, Export Features, or Export Table. 

but I assume that you don't want a permanent join

or for the field name thing

When saving the results to a new feature class or table, the Maintain fully qualified field names environment can be used to control whether the joined output field names will be qualified with the name of the table the field came from.

[LindsayRaabe_FPCWA]

Correct - I don't want a permanent join. And I thought Add Join was temporary (it appears in the list of joins which can then be removed). 

[DanPatterson]

it is temporary, you have to use the other options to make the join permanent (Join Field is permanent by default).

It is the "qualified field names" thing that appears to be the issue then

Maintain fully qualified field names (Environment setting)—ArcGIS Pro | Documentation

something that I thankfully don't have to deal with, but have a look since your last error line is indicating the bit 

L0Post_Planting_Inspection_Tracking___TEST.   

is missing from the preceding qualifier for

   .insp_2_weeks'

[LindsayRaabe_FPCWA]

Thanks. Will try it out. 

[LindsayRaabe_FPCWA]

No luck with that one. Layer output from Make Feature Layer is still missing the joined fields (e.g. table2.field) and the source table fields show as the original - not as table1.field name. 

[DanPatterson]

Then as a test, use one of the other options to that should persist the qualified field names (eg Copy Features) to see if you can at least finish your calculations etc.  A good "Delete" might seem like an extra step at the end, but perhaps a step needed to complete the task

Also, a standalone script is going to behave differently than a script attached to a toolbox within ArcGIS Pro

[AlfredBaldenweck]

Try using something like "map.AddLayer()" instead? I think AddJoin returns a layer or view object anyway. 

If that worked, you could just add a definition query after the fact.

[TonyAlmeida]

 Have you looked into arcpy.env.qualifiedFieldName

I would se the arcpy.management.CopyFeatures tool to create a temporary feature class that includes the joined fields.

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

# Create a temporary feature class to preserve the joined fields
temp_feature_class = r"in_memory\temp_feature_class"
arcpy.management.CopyFeatures(joined_table, temp_feature_class)

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

[DanPatterson]

I referred to that in an earlier thread

Maintain fully qualified field names (Environment setting)—ArcGIS Pro | Documentation

but Make Feature Layer (Data Management)—ArcGIS Pro | Documentation

on supports Current Workspace in Environment setting supported

whereas Copy Features (Data Management)—ArcGIS Pro | Documentation

supports a load more Environment settings

Current Workspace, Scratch Workspace, Output Coordinate System, Geographic Transformations, Extent, XY Resolution, XY Tolerance, Output has M values, M Resolution, M Tolerance, Output has Z values, Default Output Z Value, Z Resolution, Z Tolerance, Qualified Field Names, Output CONFIG Keyword, Maintain Attachments, Auto Commit, Output XY Domain, Output M Domain, Output Z Domain, Preserve Global IDs

So you have to delete what you copied if you don't want extra featureclasses floating around