Select to view content in your preferred language

Automatically Create Records in related Tables from Point Feature Class

11758
21
03-12-2015 11:34 AM
PaulMcBride
Occasional Contributor

Hello Everyone,

I have a point feature with a single field used as the the attribute key for relationship classes to other tables.  I am looking to automatically create a row into the related tables with the key field populated from the point creation.  Any ideas how to auto populate the tables with that key?

Below is a screenshot of the inside of database.

Thank you!

Paul McBride

0 Kudos
21 Replies
JoshuaChisholm
Frequent Contributor

Hello Paul,

I'm a little confused, do you want to copy the first row in your points file to a new row in each of the related tables?

Regards,

Josh

0 Kudos
PaulMcBride
Occasional Contributor

Thank for the reply.

Yes that is what I would like to do. 

When a user places a new point I would like them to be able to edit the related tables immediately without having to create a new record for that point.  The related tables would automatically receive the point ID field that relates them together.

Thanks again.

0 Kudos
JoshuaChisholm
Frequent Contributor

Ok, so I wrote a small python script that should work. The script should be able to handle a few points really quickly, but will be slower than needed if your very large numbers of input points. Since your said it will only be one at a time, this should be fine.

I included two ways of selected your related tables (manually and automatically). Remove the method you choose not to use. You will also need to make sure your key field already exists in all your related tables.

import arcview
import arcpy

workDir="C:\\path\\to\\data\\TESTGeodatabase.gdb"

pointsFC="MCCOG_Complaint.DBO.complaint"

keyField="complaintKey"

#Manually select related tables:
relatedTables=["MCCOG_Complaint.DBO.complaintProblem","MCCOG_Complaint.DBO.complaintWorkOrder","MCCOG_Complaint.DBO.complaintContractor"]

#Automatically select related tables:
arcpy.env.workspace=workDir
relatedTables=arcpy.ListTables("MCCOG_Complaint.DBO.complaint*") #all table that begin with 'MCCOG_Complaint.DBO.complaint'

#Copy rows to related tables:
with arcpy.da.SearchCursor(workDir+"\\"+pointsFC,[keyField]) as inRows:
    for inRow in inRows:
        for relatedTable in relatedTables:
            with arcpy.da.InsertCursor(workDir+"\\"+relatedTable,[keyField]) as outRows:
                outRows.insertRow(inRow)

print("Complete!")

Let me know how it goes!

BlakeTerhune
MVP Regular Contributor

Seems like you would want to create the search cursor on just the selected features instead of everything in the point feature class. I think the way you have it now would copy all rows in the point feature class to the related tables each time a new point was added (creating duplicate records in the related tables).

0 Kudos
PaulMcBride
Occasional Contributor

Josh,

Thank you for the script.  Did you run this within arcmap or did you create an addin and then put code into python script?

0 Kudos
AndrewRitchie
Occasional Contributor

Still invaluable 6 years later. Thanks Joshua

0 Kudos
JoshuaChisholm
Frequent Contributor

Hello Paul,

I actually ran my python script outside of ArcMap using IDLE, but it can also be run within ArcMap. I did not set it up as a python add-in. Also, as Blake pointed out, the script will copy over ALL keys in the complaint file to the related tables each time it runs (thanks for that catch Blake!).

I have a few ideas on how to proceed;

  1. We can change the script so that it only copies over keys that don't yet exist in the related tables. This would mean the script could be run in ArcMap or outside ArcMap (with IDLE), and just requires pointers to the data.
  2. We can use Blake's idea and only run the script on selected records. This would mean we have to be in ArcMap and have to be able to programmatically pick out the complaint layer with the user selected points. If we set this up as an add-in, there is a way to pick out the layer the user has selected in the TOC.
  3. We can combine the 2 above approached so that if users run the tool twice on the same Key, it won't create duplicate records in the related tables.

Let me know what you think!

0 Kudos
PaulMcBride
Occasional Contributor

Hey Josh,

Option 3 sounds great.  What I would like to do is get this script into a button that after the point is placed and assigned the key number then they could hit the button and apply to the related tables.

Thanks again!!!!

Allison_Hockey
Regular Contributor

Paul, 

did you ever find a way to do this? I am trying to create the exact same thing with a button my end users could click to create the related records. Thanks for any pointers

0 Kudos