topic Re: Script Tool to calculate field with selected rows not working in Python Questions

Script Tool to calculate field with selected rows not working

MichaelBowers — Wed, 05 Jun 2024 16:17:56 GMT

Although having a few years experience using ArcGIS Pro, I just started learning Python a few weeks ago so if this is all over the place... bare with me I'm doing the best I can right now.

I am trying to create a Script Tool that will 1) select records in my feature layer based on a date range in a date field (date only), and then 2) calculate a field for those selected records ONLY to a desired string that the layer's symbology is based off of.

The script:

# Process: Select Layer By Date And Time (Select Layer By Date And Time) (ca) Project_Selection, Row_Count = arcpy.ca.SelectLayerByDateAndTime(in_layer_or_view=Project_Layer, selection_type="NEW_SELECTION", time_type="SINGLE_TIME_FIELD", date_field="Date", selection_options=["DATE"], date_selection_type="DATE_RANGE", start_date=StartDate, end_date=EndDate) # Process: Calculate Field with Selection (Calculate Field) (management) Project_Calc = arcpy.management.CalculateField(in_table=Project_Selection, field="Symbology", expression="\"Beyond 90\"")

This is the result of my Script Tool right now. It has the records selected accurately based on my date range, but calculates the entire layer's Symbology field to "Beyond 90" when I need it to only calculate the selected records.

If I perform this process manually, the calculate field tool will use the 18 selected records and update those 18 selected records' fields only.

Please let me know if you have any suggestions.

Re: Script Tool to calculate field with selected rows not working

AlfredBaldenweck — Wed, 05 Jun 2024 16:29:45 GMT

It's hard to tell without the rest of the code what's going on, but my guess is that your Project_Layer variable isn't actually a layer object, but is the feature class itself.

aprx = arcpy.mp.ArcGISProject("CURRENT") mp = aprx.activeMap Project_Layer = mp.listLayers("layerName")[0] '''NOT''' Project_Layer = r"C:\...\project.gdb\layerName"

If this isn't the case, please post more of your code so we can diagnose it better.

Re: Script Tool to calculate field with selected rows not working

BlakeTerhune — Wed, 05 Jun 2024 18:48:49 GMT

This is likely the issue. The other way to accidentally use the feature class instead of the layer is when choosing the value in the parameter at run time of the script tool. I'm assuming you have a parameter for "Project_Layer". If you're browsing to the feature class in a geodatabase for this parameter instead of choosing it from the list of layers in the map, then it'll calculate on all records (without the selection). Also, if you have the same feature class added to the map in more than one layer and you choose the wrong one (without a selection), it will calculate on all records.

So there's multiple ways to mess this up. It might be worth adding some validation in your script tool to check if the layer has a selection. You can use the getSelectionSet() method on the Layer object.

Re: Script Tool to calculate field with selected rows not working

MichaelBowers — Mon, 10 Jun 2024 19:07:26 GMT

@AlfredBaldenweck I think you are right about my project layer variable being a feature class and not a layer object - the data type for that Project_Layer variable is a "File Geodatabase Feature Class." I've tried to incorporate your code into my script tool, but I'm getting the same result. I must be missing something. When I enter this code into ArcGIS Pro python window one line at a time, it works, but when I run it as the script tool it still calculates all (selected and unselected) records.

I'm putting all of the code this time. Here are some notes for context:

The input parameter (Data Type = Feature Layer) of the script tool is to be an existing Project Layer that has a symbology field with unique values. The symbology field needs to be updated over time relative to the current date. So, as time goes on, I want to be able to automate this process. Also... the layer's current date field is actually a text field, so I add a new "Date Only" field and calculate it equal to that "text-date" field. But, of course, the main core issue I'm having is the last calculate field process applying to the entire layer/feature class and not the selected records.

