Excel wont work as we intend to use the form in ArcGIS Online?
Can you use Python and create a gp tool? that is, do you have access to ArcGIS Server? I am not at a location that I can write /test Python script right now, but I had a similar need to looping thru records, and resetting a value every so often. In my case, I used a cursor, and as the value of a field changed, my value incremented. Once it hit 10, it would reset to one and start again.
so using a cursor and a variable to keep a running sum of your values...a quick test after each row in the cursor, reset as necessary. Of course, this assumes one time need....not real time as records are added, since unless you add a field, and store the sum in the field (which could be totally legit, if it helps).
a snippet of my code...althoug not what you are needing, might help demonstrate (posting...then will edit to format) edited on iPad...may not be quite right on indentation (sorry)
displayTrancsCnt = 1 displayID = 1 # The next lines are within a cursor, use counters and sum with tests displayTransCnt += 1 # increments to count 10 trans for each group if displayTransCnt == 11: # once count hits 11... displayTransCnt = 1 # ..reset the count displayID += 1 # ..increment the ID for next set of 10 displayGrpCnt += 1 # ..increment the sets of 10, before repeating if displayGrpCnt == 11: # once Grp hits 10 sets.., displayGrpCnt = 1 # ..resets to grp count to 1 again displayID = 1 # ..resets the ID to 1 again
Thank you very much for your response, can you kindly relate the script above to feature layers instead as i can't really relate them as needed. Thanks
My python script calculates only the value of the immediate previous record instead of calculating the values of all preceding records before that particular record. The example (the image) below is the undesired output;
While the image below here is what i want to achieve;
This is my python script i used;
import arcpy from arcpy import da CensusBldForm = "CensusBldForm" Calculated_EA1=0 with arcpy.da.UpdateCursor(CensusBldForm, ["OBJECTID", "EstimatedPopulationinEA", "Calculated_EA"]) as cursor: for row in cursor: Calculated_EA2 = row[1] row[2] = (Calculated_EA1 + Calculated_EA2) Calculated_EA1 = Calculated_EA2 cursor.updateRow(row)
Kindly assist me with the above please.
you want the cumulative sum, your line 09 is only swapping the two values, I think if you leave it out, ...EA1 should cumulate correctly...but check a few first
I think this is a little simpler.
I put the field list in a variable to keep the code block skinny (easier to read)
import arcpy
CensusBldForm = "CensusBldForm"
vsum = 0
flds = ["OBJECTID", "EstimatedPopulationinEA", "Calculated_EA"]
with arcpy.da.UpdateCursor(CensusBldForm, flds) as cursor:
for row in cursor:
vsum = vsum + row[1]
# a fancy way of the same thing follows
# vsum += row[1]
row[2] = vsum
cursor.updateRow(row)
yup... that is the cumulative sum
I also want to add a constraint whereby if the sum of the population values in the rows becomes 500, the next row's value would start from zero or the difference that makes the 500 from the previous row.
OLANIYAN OLAKUNLE - I think this will do what you want with the threshold reset, if I am understanding your description correctly:
for row in cursor: vsum = vsum + row[1] if vsum >= 500: vsum = vsum - 500 row[2] = vsum cursor.updateRow(row)
["OBJECTID", "EstimatedPopulationinEA", "Calculated_EA"]
alculated_EA"]
ahh yes, the final detail
I got the error below when i incorporated the lines of code you suggested;
vsum = vsum + row[1]
TypeError: unsupported operand type(s) for +: 'float' and 'NoneType'
See my code;
CensusBldForm = "SCensusBldForm"
vsum = 0
flds = ["OBJECTID", "EstimatedPopulationinEA", "Calculated_EA"]
with arcpy.da.UpdateCursor(CensusBldForm, flds) as cursor:
for row in cursor:
vsum = vsum + row[1]
if vsum >= 500:
vsum = vsum - 500
row[2] = vsum
cursor.updateRow(row)
What do you think i'm doing wrong?