topic Re: Frequency missing a case field in Geoprocessing Questions

Frequency missing a case field

curtvprice — Sat, 02 Apr 2011 15:13:35 GMT

A task I find myself having to do requires functionality that existed in ArcInfo Workstation's FREQUENCY command. That tool worked pretty much like the ArcGIS Frequency tool except there was an option for a {case_field} that would be added to the input table to make it easy to join the frequency table to the input table later using a single case field.

Does anyone have a cookbook example of how to generate such a case field in the input table for each unique combination found?

Thanks.

(Posted early 2011; updated 5/2015)

Re: Frequency missing a "case" field

curtvprice — Mon, 02 May 2011 18:35:09 GMT

Anyone... anyone...?

Re: Frequency missing a case field

SepheFox — Sat, 09 May 2015 05:46:06 GMT

Wow! You responded to your post four years before you even wrote it!

Re: Frequency missing a case field

SepheFox — Sat, 09 May 2015 05:53:59 GMT

Can't you just use summary statistics instead?

Re: Frequency missing a case field

RichardFairhurst — Sat, 09 May 2015 10:02:02 GMT

No, he can't. Summary Statistics adds nothing to the source and output data that permits a standard many-to-one join to work when a multi-field case field key is used. He wants a single field to represent the unique combination of values from 2 or more fields in both the source and output data so that a standard join will work to create an in memory many-to-one tableview.

To do what he wants there is no help from Esri other than the failed Make Query Table tool (its performance is unacceptably bad and it does not support outer joins or work between different geodatabases or data directories like a standard join does). So each user is left on their own to write their own program to use a dictionary and cursor to track the multi-field unique values and write back an ID to both the source and output data (with no interface that makes their code reusable for other data source/field combinations), or create their own field that contains the concatenation of all of the values that make up the multi-field key to be able to do such a join (the option I normally use if I can modify the source data).

I have written programs that can match multi-field keys using dictionaries and cursors and create a join field that does what he wants, but I have never developed the code to support a tool interface that can be added to a geoprocessing workflow that would allow the user to configure any input source, output table names and field configurations they want without having to do any code modifications. However, I have been thinking more and more that perhaps I should try. However, I do not think I will make the tool do the frequency or summary, just add the single join field to two data sources that share a multi-field match after such outputs are created. That way it will work no matter how the two data sources were created or even if the sources share a many-to-many relationship.

Re: Frequency missing a case field

curtvprice — Sat, 09 May 2015 20:13:34 GMT

Like Richard Fairhurst I have had to solve this problem and indeed created some tools that solve this problem as multiple-field joins are not supported in ArcGIS. Hadn't thought of doing this with a python dictionary, I was thinking more of doing this with a lists and sets. I'm sure there are dozens of ways of doing this.

Sephe Fox the dates are messed up because I've been going back and updating/formatting/renaming titles/placing my old content to make them easier to find in Geonet.

Re: Frequency missing a case field

RichardFairhurst — Sat, 11 Dec 2021 22:03:09 GMT

Edit: May 30, 2015 - 5:10 AM PST - Added an Ascending or Descending Sort Order column to order the values of each field in the Case field list so that the sequence order of the Case ID numbers can be fully controlled.

Edit: May 22, 2015 - 11:44 PM PST - Improved speed of the code for the Intersect option.

Edit: May 22, 2015 - 4:16 PM PST - Changed code to allow for 3 different options for assigning unique Case ID numbers to the tables. It now allow you to assign them to all Primary Table Case Keys (Default), all Case Keys in both Tables (Union), or only to Case Key found in both tables (Intersection).

Edit: May 19, 2015 - 1:50 PM PST - Updated code and attachment to correctly handle 3 or more case fields.

Edit: May 9, 2015 9:15 PM PST - Fixed error in toolbox code for unmatched case values in Secondary Table. Attached updated toolbox.