import arcpy def UpdateSymbology(): # To allow overwriting outputs change overwriteOutput option to True. arcpy.env.overwriteOutput = True # Model Environment Setting aprx = arcpy.mp.ArcGISProject("CURRENT") mp = aprx.activeMap Projects = arcpy.GetParameterAsText(0) # Input parameter Projects_Layer = mp.listLayers(Projects)[0] # Process: Add Field to Projects layer that is Date Only type (Add Field) (management) Projects_Layer_1 = arcpy.management.AddField(in_table=Projects_Layer, field_name="Date", field_type="DATEONLY", field_alias="Date") # Process: Calculate added Date Only field equal to the exisiting 'Start Date' text field (Calculate Field) (management) Projects_Layer_2 = arcpy.management.CalculateField(in_table=Projects_Layer_1, field="Date", expression="!Start_Date!") # Process: Select Layer By Date And Time (Select Layer By Date And Time) (ca) Projects_Layer_3, Row_Count = arcpy.ca.SelectLayerByDateAndTime(in_layer_or_view=Projects_Layer_2, selection_type="NEW_SELECTION", time_type="SINGLE_TIME_FIELD", date_field="Date", selection_options=["DATE"], date_selection_type="DATE_RANGE", start_date=StartDate, end_date=EndDate) # Process: Calculate Field with Selection to "Beyond 90" (Calculate Field) (management) Projects_Layer_4 = arcpy.management.CalculateField(in_table=Projects_Layer_3, field="Symbology", expression="\"Beyond 90\"") if __name__ == '__main__': UpdateSymbology_() print("FIN")

Re: Script Tool to calculate field with selected rows not working

MichaelBowers — Mon, 10 Jun 2024 19:07:07 GMT

@BlakeTerhune Thank you for this information. I may have this messed up in one of the ways you mentioned... I do have a parameter (the only parameter) for the Project_Layer, and it is chosen at run time. However, I do select the layer from the script tool's drop down list of layers in the current map. Also, the only layer I have in the map is the one project layer, so I can rule out any confusion there.

I might need to add some validation like you have suggested, but I am certain that the records get selected. Referring to my original screenshot - once the tool is ran I can see that the records I want to select are accurately and successfully selected. And this selection step executes prior to the final field calculation in the script... So, I'm still a bit confused/lost but I know there has to be something I'm missing.

Re: Script Tool to calculate field with selected rows not working

BlakeTerhune — Mon, 10 Jun 2024 23:03:07 GMT

In this latest code, StartDate and EndDate are undefined when you call them in SelectLayerByDateAndTime().

Re: Script Tool to calculate field with selected rows not working

MichaelBowers — Tue, 11 Jun 2024 11:42:17 GMT

I have them defined earlier in the code, before the 'def UpdateSymbology()' function is called. I didn't include that in the latest code I posted. My apologies - I should've mentioned that. The selection works and is accurate, reflected in my original post's screenshot.

Re: Script Tool to calculate field with selected rows not working

BlakeTerhune — Tue, 11 Jun 2024 14:35:39 GMT

Looking at the documentation, the sample code does not use the output layer for anything. Try without chaining the outputs, only use the input layer.

import arcpy def UpdateSymbology(): # To allow overwriting outputs change overwriteOutput option to True. arcpy.env.overwriteOutput = True # Model Environment Setting aprx = arcpy.mp.ArcGISProject("CURRENT") mp = aprx.activeMap Projects = arcpy.GetParameterAsText(0) # Input parameter Projects_Layer = mp.listLayers(Projects)[0] # Process: Add Field to Projects layer that is Date Only type (Add Field) (management) arcpy.management.AddField( in_table=Projects_Layer, field_name="Date", field_type="DATEONLY", field_alias="Date" ) # Process: Calculate added Date Only field equal to the exisiting 'Start Date' text field (Calculate Field) (management) arcpy.management.CalculateField(in_table=Projects_Layer, field="Date", expression="!Start_Date!") # Process: Select Layer By Date And Time (Select Layer By Date And Time) (ca) arcpy.ca.SelectLayerByDateAndTime( in_layer_or_view=Projects_Layer, selection_type="NEW_SELECTION", time_type="SINGLE_TIME_FIELD", date_field="Date", selection_options=["DATE"], date_selection_type="DATE_RANGE", start_date=StartDate, end_date=EndDate ) # Process: Calculate Field with Selection to "Beyond 90" (Calculate Field) (management) arcpy.management.CalculateField(in_table=Projects_Layer, field="Symbology", expression="Beyond 90") if __name__ == '__main__': UpdateSymbology() print("FIN")

Re: Script Tool to calculate field with selected rows not working

