0

I am using Arc 10.3.1 and attribute assistant. I would like the attribute assistant to autopopulate a field called [Supervisors] with a conditional statement depending upon the values of other fields. So far I have gotten this expression to work 

iif([SM_Zone] = "A" , "Greg"  ,"<Null>")

but I need more conditions added and when I add more conditions the expression fails. The expression that is failing is:

iif([SM_Zone]="A" & [REQUEST_TYPE] <> "PW (SM)-Right Of Way Weeds"),"Greg", iif([REQUEST_TYPE] = "PW (SM)-Right Of Way Weeds","Bill","Null")

also I would really like to craft the statement with this logic: 

if ([SM_Zone] = "A" & [REQUEST_TYPE] LIKE "%RIGHT%"), "Bill" , "Greg")

ie: if the point is in zone A and the type has %RIGHT% in it then the supervisor is bill, else the supervisor is Greg.

Like I mentioned before I have gotten the simple statement to work but when I add other conditions it fails. Could someone please point me in the correct direction or help identify what is causing the failure.

Improve this question
2
  • I would like to thank @Richard Fairhurst for the help on this one. After his help and a little tinkering the correct working expression is: iif(InStr(UCase([REQUEST_TYPE]), "RIGHT") > 0, "Bill" , iif([SM_Zone]="A","Greg", iif([SM_Zone]="B","Champ", iif([SM_Zone]="C","Vacant", iif([SM_Zone]="D","James", vbNull)))))
    – ed.hank
    Commented Jan 26, 2016 at 15:11
  • Just to bring this up, I noticed this web page ceus-now.com/… is copying content from here without any attributions to stackexchange :/
    – ed.hank
    Commented Jul 20, 2016 at 19:48

1 Answer 1

Reset to default
3

Although Attribute Assistant uses VB statements, you are using the wrong elements of VB for your logic. The "&" operate concatenates strings together, it does not act as a logical AND that combines two separate expressions into a single logical result. Additionally, some of your statements are using SQL syntax which does not work in Attribute Assistant (LIKE is not supported or wildcards).

For your first failing statement, your parenthesis are out of place and the logic should be rearranged to be more complete and efficient. Also I believe when you use "<Null>" as a return value it is placing a string of the actual word "<Null>" in the field, not setting the field to Null in the database. I believe vbNull should be used as a return value to make the field actually Null.

iif([REQUEST_TYPE] = "PW (SM)-Right Of Way Weeds","Bill", iif([SM_Zone]="A","Greg", vbNull))

In this statement by testing [REQUEST_TYPE]="PW (SM)-Right Of Way Weed" first you don't have to test for all other values of [REQUEST_TYPE] in the second half of the first iif statment. The second iif statement must be completely enclosed in the parenthesis of the first iif statement and must act as the false return value (or true return value when appropriate) of the first iif statement to work.

The closest thing to LIKE in VBS is InStr. Also strings should be explicitly cast to upper case to ensure case insensitivity. So I believe this would work for your statement that uses LIKE for any style of capitalization of the word "Right":

iif([SM_Zone] = "A" AND InStr(UCase([REQUEST_TYPE]), "RIGHT") > 0, "Bill" , "Greg")
Improve this answer
10
  • 1
    I was about to answer with the same comment about parentheses, but Richard says it better
    – Midavalo ♦
    Commented Jan 20, 2016 at 19:05
  • That was it! I appreciate the help. Also do you know if there is a guide/tutorial to how to craft these expressions?
    – ed.hank
    Commented Jan 20, 2016 at 20:15
  • Ok, well it worked for one, but now I am trying to stack the expression like this iif([SM_Zone] = "B" AND InStr(UCase([REQUEST_TYPE]), "RIGHT") > 0, "Bill" , "Champ"), iif([SM_Zone] = "D" AND InStr(UCase([REQUEST_TYPE]), "RIGHT") > 0, "Bill" , "James") but no matter what zone, A, B, C, or D, it always populates it with James . How do i properly format this so if its zone A and not like "RIGHT" it goes to Greg, if its zone B and not like "RIGHT" it goes to Champ, if its zone D and not like "RIGHT" it goes to Antonio, and everything else goes to Bill ? Could you please help with the format?
    – ed.hank
    Commented Jan 20, 2016 at 20:45
  • If you want to test for anything that does not have "RIGHT" in it you have to use: InStr(UCase([REQUEST_TYPE]), "RIGHT") = 0. The logic should be revised to make the not like "RIGHT" test the enclosing iif statement. You cannot put onf iif statement after another. There is always one iif statement that can return the true or false result as an embedded iif statement. Anyway your describe statement is too complex to write in a comment. Later I will amend my answer to cover your request.
    – Richard Fairhurst
    Commented Jan 20, 2016 at 21:13
  • Thank you. I see the error in my logic, I just am not sure how to fix it. Is there a way to place an elseif after the first iif statement?
    – ed.hank
    Commented Jan 20, 2016 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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