I have created a Python toolbox tool that is doing almost everything I want. The zipped python toolbox attached was designed in ArcGIS 10.3, but hopefully it will run in lower versions. The toolbox should be placed in the "%APPDATA%\ESRI\Desktop10.[3]\ArcToolbox\My Toolboxes" folder in Windows 7 (modify the items in brackets to fit your Desktop version of 10.3 or higher).

I use python lists and itertools to get my sorted unique list of multiple field key values and to generate the sequential numbers associated with each key, but I convert the list into a dictionary prior to running the updateCursor so that I gain the speed or dictionary random access matching when I am writing the Case ID number values back to the data sources. Dictionary key matching is much faster than trying to locate matching items in a list. Here is the interface:

The validation works to make sure that the two input data sources are not the same and that the fields used in the case field list are actually in both sources. The user can choose as many fields as they want to make up their unique multiple field case value keys. The field names do not have to be the same in both data sources. The position of the fields in the list will control the sort priority of the Case Fields (highest priority = top field) and the Sort Order column controls whether the values in each field are sorted in Ascending or Descending order. The sort order of the Case field values controls the Case ID number sequencing. The arrangement of the fields can be different from the field arrangement actually used in the sources.

However, I am having a problem that I cannot seem to solve. I cannot seem to get the schema additionalFields parameter to update the field list for my outputs so that the Case ID field is shown in ModelBuilder if the field doesn't exist and is being added to the data sources by the tool. Does anyone know how to make that work or spot what I am doing wrong?

Anyway, here is my code:

import arcpy

class Toolbox(object):
    def __init__(self):
        """Define the toolbox (the name of the toolbox is the name of the
        .pyt file)."""
        self.label = "Field Match Tools"
        self.alias = ""

        # List of tool classes associated with this toolbox
        self.tools = [MultiFieldKeyToSingleFieldKey]

