FeatureClassToFeatureClass not respecting selection (from .py script)

Question

I have ArcGIS Pro 2.5 right now, and there is a problem with having two different results when I execute code from the Python window vs. executing from a script.  The script version will not respect the selection when I copy one geodatabase class to a new one.

I have a point table called "spatial_join1" with 222 points.  I use arcpy.management.SelectLayerByAttribute to select 5 records.  That command works the same in the Python window and in the script.  I then use arcpy.onversion.FeatureClassToFeatureClass() to save those 5 records as a new layer.  If I execute that command in the Python window, I get 5 features in the new layer.  When I run the script with the same command, I get the full 222 record set.

(this is the Python window version)

(this is the script version).

Am I using the wrong tool, or do I need a line in my script file to tell it to respect the selection?

Thanks for the help--

ChadChristensen · Answer

I found the answer in some very obscure text in the explanation of the tool.  This tool, when run as a script, will only show up in a DERIVED OUTPUT which has its own special conditions.  You can't just create the join and expect to see it reflected in the original layer.  The text says this:

"To see the results of a join created in a script tool, the tool must include the layer as a derived output parameter. "

I wish this was more clear on the tool page, because this took days worth of struggle to work out.

danretired · Answer

strange... maybe try an intermediate "make feature layer" or write to "memory" workspace first

Write geoprocessing output to memory—ArcGIS Pro | Documentation

ChadChristensen · Answer

Thank you, Dan - I appreciate the help.  I did try three different tools for this project:

arcpy.conversion.FeatureClassToFeatureClass,

arcpy.management.CopyFeatures,

arcpy.conversion.FeatureClassToGeodatabase

and they all had the same problem.  I also tried a local file vs server file, with no change.  When I reduced the script down to the two commands (select, and copy) I still had the problem.  It's as if the selection command is holding the file hostage and not 'finishing' before it moves on to the copy command.  When I put the two commands in different scripts, and run them separately, they work (much like putting the two commands in the Python window).  Also, I have put the two commands in two separate functions, and no luck.  If you have any insight into forcing the select command to 'complete', I would be grateful!