Problem with AddJoin - "The Value Cannot Be A Table"

[Katie_Clark]

I am attempting to automate a process of exporting photos from a Survey123 geodatabase and give the output a custom filename, modified from the general script for exporting photos provided by Esri. Here is what I have so far. I am trying to join the Feature Layer, image_repeat table and image_repeat__ATTACH table before exporting the photos so that I can use fields that are in the feature class attribute table in the output file names. 

import arcpy
from arcpy import da
import os

featureTable = arcpy.GetParameterAsText(0) # the Feature layer
repeatTable = arcpy.GetParameterAsText(1) # the image_repeat table
attachTable = arcpy.GetParameterAsText(2) # the image_repeat__ATTACH table
fileLocation = arcpy.GetParameterAsText(3) # Output folder location for the photos

arcpy.AddJoin_management(repeatTable, "parentglobalid", featureTable, "globalid")
arcpy.AddJoin_management(repeatTable, "Nutrient_Bank_Monitoring.globalid", attachTable, "REL_GLOBALID")

with da.SearchCursor(repeatTable, ['DATA', 'ATT_NAME', 'ATTACHMENTID', 'site', 'plot']) as cursor:
    for item in cursor:
        attachment = item[0] # blob data type
        site = str(item[3])
        plotNum = str(item[4])
        filenum = "ATT" + str(item[2]) + "_" # i.e. ATT531_
        filename = site + "_Plot" + plotNum + "_" + filenum + str(item[1]) #i.e. miyagi_creek_Plot6_ATT531_plot_image-20200324-143302.jpg
        open(fileLocation + os.sep + filename, 'wb').write(attachment.tobytes())
        del item
        del filenum
        del filename
        del attachment‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

However, I get the following error message - "The value cannot be a table":

I have no problem doing the joins manually in ArcMap, so I'm unclear why it won't work in the script. Is it possible to do what I'm trying to do?

I am obviously still a python newbie, so many thanks in advance for any help or advice!

[DanPatterson_Retired]

Throw in a print statement to check the datatype or use

Describe object properties—ArcPy Functions | Documentation 

or use the describe object's dataType to limit the objects that you process

[DavidPike · Accepted solution]

Hi, the problem is you cant apply a join to a dataset, it must be a layer.

arcpy.MakeFeatureLayer(in_features, out_layer)

arcpy.MakeFeatureLayer(repeatTable, "repeatTable_lyr")

then swap out your join repeatable for the layer, "repeatTable_lyr" will exist in the memory workspace or a workspace you specify, you can also of course assign it to a variable.

https://pro.arcgis.com/en/pro-app/tool-reference/data-management/make-feature-layer.htm

[Katie_Clark]

Hi David, thanks for your response. 

I had to use the makeTableView function because I am working with a table rather than a layer:

repeatLayer = arcpy.MakeTableView_management(repeatTable, "repeatTable_lyr")‍

However, then I run into this error, 'NoneType' object has no attribute 'tobytes'

So I guess you can't use that function on a table view? If so, how can I access that information to be able to export the photo and assign it a custom file name?

[DavidPike]

A few print statements will give you a better idea. has that DATA column got any missing values or nulls? that would throw an exception.  If so use a bit of logic such as if type(attachment)… do this or a try Except block with a continue.

[Katie_Clark]

You're right, I used an IF statement and confirmed that all of the attributes for the DATA column are None type. The script runs successfully with this IF statement but just returns "No Attachment data" for all rows.

with da.SearchCursor(repeatLayer, ['plot_image_repeat__ATTACH.DATA', 'plot_image_repeat__ATTACH.ATT_NAME', 'plot_image_repeat__ATTACH.ATTACHMENTID', 'Nutrient_Bank_Monitoring.site', 'Nutrient_Bank_Monitoring.plot']) as cursor:
    for item in cursor:
        attachment = item[0] # blob data type
        site = str(item[3])
        plotNum = str(item[4])
        filenum = "ATT" + str(item[2]) + "_" # i.e. ATT531_
        filename = site + "_Plot" + plotNum + "_" + filenum + str(item[1]) #i.e. miyagi_creek_Plot6_ATT531_plot_image-20200324-143302.jpg
        if attachment != None:
            open(fileLocation + os.sep + filename, 'wb').write(attachment.tobytes())
        else:
            arcpy.AddMessage('No Attachment data')
        del item
        del filenum
        del filename
        del attachment‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

So, something must be going wrong with the join. (again, this issue does not occur when I do the joins manually). Any ideas?

Thanks again for the help!

[DavidPike]