MichaelBowers — Wed, 12 Jun 2024 12:42:00 GMT

I tried what you suggested but unfortunately got the same result. The tool ran successfully but all records were calculated again.

Re: Script Tool to calculate field with selected rows not working

AlfredBaldenweck — Wed, 12 Jun 2024 12:48:15 GMT

Could you share a screenshot of your parameter list so that we're all on the same page?

Re: Script Tool to calculate field with selected rows not working

MichaelBowers — Wed, 12 Jun 2024 13:01:34 GMT

Sure thing. Here is the parameter in the properties pane and in the geoprocessing pane.

Re: Script Tool to calculate field with selected rows not working

MichaelBowers — Wed, 12 Jun 2024 15:13:07 GMT

I've looked at trying to use da.UpdateCursor instead of Field Calculator, as described in this post I found: https://gis.stackexchange.com/questions/298714/using-select-by-attributes-then-calculate-field-on-selection-in-arcpy

My attempts were unsuccessful thus far, but I'm not familiar with SQL, Python 'for' loops, nor the 'with' and 'as' Python keywords at all so I'm probably not setting up the update cursor properly.

Could this tool work instead of calculate field?

Re: Script Tool to calculate field with selected rows not working

BlakeTerhune — Wed, 12 Jun 2024 15:15:56 GMT

Since you are choosing a layer as your input parameter, there's no need to get the layer from the Project again.

def UpdateSymbology(): # To allow overwriting outputs change overwriteOutput option to True. arcpy.env.overwriteOutput = True # Model Environment Setting Projects_Layer = arcpy.GetParameter(0) # Input parameter # ... rest of the code ...

Re: Script Tool to calculate field with selected rows not working

MichaelBowers — Wed, 12 Jun 2024 15:21:58 GMT

Alright, I've removed that portion from the tool. I ran it again but still calculating for all records.

Re: Script Tool to calculate field with selected rows not working

BlakeTerhune — Wed, 12 Jun 2024 17:02:37 GMT

I have a question about your logic. You are creating a new field called DATE (which is a reserved word, so I suggest you pick a different name) and setting all the (selected) records to the same value: StartDate

Then you are making a selection on that same DATE field where the value is between StartDate and EndDate. Wouldn't that always select everything you just calculated? Why do you need the first step to create and calc the DATE field? Here's an example using an UpdateCursor and a where clause. I didn't put in a field name because I'm not sure what you actually want to compare StartDate to.

expression = f"your_field_name >= date '{StartDate}' and your_field_name <= date '{EndDate}'" with arcpy.da.UpdateCursor(Project_Layer, ["Symbology"], where_clause=expression) as u_cursor: for row in u_cursor: u_cursor.updateRow(["Beyond 90"])

This SQL expression should work in a file geodatabase and SQL Server. I'm not sure what dbms your data is in.

Re: Script Tool to calculate field with selected rows not working

MichaelBowers — Wed, 12 Jun 2024 20:07:42 GMT

I needed the first step to add the DATE field because SelectLayerByDateAndTime() requires the input field to be a date field. The existing field in my Projects layer (!Start_Date!) is a Text field, so that select tool won’t run using it.

My logic was to add a Type = Date Only field to the layer and calculate it equal to the values in the !Start_Date! text field. This all occurred before any selection took place. Once created, I could use the {date_selection_type} option to select these dates relative to a date range. I assigned variables to {start_date} and {end_date} using the datetime lib, date.today(), today.strftime(), datetime.datetime(), and datetime.timedelta() to fetch the current date, change it “x” amount of days, and return a string to input into those parameters. The selection works! But, I can’t get it to calculate those selected records. I still feel like the SelectLayerByDateAndTime() -> CalculateField() method should work… and still don't know why what I was doing did not...

Your UpdateCursor with SQL where clause works! It updates only those 18 records that qualify in my date range. I don’t even need any selection either since it just reads the data, so I removed that completely. I’m sure I was making this more complicated than it needed to be, but within the limits of my Python and ArcGIS Pro knowledge what I was doing made sense to me.

@BlakeTerhune and @AlfredBaldenweck thank you for the help. This is the first Script Tool I've ever written and I wouldn't have been able to figure it out on my own.