class MultiFieldKeyToSingleFieldKey(object):
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = "Multiple Field Key To Single Field Key"
        self.description = ""
        self.canRunInBackground = False

    def getParameterInfo(self):
        """Define parameter definitions"""

        # First parameter
        param0 = arcpy.Parameter(
            displayName="Input Primary Table",
            name="in_prim_table",
            datatype="DETable",
            parameterType="Required",
            direction="Input")

        # Second parameter
        param1 = arcpy.Parameter(
            displayName="Input Secondary Table",
            name="in_sec_table",
            datatype="DETable",
            parameterType="Required",
            direction="Input")

        # Third parameter
        param2 = arcpy.Parameter(
            displayName="Case Fields",
            name="case_fields",
            datatype="GPValueTable",
            parameterType="Required",
            direction="Input")

        param2.columns = [['GPString', 'Primary Case Field'], ['GPString', 'Secondary Case Field'], ['GPString', 'Sort Order']]
        param2.filters[0].type="ValueList"
        param2.filters[0].list = ["X"]
        param2.filters[1].type="ValueList"
        param2.filters[1].list=["x"]
        param2.filters[2].type="ValueList"
        param2.filters[2].list=["Ascending", "Descending"]
        param2.parameterDependencies = [param0.name]

        # Fourth parameter
        param3 = arcpy.Parameter(
            displayName="Case ID Field Name",
            name="in_Case_ID_field",
            datatype="GPString",
            parameterType="Required",
            direction="Input")

        param3.value = "CASE_ID"

        # Fifth parameter
        param4 = arcpy.Parameter(
            displayName="Create unique Case ID numbers for:",
            name="case_key_combo_type",
            datatype="GPString",
            parameterType="Required",
            direction="Input")

        param4.filter.type = "valueList"
        param4.filter.list = ["all Primary keys and only matching Secondary keys","all Primary keys and all Secondary keys (Union)","only keys found in both the Primary and Secondary tables (Intersection)"]
        param4.value = "all Primary keys and only matching Secondary keys"

        newField = arcpy.Field()
        newField.name = param3.value
        newField.type = "LONG"
        newField.precision = 10
        newField.aliasName = param3.value
        newField.isNullable = "NULLABLE"

        # Fifth parameter
        param5 = arcpy.Parameter(
            displayName="Output Primary Table",
            name="out_prim_table",
            datatype="DETable",
            parameterType="Derived",
            direction="Output")

        param5.parameterDependencies = [param0.name]
        param5.schema.clone = True

        param5.schema.additionalFields = [newField]

        # Sixth parameter
        param6 = arcpy.Parameter(
            displayName="Output Secondary Table",
            name="out_sec_table",
            datatype="DETable",
            parameterType="Derived",
            direction="Output")

        param6.parameterDependencies = [param1.name]
        param6.schema.clone = True

        param6.schema.additionalFields = [newField]

        params = [param0, param1, param2, param3, param4, param5, param6]

        return params

    def isLicensed(self):
        """Set whether tool is licensed to execute."""
        return True

    def updateParameters(self, parameters):
        """Modify the values and properties of parameters before internal
        validation is performed.  This method is called whenever a parameter
        has been changed."""

        if parameters[0].value:
            # Return primary table  
            tbl = parameters[0].value  
            desc = arcpy.Describe(tbl)  
            fields = desc.fields  
            l=[]  
            for f in fields:  
                if f.type in ["String", "Text", "Short", "Long", "Float", "Single", "Double", "Integer","OID", "GUID"]:  
                    l.append(f.name)  
            parameters[2].filters[0].list = l  
        if parameters[1].value:  
            # Return secondary table  
            tbl = parameters[1].value  
            desc = arcpy.Describe(tbl)  
            fields = desc.fields  
            l=[]  
            for f in fields:  
                if f.type in ["String", "Text", "Short", "Long", "Float", "Single", "Double", "Integer","OID", "GUID"]:  
                    l.append(f.name)  
            parameters[2].filters[1].list = l  
        if parameters[2].value != None:
            mylist = parameters[2].value
            for i, e in list(enumerate(mylist)):
                if mylist[2] != "Descending":
                    mylist[2] = "Ascending"
            parameters[2].value = mylist
        return

    def updateMessages(self, parameters):
        """Modify the messages created by internal validation for each tool
        parameter.  This method is called after internal validation."""
        if parameters[3].value and parameters[0].value and parameters[1].value:
            desc = arcpy.Describe(parameters[0].value)  
            fields = desc.fields
            in_primary = False
            is_primary_error = False
            is_primary_uneditable = False
            for f in fields:
                if parameters[3].value.upper() == f.name.upper():
                    in_primary = True
                    if f.type != "Integer":
                        is_primary_error = True
                    elif not f.editable:
                        is_primary_uneditable = False
            desc2 = arcpy.Describe(parameters[1].value)  
            fields2 = desc2.fields
            in_secondary = False
            is_secondary_error = False
            is_secondary_uneditable = False
            for f2 in fields2:
                if parameters[3].value.upper() == f2.name.upper():
                    in_secondary = True
                    if f2.type != "Integer":
                        is_secondary_error = True
                    elif not f2.editable:
                        is_secondary_uneditable = False
            newField = arcpy.Field()
            newField.name = parameters[3].value
            newField.type = "LONG"
            newField.precision = 10
            newField.aliasName = parameters[3].value
            newField.isNullable = "NULLABLE"
            fields1 = []
            fields2 = []
            order = []
            for item in parameters[2].value:
                fields1.append(item[0].upper())
                fields2.append(item[1].upper())
                order.append(item[2])
            if str(parameters[0].value).upper() == str(parameters[1].value).upper():
                parameters[1].setErrorMessage("The Input Secondary Table {0} cannot be the same as the Input Primary Table {1} ".format(parameters[1].value, parameters[0].value))
            else:
                parameters[1].clearMessage()
            if in_primary and in_secondary:
                if is_primary_error and is_secondary_error:
                    parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in both the Input Primary and Secondary Tables".format(parameters[3].value.upper()))
                elif is_primary_error:
                    parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in the Input Primary Table".format(parameters[3].value.upper()))
                elif is_secondary_error:
                    parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in the Input Secondary Table".format(parameters[3].value.upper()))
                elif parameters[3].value.upper() in fields1 and parameters[3].value.upper() in fields2:
                    parameters[3].setErrorMessage("{0} is used as a Case Field for both the Input Primary and Secondary Tables".format(parameters[3].value.upper()))
                elif parameters[3].value.upper() in fields1:
                    parameters[3].setErrorMessage("{0} is used as a Case Field for the Input Primary Table".format(parameters[3].value.upper()))
                elif parameters[3].value.upper() in fields2:
                    parameters[3].setErrorMessage("{0} is used as a Case Field for the Input Secondary Table".format(parameters[3].value.upper()))
                elif is_primary_uneditable and is_secondary_uneditable:
                    parameters[3].setErrorMessage("{0} exists and is not editable in both the Input Primary and Secondary Tables".format(parameters[3].value.upper()))
                elif is_primary_uneditable:
                    parameters[3].setErrorMessage("{0} exists and is not editable in the Input Primary Table".format(parameters[3].value.upper()))
                elif is_secondary_uneditable:
                    parameters[3].setErrorMessage("{0} exists and is not editable in the Input Secondary Table".format(parameters[3].value.upper()))
                else:
                    parameters[3].setWarningMessage("{0} will be overwritten in both the Input Primary and Secondary Tables".format(parameters[3].value.upper()))
            elif in_primary:
                parameters[6].schema.additionalFields = [newField]
                if is_primary_error:
                    parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in the Input Primary Table".format(parameters[3].value.upper()))
                elif is_primary_uneditable:
                    parameters[3].setErrorMessage("{0} exists and is not editable in the Input Primary Table".format(parameters[3].value.upper()))
                else:
                    parameters[3].setWarningMessage("{0} will be overwritten in the Input Primary Table".format(parameters[3].value.upper()))
            elif in_secondary:
                parameters[5].schema.additionalFields = [newField]
                if is_secondary_error:
                    parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in the Input Secondary Table".format(parameters[3].value.upper()))
                elif is_secondary_uneditable:
                    parameters[3].setErrorMessage("{0} exists and is not editable in the Input Secondary Table".format(parameters[3].value.upper()))
                else:
                    parameters[3].setWarningMessage("{0} will be overwritten in the Input Secondary Table".format(parameters[3].value.upper()))
            else:
                parameters[5].schema.additionalFields = [newField]
                parameters[6].schema.additionalFields = [newField]
                parameters[3].clearMessage()
        return

    def execute(self, parameters, messages):
        """The source code of the tool."""
        try:
            desc = arcpy.Describe(parameters[0].value)  
            fields = desc.fields
            in_primary = False
            for f in fields:
                if parameters[3].value.upper() == f.name.upper():
                    in_primary = True
            if not in_primary:
                arcpy.AddField_management(parameters[0].value, parameters[3].value.upper(), "Long", 10)
                arcpy.AddMessage("Added a Case ID field to the Input Primary Table")  
            desc2 = arcpy.Describe(parameters[1].value)  
            fields2 = desc2.fields
            in_secondary = False
            for f2 in fields2:
                if parameters[3].value.upper() == f2.name.upper():
                    in_secondary = True
            if not in_secondary:
                arcpy.AddField_management(parameters[1].value, parameters[3].value.upper(), "Long", 10)
                arcpy.AddMessage("Added a Case ID field to the Input Secondary Table")  
            tbl1 = parameters[0].value
            tbl2 = parameters[1].value
            fields1 = []
            fields2 = []
            order = []
            for item in parameters[2].value:
               fields1.append(item[0])
               fields2.append(item[1])
               order.append(item[2])
            arcpy.AddMessage("Primary Case Fields are {0}".format(str(fields1)))
            arcpy.AddMessage("Secondary Case Fields are {0}".format(str(fields2)))
            arcpy.AddMessage("Sort Orders are {0}".format(str(order)))
            import itertools
            k = list((r[0:]) for r in arcpy.da.SearchCursor(tbl1, fields1))
            arcpy.AddMessage("Case Values have been read from the Input Primary Table")
            if parameters[4].value == "all Primary keys and all Secondary keys (Union)":
                j = list((r[0:]) for r in arcpy.da.SearchCursor(tbl2, fields2))
                k = k + j
                j = None
                arcpy.AddMessage("Case Values have been appended from the Input Secondary Table")
            from operator import itemgetter  
            for i, e in reversed(list(enumerate(order))):
                if order == "Descending":
                    k.sort(key=itemgetter(i), reverse=True)
                else:
                    k.sort(key=itemgetter(i))
            k = list(k for k,_ in itertools.groupby(k))
            if parameters[4].value == "only keys found in both the Primary and Secondary tables (Intersection)":
                j = {tuple(r[0:]):1 for r in arcpy.da.SearchCursor(tbl2, fields2)}
                arcpy.AddMessage("Case Values have been read from the Input Secondary Table")
                l = []
                for item in k:
                    if tuple(item) in j:
                        l.append(item)
                j = None
                k = l
                l = None
                arcpy.AddMessage("Case Values have been matched to the Input Secondary Table")
            arcpy.AddMessage("A list of sorted and unique Case Values has been created")
            dict = {}
            fields1.append(parameters[3].value)
            fields2.append(parameters[3].value)
            for i in xrange(len(k)):
                dict[tuple(k)] = i + 1
            k = None
            arcpy.AddMessage("A dictionary of unique Case Value keys with Case ID number values has been created")
            with arcpy.da.UpdateCursor(tbl1, fields1) as cursor:
                for row in cursor:
                    if tuple(row[0:len(fields2)-1]) in dict:
                        row[len(fields1)-1] = dict[tuple(row[0:len(fields1)-1])]
                    else:
                        row[len(fields2)-1] = -1
                    cursor.updateRow(row)
            del cursor
            arcpy.AddMessage("{0} values have been updated for Input Primary Table".format(parameters[3].value))
            with arcpy.da.UpdateCursor(tbl2, fields2) as cursor2:
                for row2 in cursor2:
                    if tuple(row2[0:len(fields2)-1]) in dict:
                        row2[len(fields2)-1] = dict[tuple(row2[0:len(fields2)-1])]
                    else:
                        row2[len(fields2)-1] = -1
                    cursor2.updateRow(row2)
            del cursor2
            arcpy.AddMessage("{0} values have been updated for Input Secondary Table".format(parameters[3].value))
        except Exception as e:  
            messages.addErrorMessage(e.message)

        return