You may have to persist the join by saving into into memory or scratch, if it was me I would save it as an output for the moment and inspect it manually to see what it looks like.

[Katie_Clark]

Turns out there was indeed something wrong with the join, I was referencing the wrong 'globalid' in the second join. Specifically, I had the globalid of the feature layer ("Nutrient_Bank_Monitoring.globalid") rather than the repeat table ("plot_image_repeat.globalid")

Here is my full script that works, just in case it might help someone else who comes across this thread:

import arcpy
from arcpy import da
import os

#all variables are from a file geodatabase exported from Survey123
featureTable = arcpy.GetParameterAsText(0) # the Feature layer
repeatTable = arcpy.GetParameterAsText(1) # the image_repeat table
attachTable = arcpy.GetParameterAsText(2) # the image_repeat__ATTACH table
fileLocation = arcpy.GetParameterAsText(3) # Output folder location for the photos

repeatLayer = arcpy.MakeTableView_management(repeatTable, "repeatTable_lyr")
attachLayer = arcpy.MakeTableView_management(attachTable, "attachTable_lyr")

arcpy.AddJoin_management(repeatLayer, "parentglobalid", featureTable, "globalid")
arcpy.AddJoin_management(repeatLayer, "plot_image_repeat.globalid", attachLayer, "REL_GLOBALID")
                
with da.SearchCursor(repeatLayer, ['plot_image_repeat__ATTACH.DATA', 'plot_image_repeat__ATTACH.ATT_NAME', 'plot_image_repeat__ATTACH.ATTACHMENTID', 'Nutrient_Bank_Monitoring.site', 'Nutrient_Bank_Monitoring.plot', 'plot_image_repeat.plot_direction']) as cursor:
    for item in cursor:
        attachment = item[0] # blob data type
        site = str(item[3])
        plotNum = str(item[4])
        plotDir = str(item[5])
        filenum = "ATT" + str(item[2]) + "_" # i.e. ATT531_
        filename = site + "_Plot" + plotNum + "_" + plotDir
        if attachment != None:
            open(fileLocation + os.sep + filename + '.jpg', 'wb').write(attachment.tobytes())
        else:
            arcpy.AddMessage('No attachment data for ' + site + ", Plot " + plotNum)
        del item
        del filenum
        del filename
        del attachment

                                                        
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[MatthewWoodmansee]

Thank you for posting the final version of the code!  I am currently working on a similar problem, but I have two questions based on your solution:
What is the "image_repeat_ATTACH" table?  I am trying this from Collector and have no repeat table at all.

It appears that you hard-coded the table names in the SearchCursor statement - is that correct?  I am trying to make this more variable and having issues getting the SearchCursor to read the fields correctly.

I have included my code below to show what I am trying to do in ArcCatalog 10.7.1. Thanks for any insight!

import arcpy

from arcpy import da

import os

inTable = arcpy.GetParameterAsText(0) # attachment table name (_ATTACH)

fileLocation = arcpy.GetParameterAsText(1) # folder of where the files will end up going

featureTable = arcpy.GetParameterAsText(2) # feature class holding the data we need.

featureLayer = arcpy.MakeFeatureLayer_management(featureTable, "feature_lyr") #create feature layer from feature class

attachLayer = arcpy.MakeTableView_management(inTable, "inTable_lyr") #create table view from attachment table

arcpy.AddJoin_management(attachLayer, "REL_OBJECTID", featureLayer, "OBJECTID") #join feature class to attachment table

with da.SearchCursor(attachLayer, ['attachLayer.DATA', 'attachLayer.ATT_NAME', 'attachLayer.REL_OBJECTID', 'featureLayer.ProjectNumber']) as cursor:

    for item in cursor:

        attachment = item[0]

        filenum = "ATT" + str(item[2]) + "_"

        Projectnum = str(item[3]) + "_"

        filename = filenum + Projectnum + str(item[1])

        open(fileLocation + os.sep + filename, 'wb').write(attachment.tobytes())

        del item

        del filenum

        del filename

        del Projectnum

        del attachment

[Katie_Clark]

Adding on to what David Pike‌ posted, I agree that my first method for troubleshooting something like that would be adding in some print statements to see what 'attachLayer.DATA' is actually returning. Just a (hopefully) helpful tip that if you want messages to print within the geoprocessing output details box, use the arcpy.AddMessage function. 

As for the repeat table, that is a result of file geodatabase coming from Survey123 rather than Collector. You use repeat questions when multiple photos/attachments are associated with a single survey entry. So, you're correct that you wouldn't have a repeat table when downloading data that was created in Collector for ArcGIS.