copy related table data into a single field in an attribute table

Question

I have a featureclass of cities and a separate table of city administrators (multiple administrators for each city) and related contact info.

What I need to do is consolidate all city administrator info into the attribute table of the city featureclass. I'd like to add and populate fields for contact1, contactemail1, contact2, contactemail2, etc etc

A join, obviously, dumps all but the first record.

The relate shows me all the info i need, but I need to somehow copy all that info into a single row in the original featureclass, rather than clicking on each city to see the related table.

Possible?

Answer 1

This is a very crude effort and there are some places needs to be optimized. From my understanding of your problem I wrote this script. If needed please modify as per your suitability.

import arcpy


def main():
    # path of feature class
    fc = r'D:\Python\ScratchDatabase\Geodatabase.gdb\County'

    # list of fields to add in feature class
    # NOTE: sequence must be same as table fields
    # otherwise field name may not cantain relevant data
    field_to_add = ['AdminName', 'Email']

    # path of table
    table = r'D:\Python\ScratchDatabase\Geodatabase.gdb\CityAdmins'

    # fields information to add in feature class
    table_fields = ["Admin", "ContactEmail"]

    # common field in both which contain same values
    # Here 'Name' is field's name in both table and feature class and contains
    # same type of information. For example, 'Monroe' value is present in table
    # as well as in feature class
    common_field = 'Name'

    if len(field_to_add) != len(table_fields):
        print "Fields number mismatched. Number of fields must be same."
        quit()

    # read values from table
    tablevalues = []
    table_fields.insert(0, common_field)
    with arcpy.da.SearchCursor(table, table_fields) as cursor:
        for row in cursor:
            tablevalues.append(row)

    # create dictionary with city name as key and admin name and email as value
    # also find the maximum length of new fields
    field_len = 10 # set default minimum value
    max_fields = 0
    dictionary = {}
    for key in set(val[0] for val in tablevalues):
        temp = []
        for val in tablevalues:
            if key == val[0]:
                temp.append(tuple(val[1:]))
                new_count = max([len(item) for item in val[1:]])
                field_len = new_count if field_len < new_count else field_len
        max_fields = len(temp) if max_fields < len(temp) else max_fields
        dictionary.update({key:temp})
    del tablevalues

    # add required number of fields in feature class
    # data type of field is 'TEXT' and length is maximum length of value
    new_flds = []
    flds = [fld.name.lower() for fld in arcpy.ListFields(fc)]
    for i in xrange(max_fields):
        for fld in field_to_add:
            field_name = fld + "_" + str(i+1) # example: Admin_1
            if field_name.lower() not in flds:
                arcpy.AddField_management (fc, field_name, 'TEXT', field_length=field_len)
            new_flds.append(field_name)

    # add data into feature class
    new_flds.insert(0, common_field)
    for key in dictionary.keys():
        with arcpy.da.UpdateCursor(fc, new_flds) as cursor:
            for row in cursor:
                if not dictionary.get(row[0]):
                    continue
                values = [v for val in dictionary.get(row[0]) for v in val]
                # to add null values for extra fields
                # for example, total fields are four but relevant data is 
                # only one. So, this will add None for other two fields
                while True:
                    if len(row[1:]) == len(values):
                        break
                    values.append(None)
                row[1:] = values
                cursor.updateRow(row)

    print "Done. Values are added in feautre class."


if __name__ == '__main__':
    main()