Re: Frequency missing a case field

curtvprice — Sun, 10 May 2015 13:29:44 GMT

Using My Toolbox is just convenient; you can run it anywhere you want right?

Thanks for the awesome .pyt template, if nothing else!

Re: Frequency missing a case field

curtvprice — Sun, 10 May 2015 14:05:58 GMT

Note, this toolbox does not run at 10.2.2. The the .filters parameter is new at 10.3. (I will be waiting for 10.3.1 before updating.)

Re: Frequency missing a case field

curtvprice — Sun, 10 May 2015 14:29:54 GMT

The toolbox should be placed in the "C:\Users\[UserName]\AppData\Roaming\ESRI\Desktop10.[3]\ArcToolbox\My Toolboxes" folder in Windows 7 (modify the items in brackets to fit your user name and Desktop version).

BTW a good way to find this path to the Windows user profile folder (which can vary depending on how your Windows profile has been configured as well as the Windows version) is to use the path %APPDATA%\ESRI and navigate from there.

Re: Frequency missing a case field

RichardFairhurst — Sun, 10 May 2015 15:23:39 GMT

Too bad that the .filters parameter is only available at 10.3. I have 10.2.2 at work, but it may be a while before I have time to experiment with that version. If you come up with any alternative code to do what that parameter is doing (adding the pick lists of fields to the GPValueTable) let me know.

