ArcGIS Desktop Script Tool fails when I run MakeQueryTable_management. How to read intermediate outputs?

Question

I am seeking some help with fixing my Python script to allow it to run as an ArcGIS Script Tool. Let me first say that the script runs without issue outside of ArcGIS.

The section giving me grief inside the Script Tool is simply joining a few tables to make one long table, which I will later query and export to an Excel template (the end goal).

I basically feed in two tables, join them using acrpy.MakeQueryTable_management, and then save the output as a new table with arcpy.CopyRows_management (either a temporary table to save to a GDB)

The script does this three times in three similarly structured blocks of code, using the output of the previous arcpy.CopyRows_management as an input in the next arcpy.MakeQueryTable, in order to join more fields. The script is failing when running arcpy.MakeQueryTable_management the second time, giving the error message:

ExecuteError: Failed to execute. Parameters are not valid. ERROR 000152: Invalid input data Failed to execute (MakeQueryTable)

Again, the first pass works fine, but it fails on the second. As best I can tell, the script fails because it does not read/recognize the table from the first arcpy.MakeQueryTable_management /arcpy.CopyRows_management, therefore the input data is seen as not valid

If I run each block one at a time, it works fine.

How do I pass the table along so it runs all at once?

I think it is as simple as that, but I am at a loss.

I’ve looked at these discussions for some help, and it’s been useful, but hasn’t helped me sort out my problem.

https://community.esri.com/t5/python-questions/make-query-table-works-in-python-window-but-not-as-tool/m-p/365787

https://community.esri.com/t5/geoprocessing-questions/arcpy-scripting-works-in-python-window-but-not-in-script/td-p/694518

import arcpy, csv
import os
from datetime import datetime
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

startTime = datetime.now()

arcpy.env.workspace = arcpy.GetParameterAsText(0)
arcpy.env.overwriteOutput = True

#Tables to join. These tables will be joing to make a new Query Table. 
tableList = ["Gt_Locations", "Gt_Samples"]

##Fields to include in Query table
fieldList = [["Gt_Locations.Location_ID",],
            ["Gt_Locations.Location_Name",'Location_Name'],
            ["Gt_Locations.Type_Code"],
            ["Gt_Locations.X_2", 'X'],
            ["Gt_Locations.Y_2", 'Y'],
            ["Gt_Samples.Location_ID", 'Location_ID_'],
            ["Gt_Samples.Sample_date", 'Sample_date']]

##Define matching fields to use in join and use keyField as unique ID
whereClause = "Gt_Locations.Location_ID = Gt_Samples.Location_ID"
keyField = "Gt_Samples.Sample_ID"
lyrName = arcpy.GetParameterAsText(1)    ##Default to SampleID_Key_CopyRows

##Execute the join with MakeQueryTable and define new table name with lyrName variable
arcpy.MakeQueryTable_management(tableList, lyrName, "USE_KEY_FIELDS", keyField, fieldList, whereClause)
arcpy.CopyRows_management(lyrName, lyrName)
    
##This block joines the Results table table with QueryTable from above. 
tableList2 = ["SampleID_Key_CopyRows", "Gt_Results"]

fieldList2 =  [["SampleID_Key_CopyRows.Location_Name", 'Location_Name'],
               ["SampleID_Key_CopyRows.Gt_Locations_Type_Code", 'Type_Code'],
               ["SampleID_Key_CopyRows.Gt_Samples_Sample_ID", 'Sample_ID'],
               ["SampleID_Key_CopyRows.Sample_date", 'Sample_date'],
               ["SampleID_Key_CopyRows.X", 'X'],
               ["SampleID_Key_CopyRows.Y", 'Y'],
               ["Gt_Results.Value_txt", 'Value_txt'],
               ["Gt_Results.Unit_ID", 'Unit_ID'],
               ["Gt_Results.Analyte_ID", 'Analyte_ID'],
               ["Gt_Results.Sample_ID_D", 'Sample_ID_D'],
               ["Gt_Results.Result_ID", 'Result_ID']]

whereClause2 = "SampleID_Key_CopyRows.Gt_Samples_Sample_ID = Gt_Results.Sample_ID_D"
keyField2 = "Gt_Results.Result_ID"
lyrName2 = arcpy.GetParameterAsText(2)   ## Default to Results_Key_CopyRows

arcpy.MakeQueryTable_management(tableList2, lyrName2, "USE_KEY_FIELDS", keyField2, fieldList2, whereClause2)    ##THIS IS WHERE THE ERROR IS REPORTED
arcpy.CopyRows_management(lyrName2, lyrName2)

Answer 1

Solved my issue. I just had to reference the actual location of the first input, not just the output name. I thought I could simply reference the output name because it was saved to my defined workspace.

Here the change:

lyrName = arcpy.GetParameterAsText(0) ##Default to SampleID_Key_CopyRows

##Execute the join with MakeQueryTable and define new table name with lyrName variable
arcpy.MakeQueryTable_management(tableList, lyrName, "USE_KEY_FIELDS", keyField, fieldList, whereClause)
arcpy.CopyRows_management(lyrName, "SampleID_Key_CopyRows")

SampleID_Location = dir + '\\' + str(lyrName)