0

I'm looking to see if there's some code out there I can manipulate, or if I can utilize a model to essentially compare field values between 3 or more tables. The two issues here are

  1. I have to relate through an intermediate table to go from the Main table to the various tables I want to compare (more below), and
  2. the string values in the main table aren't exactly the same as the values in the compare tables.

Example: Main_Table has a field Restricted with possible values:

  • Unrestricted
  • Restrict 1
  • Restrict 2

Primary Key field Main ID relates to a Link_Table with fields: Main_ID, Table_A_ID and Table_B_ID

Table_A_ID and Table_B_ID may or may not be null. If they have values, then they relate to their corresponding compare tables (Table A and Table B).

Table A and B both have Restricted fields. But the values here (strings) are either "X" (meaning restricted) or NULL (meaning unrestricted).

Essentially, I want to compare the Restricted value in Main_Table to the Restricted values of Table A and B. If there are any differences, I want to either select them in Main_Table or output the results in a new table (whatever is easier.)

So, if Main_Table is 'Restrict 1' (or 2), and either Table A or B is NULL, then they fail and that record is selected in Main_Table (or outputted to a new table).

There's quite a bit involved here and I'm not sure if there's already some code out there I can start with.

Update 1 I think screenshots would be confusing, as I'm using generic field and table names here in place of my real data. But I can give you a snapshot of what the tables might look like. I should note...record 101 has a related value in Table_B but not Table_A. I still want to compare Main_Table to Table_B in that case, even though there is no related record in Table_A.

Table Examples

  • 1
    Can you please edit your Question to include a screenshot of some of your records from each table (or an example of them if you prefer not to share your data), and a snippet of any arcpy code you've tried – Midavalo Jul 7 '16 at 18:06
  • You say X means "Restricted" in Table_A and Table_B, but you then say that Main_ID 100 is Unrestricted but Table_A and Table_B both return X meaning it's Restricted. How is this a Pass? – Midavalo Jul 8 '16 at 22:55
  • yikes. In my haste, I introduced a fatal typo. You are absolutely correct. If the fields don't match, as is the case with record 100, then it fails. But that is the record I want to select and/or record in a results table. – MattS Jul 10 '16 at 13:42
1

This should point you in the right direction. It does make a few assumptions that may need to be worked around if incorrect:

  • Main_Table is a Feature Layer in ArcMap, the rest are Tables in ArcMap.
  • Your only values are X or Null in the tables, and Unrestricted or something else (everything else considered to be Restricted in some way).
  • If linked records are not found for both Table A and Table B then it will be a Pass
  • Needs to be run from ArcMap via a Python Script tool
import arcpy

mainTable = r'MainTable'       # Enter the name of your Main Table here
linkTable = r'LinkTable'       # Enter the name of your Link Table here
tableA = r'TableA'             # Enter the name of Table A here
tableB = r'TableB'             # Enter the name of Table B here

mainIDField = 'Main_ID'        # Enter the name of your Main ID field
tableAIDField = 'Table_A_ID'   # Enter the name of your Table A ID field
tableBIDField = 'Table_B_ID'   # Enter the name of your Table B ID field

mxd = arcpy.mapping.MapDocument("CURRENT")
#mainLayer = arcpy.mapping.ListLayers(mxd, mainTable)[0]      # Use this line if Main_Table is a Feature Layer
mainLayer = arcpy.mapping.ListTableViews(mxd, mainTable)[0]   # Use this line if Main_Table is a Table

arcpy.AddMessage(mainLayer.name)
arcpy.AddMessage("Selection Set = {}".format(mainLayer.getSelectionSet()))

mainFields = ['OID@', mainIDField, 'Restriction']
linkFields = [mainIDField, tableAIDField, tableBIDField]
aFields = [tableAIDField, 'Restriction']
bFields = [tableBIDField, 'Restriction']

linkA = dict()
linkB = dict()
with arcpy.da.SearchCursor(linkTable, linkFields) as cursor:
    for row in cursor:
        linkA[row[0]] = row[1]
        linkB[row[0]] = row[2]

tableA_dict = dict()
with arcpy.da.SearchCursor(tableA, aFields) as cursor:
    for row in cursor:
        tableA_dict[row[0]] = row[1]

tableB_dict = dict()
with arcpy.da.SearchCursor(tableB, bFields) as cursor:
    for row in cursor:
        tableB_dict[row[0]] = row[1]

selectionList = list()

with arcpy.da.SearchCursor(mainTable, mainFields) as cursor:
    for row in cursor:
        xOID = row[0]
        xMainID = row[1]
        if row[2] == 'Unrestricted':
            xMainRestrict = 'Unrestricted'
        else:
            xMainRestrict = 'Restricted'
        xARestrict = xBRestrict = "Not Present"

        if xMainID in linkA:
            xLinkA = linkA[xMainID]
            if xLinkA in tableA_dict:
                if tableA_dict[xLinkA] == 'X':
                    xARestrict = 'Restricted'
                else:
                    xARestrict = 'Unrestricted'
        if xMainID in linkB:
            xLinkB = linkB[xMainID]
            if xLinkB in tableB_dict:
                if tableB_dict[xLinkB] == 'X':
                    xBRestrict = 'Restricted'
                else:
                    xBRestrict = 'Unrestricted'

        if (xARestrict not in [xMainRestrict, 'Not Present']) or (xBRestrict not in [xMainRestrict, 'Not Present']):
            selectionList.append(xOID)

mainLayer.setSelectionSet("NEW", selectionList)

arcpy.RefreshActiveView()
arcpy.RefreshTOC()

What it does is link all the values together and check if the restrictions in Table_A and Table_B match the restriction in the Main_Table. If either of them don't, then it adds that record to the selection set. If a value isn't present in Table_A or Table_B it is overlooked (considered to match Main_Table).

Once every record is checked, it then selects them in ArcMap by passing the list of "Failed" objectIDs through to the layer as a selection set.

  • This is terrific. I need a bit of time to look through this. I won't be able to do so until next week but I'll try to report back. I can tell you, however, about a couple of the 'assumptions' you mention. First, Main_Table is not a feature layer. It's also a table. That's important. Also, you mention that if a value isn't present in tbl A or B, it is overlooked. That's not going to work in my case either. If, for instance, A is not present. Main and B should still be compared. That's also critical. But this is at least a very helpful start. – MattS Jul 14 '16 at 12:17
  • @MattS - to clarify, if a value isn't present in Table_A then Table_A is overlooked/skipped. Table_B is still assessed. Also if Main_Table is a table not a feature layer then a couple of parts may not work so I'll need to make a small adjustment to them. I'll do that later today or tomorrow. – Midavalo Jul 14 '16 at 18:04
  • I can't thank you enough. I wasn't expecting this much help but this is great. My real-life situation is a bit more involved than the scenario I presented here but that's ok. I can certainly use most of this and just make a few weeks to fit my data and schema a bit better. Thank you also for updating main_table to a table, not a feature layer. I very much hope that starting next week, I can sit down and begin testing this code. I haven't had a chance yet. I'll let you know how it goes. Looking forward to your update... – MattS Jul 15 '16 at 18:57
  • @MattS I have updated to work on a Table rather than Feature Layer, and fixed a couple of typos in the code – Midavalo Jul 15 '16 at 22:12
  • @MattS let me know if you need help translating to fit your actual data – Midavalo Jul 15 '16 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.