I was thinking of adding applicable SHAPE@ tokens to the end of the field lists for tables with a geometry field, either by default or with a Boolean option. That way matching of two data sources could be done on geometry values like X and Y coordinates without having to first calculate the coordinates into double fields.

Any thoughts on the code involving the schema.additionalFields that is failing to get ModelBuilder to recognize the new field added by the tool? I have tried several versions of where to place it in my logic, but nothing I have tried worked. I guess I will have to try designing a much simpler tool that just adds a field to a data source to see if I can make it work at all. It seems like it may be a bug.

Re: Frequency missing a case field

RichardFairhurst — Sat, 16 May 2015 17:03:24 GMT

Edit - May 23, 2015 - 12:09 AM PST - Improved speed of the code for the intersect option. See 10.3 version.

Edit - May 22, 2015 - 6:11 PM PST - Updated options for Case ID numbers. See 10.3 version.

[Edit - May 19, 2015 - 1:30 PM PST - Revised tool execution code to correctly handle more than case two fields]

I have attached a version of my tool where I have attempted to create an interface for the tool that will work for Desktop 10.2. The 10.3 tool interface I posted previously works better overall and should be used if you have Desktop 10.3, but for those still using Desktop 10.2 hopefully this interface will work reasonably well.

The key objectives of the interface is to aid the user in creating a valid multiple field case field list from the table inputs and to let the user choose the order of the fields affecting the sort of the Case ID values independently of the field order within the source table. To do this I have used both a checkbox pick list and a string to get field inputs from the user for the tool.

