Perform a SQL Count in Calculate Field

[Anonymous User]

Hello, 

This seems like it should be very easy, but I cannot get this to work. 

I have to use SQL because I have layers with sync or keep track of created and updated features enabled.

In a nutshell, I created a column titled 'entertotalnocomplaints'.  Note, this is a field map app the deputies are using. As of right now the deputies must count the number of complaints received on one address and then enter the total into this column. I feel I should be able to count the number of complaint fields that have text and return that total count to the field 'entertotalnocomplaints'.  So count those with text and return a numeric number to the 'entertotalnocomplaints' field. 

Upon some research of this forum this is what I have come up with so far, obviously it doesn't work. I would really appreciate some help. Thank you.

SQL statement that I put in the calculate field

entertotalnocomplaints =

def FieldCount(complaint, complaint1, complaint2, complaint3, complaint4, complaint5, complaint6, complaint7, complaint8, complaint9, complaint10):
fields = [complaint, complaint1, complaint2, complaint3, complaint4, complaint5, complaint6, complaint7,  complaint8, complaint9, complaint10]
return count

Thank you!

[DougGreen]

Hi @Anonymous User, I don't know why I didn't pick up on this before but of course you're wanting this to happen regularly. This solution that was provided here works whenever you go in and calculate the field. So Calculating a field in this way is a "one-and-done" type of thing. It's not setting up an automatic default value of sorts on the field. So as new records are added or any information changes on the record, you'd have to calculate the field to show the changes. I don't think you're going to want to do that regularly but you could set up a minimal python script to run once or twice a day that would calculate that field for you https://pro.arcgis.com/en/pro-app/2.8/tool-reference/data-management/calculate-field.htm.

[DougGreen]

Hi @Anonymous User, I'm thinking this isn't exactly a SQL question with a "def" statement. I think that's a Python function. You could use something like this as a field calculate function:

def getComplaintCount(complaint1, complaint2, complaint3, complaint4, complaint5, complaint6, complaint7, complaint8, complaint9, complaint10):
    compCount = 0
    compCount += 1 if complaint1 != None else 0
    compCount += 1 if complaint2 != None else 0
    compCount += 1 if complaint3 != None else 0
    compCount += 1 if complaint4 != None else 0
    compCount += 1 if complaint5 != None else 0
    compCount += 1 if complaint6 != None else 0
    compCount += 1 if complaint7 != None else 0
    compCount += 1 if complaint8 != None else 0
    compCount += 1 if complaint9 != None else 0
    compCount += 1 if complaint10 != None else 0

    return compCount

This simply checks if each value is null and adds 1 to the compCount variable if it is not null. If your complaint fields are not nullable strings, you'd want to replace the "None" with a ''.

[Anonymous User]

Hello, this makes sense. I added this to the calculate field, but I get calculate expression is not valid. 

I do see a functions field and count function is not in it. I wonder if this calculate field only performs the functions in this list? 

Thank you Doug!

[Anonymous User]

@jcarlson it worked! @DougGreen Thank you to both of you. Have a great weekend!

[JoshuaBixby]

@Anonymous User, on an unrelated note, using "no" as an abbreviation for "number" in a field name isn't a good practice. For example your current field name is entertotalnocomplaints, which is intended to be read as "enter total number of compliants," but could be read as "enter total of no (or non) complaints."  Also, the field does not appear to be entered by the user directly so it is a bit confusing to start it with "enter."  I suggest a field name of "NumComplaints" or "ComplaintCount".

[Anonymous User]

Thank you! I am learning that certain words do not belong in field names. Great point.