How to join more than one fields in feature class?

Question

I have arcgis pro 2.7.1 and sde.

How do I join more than one fields in feature class and table? I see it is allowed only one field from join. Please advise.

Thank you

Alex

RichardFairhurst · Answer

Press the Windows button on your computer (lower left corner of the Windows task bar), press the ArcGIS application group, then press the Python Command Prompt application to open a command prompt window.  Type Idle and press Enter to start Idle (Python GUI application).  From the File menu create a New File or use the shortcut Ctrl-N.  Paste the code into the file.  From the File menu Save (Ctrl-S shortcut) the file in the directory you want.  Give the file the name you want and make sure to put the .py extension at the end.  You can run the file from the Run menu Run Module (F5 shortcut).  If there are errors they will be shown in the main Idle window.

You can use this .py file to schedule a weekly task using the Windows Task Manager that will run automatically at the date and time you specify.  For the program script portion of the action use (keep in double quotes and replace yourusername with your actual user name):

"C:\Users\yourusername\AppData\Local\Programs\ArcGIS Pro\bin\Python\envs\arcgispro-py3\pythonw.exe"

For the Add arguments put a double quoted string containing the path and file name of your python script.

AlexP_ · Answer

@RichardFairhurst  Thank you. It looks like I add this script in ArcGIS Pro python window?

RichardFairhurst · Answer

Since the purpose of your join is to transfer data from the table to the feature class, my Blog applies.  If you change the lines of code below based on the comment that precedes them to match your path, table/feature class names, and field names, this code should work to transfer the data.  This code is not set up for versioned SDE data or SDE data that requires an Editor session to make changes, but it could be adapted to deal with that if needed.  This code assumes the concatenation of the 3 fields uniquely identifies each row in the table.  If the concatenated values of the three fields in the table are not unique, only the value from the last table record holding the concatenated value from the three fields will be passed to all of the matching features in the feature class.  The code could be modified to check for concatenated keys in the source table that are not unique and do statistical operations on the value being transferred, like First, Last, Min, Max, Sum, Mean, etc. (depending on the data type of the value being transferred).

from time import strftime

print( "Start script: " + strftime("%Y-%m-%d %H:%M:%S"))

import arcpy

## Change the path and source table name to match your table
sourceFC = r"C:\Path\SourceTable"

## Change the names of the three join fields and the fourth value field
sourceFieldsList = ["JoinField1", "JoinField2", "JoinField3", "ValueField"]

# Use list comprehension to build a dictionary from a da SearchCursor where the key values are based on 3 separate feilds
valueDict = {(r[0],r[1],r[2]):r[3:] for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

## Change the path and update feature class name to match your feature class
updateFC = r"C:\Path\UpdateFeatureClass"

## Change the names of the three join fields and the fourth value field
updateFieldsList = ["JoinField1", "JoinField2", "JoinField3", "ValueField"]

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:
    for updateRow in updateRows:
        # store the Join value by combining 3 field values of the row being updated in a keyValue variable
        keyValue = (updateRow[0],updateRow[1],updateRow[2])
        # verify that the keyValue is in the Dictionary
        if keyValue in valueDict:
            # transfer the value stored under the keyValue from the dictionary to the updated field.
            updateRow[3] = valueDict[keyValue]
            updateRows.updateRow(updateRow)

del valueDict

print( "Finished script: " + strftime("%Y-%m-%d %H:%M:%S"))