In order to choose fields in the 10.2 interface you should check fields in the checkbox lists shown for each input table. The checked fields will be added to a case field text list string, and the string is used to determine the sort order of the Case ID values. The fields in the case field text list string only updates after the user clicks with the left mouse button outside of the fields checklist. If the user clicks outside of the ckeckbox list before choosing their next field for a table, the next field is added to the end of the text list string and can make the text list string order different from the checkbox field list order. Unchecking fields will remove fields from the text list string without disturbing the order of the rest of the fields in the string.

A screen shot of the interface is shown below. (Note that I was able to choose the fields from the checkbox lists in a way that changed the order of the fields in the Text List strings without having to type the fields in the string.)

There are several drawbacks to this interface that the 10.3 interface overcomes. The biggest drawback is that when the 10.2 version tool is chosen from the Results tab, the field checkbox list and field text list string are blanked out and the user has to choose the field list again before running the tool. The 10.3 version remembers the fields chosen and can be run again immediately from the Results tab. This difference is due to differences in the ways the tool inputs are validated. The 10.3 version also has better validation to make sure that every field chosen from one table has a matching field from the other table. In the 10.2 version I am relying on the user to keep track of that. The user can also type invalid information into the Field Text List strings in the 10.2 version, but they can;t choose invalid fields in the 10.3 version. However, despite these drawbacks the 10.2 interface is still usable.

Both tools still do not update the field list to include the Case ID field in ModelBuilder when that field is added by the tool. I have not yet contacted Esri to find out if that can be fixed or if this behavior is a bug.

Re: Frequency missing a case field

curtvprice — Sat, 16 May 2015 17:28:43 GMT

Richard, I really appreciate your careful work on this, especially because I have found python toolboxes fairly opaque and I have this practical example to learn from. I feel like I'm finally getting why I want to bother learning how to develop with them!

Re: Frequency missing a case field

RichardFairhurst — Tue, 19 May 2015 20:33:11 GMT

Curtis:

I have revised the 10.2 and 10.3 versions to correctly handle more than 2 case fields. The original code failed to work when 3 or more fields were selected, but the new attachments uploaded today do work for 3 or more fields.

Re: Frequency missing a case field

RichardFairhurst — Fri, 22 May 2015 21:44:33 GMT

I have been using this tool a lot over the past few days to do matching of values between two tables and I have found it useful to add another option to my tool. As a result, I have modified the 10.2 and 10.3 versions to provide 3 different options for assigning Case ID numbers to the records in the two tables. The attachment, screenshot and code in my previous post on the 10.2 and 10.3 tools has been updated.

