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

910
11
Jump to solution
04-26-2020 03:40 PM
Katherine_Clark
MVP Regular Contributor

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!

0 Kudos
1 Solution

Accepted Solutions
DavidPike
MVP Frequent Contributor

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

View solution in original post

11 Replies
DanPatterson_Retired
MVP Esteemed Contributor

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
MVP Frequent Contributor

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

Katherine_Clark
MVP Regular Contributor

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?

0 Kudos
DavidPike
MVP Frequent Contributor

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.

Katherine_Clark
MVP Regular Contributor

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!

0 Kudos
DavidPike
MVP Frequent Contributor

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.

Katherine_Clark
MVP Regular Contributor

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

                                                        
0 Kudos
MatthewWoodmansee
New Contributor II

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
0 Kudos
Katherine_Clark
MVP Regular Contributor

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. 

0 Kudos