topic Re: Problem with AddJoin - "The Value Cannot Be A Table" in Python Questions

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

Katie_Clark — Sat, 11 Dec 2021 06:45:04 GMT

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!

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

DanPatterson_Retired — Mon, 27 Apr 2020 00:36:50 GMT

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

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

DavidPike — Mon, 27 Apr 2020 11:46:55 GMT

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

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

Katie_Clark — Tue, 28 Apr 2020 17:06:02 GMT

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?

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

DavidPike — Tue, 28 Apr 2020 17:28:08 GMT

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.

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

Katie_Clark — Sat, 11 Dec 2021 06:45:07 GMT

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!

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

DavidPike — Tue, 28 Apr 2020 18:04:02 GMT

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.

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

Katie_Clark — Sat, 11 Dec 2021 06:45:09 GMT

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

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

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

MatthewWoodmansee — Thu, 04 Jun 2020 16:04:14 GMT

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

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

DavidPike — Thu, 04 Jun 2020 16:20:00 GMT

You can reference the data via a cursor through the dataset path itself, not even sure if it works on a layer. Also save the layer after the join then use that path in the cursor.

for fields input into the cursor you can pass in a list of field names as strings:

cursor_fields = ["Field1", "Field2", "Field3"]

with arcpy.da.cursor(path, cursor_fields) as cursor...

throw some print statements into the variables you're creating in the cursor, my guess is your final path is ending up a bit funky after concatenation, i'd use os.path.join instead.

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

Katie_Clark — Thu, 04 Jun 2020 16:39:12 GMT

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.

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

MatthewWoodmansee — Fri, 05 Jun 2020 15:26:05 GMT

Thank you both David and Katherine for your help. I am posting my final code (with commented-out manual data tests and print statements) in case this will help anyone else. I added in an "If" statement for the user to chose a project number (there will be lots of different projects using this one Collector application). I also used a lot of the code from another thread (I had forgotten how helpful list comprehensions are!: Is it possible to use a field for the filename of an attachment export?

Here is my code - thanks again!

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.

ProjectNum = arcpy.GetParameterAsText(3) # project number user wants to export for

#featureTable = r"R:\Connections\GIS_Shared_Data.sde\GIS_shared_data.DBO.GlobalData_UTM\GIS_shared_data.DBO.AOC_testing"

featureFields = ['OBJECTID', 'ProjectNumber']

#use list comprehension to build a dictionary from SearchCursor

featureDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(featureTable, featureFields)}

#print featureDict

#related table, and the fields we want from that table

#inTable = r'R:\Connections\GIS_Shared_Data.sde\GIS_shared_data.DBO.AOC_testing__ATTACH'

TableFields = ['REL_OBJECTID', 'DATA', 'ATT_NAME']

#location of the folder where we place the attachments

#fileLocation = r'C:\Users\xxxxx\Desktop\TestingData\AttachmentTest'

#now run through the dictionary and create the files

with da.SearchCursor(inTable, TableFields) as cursor:

for item in cursor:

attachment = item[1]

#item[0] is the unique ID (OBJECTID), take first item in Dictionary

fileName1 = featureDict[item[0]][0]

#print fileName1

fileName2 = str(item[2]) #uses the "photo 1" text

#print fileName2

#add the ObjectID to the final version, then concat project number and attachment name

fileNameFinal = str(item[0]) + "_" + str(fileName1) + "_" + fileName2

#print fileNameFinal

if fileName1 == ProjectNum:

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

del item

del attachment

del fileName1

del fileName2

del fileNameFinal

del cursor