AnsweredAssumed Answered

How to ensure a script recognizes field values that have already been treated in a previous row?

Question asked by frederic_martin on Sep 16, 2016
Latest reply on Sep 18, 2016 by rfairhur24

How do I ensure the script recognizes field values that have already been “used” in a previous row (not necessarily the one just above it)? Below is my source table – the result of a spatial join between two feature classes (equivalent to a land parcel).  I have multiple “allocation claim numbers” (AllocClmNum, 1st field) with their respective “recipient credit values” (RecClmNum, 2nd field) associated to one “recipient claim number (RecClmNum, 3rd field) and its credit value (RecCredValReal, 4th field).  The script I have subtracts funds from the allocation credit value ( AllocCreVal) stored in a dictionary (thank you Richard Fairehurst) from each RecCreClmNum so that the RecCredValReal value becomes zero, and does so until the AllocCreVal reaches a value at or above 15000.  The result is stored in a new table (second one below).  In this way the script uses result of the previous the AllocCreVal subtraction and applies it to the next row.  The output is shown in the second table : the RecClmNum values to which the script was applied are in the field RecipientClaims.  This part works fine (see second table). 

source table spatial join

The problem is that the script runs regardless of whether the RecClmNum has already been allocated or not

. What I can’t solve is how to NOT include those RecClmNum rows that have already had an allocation applied to them (those highlighted in the green squares).  There are about 10000 of these records to go through. 

The values in green below should not have been calculated since they were already done using the previous AllocClmNum values.

result table


# set your variables for the Allocation Claims feature class
updateFC_AllocClms = "SortedAllocationClaims"
fields_AllocClms = ["AllocClmNum","CorrAllocCreVal","RecipientClaims"]

# set your variables for the feature class with the joined Allocation Claims and Recipient Claims (spatial)
sourceFC_AllocClmsSpj = "AllocationClaimsSPJ"
fields_AllocClmsSpj = ["OBJECTID","AllocClmNum","RecClmNum","RecCreVal","AllocStatus"]

# set the dictionary variables
Dict_AllocClmsSpj = {}
# alternative dictionary with "false" values
# source:  Richard Fairhurst
with arcpy.da.SearchCursor(sourceFC_AllocClmsSpj,fields_AllocClmsSpj) as searchRows:
     for searchRow in searchRows:
          keyValue = searchRow[1]
          if not keyValue in Dict_AllocClmsSpj:
               lstRow = list(searchRow[1:])
               # assign a new keyValue entry to the dictionary storing a list
               Dict_AllocClmsSpj[keyValue] = [lstRow]
               lstRow = list(searchRow[1:])
               # dictionary keyValue exists so append to the list

# run the script

# update the Sorted Allocation Claims FC with the new values for Allocation Credits ("CorrAllocCreVal")
with arcpy.da.UpdateCursor(updateFC_AllocClms, fields_AllocClms) as upRows:
     for upRow in upRows:
          x = upRow[2]
          comma = ","
          KeyAllocationClaim = upRow[0] # key value is AllocClmNum
          # KeyRecipientClaims = upRow[2] # value represents the recipient claim numbers
          if KeyAllocationClaim in Dict_AllocClmsSpj: # if the KeyAllocationClaim is in the dictionary
               for record in Dict_AllocClmsSpj[KeyAllocationClaim]:
                    # something like: if record[1] not in upRow[2]
                    # return the list of values in the field "RecipientCLaims" (upRow[2]).
                         # if record[1] (RecClmNum from
                    if record[4] == False:
                         if (upRow[1] - record[2]) >= 15000: # and as long as credit value is >= to 15000
                              upRow[1] = upRow[1] - record[2] # subtract the Allocation Credit Value from the Recipient Credit Value in the  Dict_AllocClmsSpj
                              # append all the recipient claim numbers "record[1]" to the field "RecipientClaims" in the "SortedAllocationClaims" table separated by a comma
                              upRow[2] = upRow[2] + record[1] + comma
                              record[4] = True