The default option assigns positive Case ID numbers to all unique case values in the Primary Table. The Secondary Table is assigned a positive Case ID numbers only if it matches a case value found in the Primary Table. Unmatched values in the Secondary Table are assigned a Case ID value of -1.

The second option assigns positive Case ID numbers to all unique case values found in either of the two tables. This option represents a Union of case values in the two tables. No Case Field values in either table will have -1 assigned as a Case ID value.

The new third option will only assign positive Case ID numbers when the case value is found in both tables. This option represents the Intersection of case values found in the two tables. In this option any unmatched case value in either table that is not found in the other table will be assigned a Case ID value of -1.

The inclusion of Case ID values that are assigned -1 in these different ways makes it much faster to select the set of values not found in one table or the other than using a join and selecting Null records in the joined table.

Re: Frequency missing a case field

RichardFairhurst — Sat, 30 May 2015 12:29:07 GMT

I have made another revision to the 10.3 version of the tool. I have added the option to sort the values in any field in Ascending or Descending order to control the Case ID numbering sequence. The GPValueTable parameter control at 10.3 allows me to just add a new Sort Order column with a filtered drop down list, making it easy for the user to set up and see the relationship between the field names and the sort order option.

I have not added this option to the 10.2 version of the tool, because I believe the interface limitations at 10.2 would place too much of a burden on the user to maintain a relationship between the two Case field lists and a third Sort Order option list.

Re: Frequency missing a case field

SalmanAhmed — Fri, 19 Jun 2015 08:01:46 GMT

Hello, I find myself in a situation needing to use this nice tool again. But I want to create case IDs based on two fields that have been joined to the base table. I know that for such fields this tool doesn't work. So I created two new fields and just transferred the values from those two joined fields into the new ones using field calculator. But still these newly created fields also don't show up in the tool dialogue box. Is there anyway to make this work with this tool?

Re: Frequency missing a case field

RichardFairhurst — Fri, 19 Jun 2015 21:02:45 GMT

If you created new fields and calculated them over with the field calculator then I see no reason why they would not show up. Each time the tool runs it should recognize any newly added fields and their values in the single table. It works for me that way. So I would need screen shots to understand what you are seeing if it the new fields do not show up, since I find it hard to believe that newly added fields with values will not be recognized by the tool.

Joined tables are very hard to set up and work with in ArcObjects. The qualified field names are very tricky to use even when you can get them to work in ArcObjects. I don't know what complications in creates when using Python.

I set the tool up to use a Table directly and not a Layer/Tableview. The inputs would have to be converted to a Layer/Tableview to detect any kind of a Join. But all the rest of the code may still fail to deal with the fully qualified table names correctly if I made that change. You would need to provide a more detailed explanation of what fields you are joining for me to know whether or not I want to even try to do what you want.

Re: Frequency missing a case field

RichardFairhurst — Fri, 19 Jun 2015 21:57:28 GMT

I did a quick experiment by changing the inputs to allow the use of GPTableView instead of DETable. The Join fields are recognized in the set up.

However, the AddField_management tool required to add the case field fails, because a Joined table is not allowed as an input to that tool. So I would have to write a subroutine to extract all of the join properties, break the join, add the field and then recreate the join. I suspect that if I overcame that problem then there would be problems with the update cursor, since I would have to keep track of the unqualified and fully qualified field name at different stages in the code. I also have concerns with the cursor performance if a join is in place. If you are joining two or more tables at once then I am not interested in helping you, since I avoid that configuration at all costs. So, you will have to give me a much more detailed description of the real life scenario you need to solve, since I am not willing to work out every possible complication that could arise with every possible join set up.

Re: Frequency missing a case field

SalmanAhmed — Sat, 20 Jun 2015 09:54:04 GMT

I think I know what the problem was. The newly created fields were created by opening the table from the table of contents. And I was running the tool on the original table in the database. So I think these new fields were not there in the table in the database. So I need to export this table first to the database to be able to see the new fields in the table. I stupidly assumed that any changes I make in the table by opening it from the table of contents will be transferred to the respective table of that shapefile in the database.