topic Re: arcpy Make Feature Layer not showing fields from joined table in Python Questions

arcpy Make Feature Layer not showing fields from joined table

LindsayRaabe_FPCWA — Fri, 28 Feb 2025 04:26:01 GMT

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'

Re: arcpy Make Feature Layer not showing fields from joined table

DanPatterson — Fri, 28 Feb 2025 05:00:23 GMT

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.

Re: arcpy Make Feature Layer not showing fields from joined table

LindsayRaabe_FPCWA — Fri, 28 Feb 2025 05:03:33 GMT

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

Re: arcpy Make Feature Layer not showing fields from joined table

DanPatterson — Fri, 28 Feb 2025 05:09:29 GMT

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'

Re: arcpy Make Feature Layer not showing fields from joined table

LindsayRaabe_FPCWA — Fri, 28 Feb 2025 05:10:55 GMT

Thanks. Will try it out.

Re: arcpy Make Feature Layer not showing fields from joined table

LindsayRaabe_FPCWA — Fri, 28 Feb 2025 06:42:34 GMT

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.

Re: arcpy Make Feature Layer not showing fields from joined table

DanPatterson — Fri, 28 Feb 2025 12:51:06 GMT

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

Re: arcpy Make Feature Layer not showing fields from joined table

AlfredBaldenweck — Fri, 28 Feb 2025 14:10:35 GMT

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.

Re: arcpy Make Feature Layer not showing fields from joined table

TonyAlmeida — Fri, 28 Feb 2025 15:57:24 GMT

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)

Re: arcpy Make Feature Layer not showing fields from joined table

DanPatterson — Fri, 28 Feb 2025 17:06:21 GMT

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

Re: arcpy Make Feature Layer not showing fields from joined table

HaydenWelch — Sat, 01 Mar 2025 01:56:18 GMT

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

Re: arcpy Make Feature Layer not showing fields from joined table

RhettZufelt — Tue, 04 Mar 2025 00:14:12 GMT

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