monzay

Nested IIFs in Attribute Assistant

Blog Post created by monzay on Feb 21, 2017

I've spent a few hours figuring out how to build a nested IIF statement in attribute assistant. There aren't a lot of examples online and while it is a simple statement it still took a lot of trial and error to get it right. I also got help from Michael Miller when I was trying to concatenate a variable and a string and while the nested IIF worked in this instance it wasn't my main focus at the time.

 

IIF(expression, truepart, falsepart)

This website was also helpful http://www.simply-access.com/Nested_IIF_Statement.html

 

I'm building a sign inventory using high resolution aerial imagery and Google Streetview. With Portal / ArcGIS Online it makes it easier to share and collaborate with others in my organization. Especially those who do not use ArcGIS Desktop but they know how to use a web browser. They wanted to be able to see a sign image in the popup. I wanted an easy way to populate that field as I added new signs. 

 

I created a featureclass with one feature with the following fields:

 

SIGN_CATEGORY: 1 = Regulatory, 2 = Warning, 3 = Guide, 4 = School, 5 = Marker

MUTCD_ID: various lists for each of the above subtypes

IMAGE_LINK: partial URL for the link. (The custom attribute display stores the other part of the URL).

 

This worked:

IIF([MUTCD_ID]="UNK", "no image",IIF([SIGN_CATEGORY]=1, "regulatory/"+LCase([MUTCD_ID]),"no image"))

We don't always know what the MUTCD ID is so we record it as unknown until someone in the sign shop with more knowledge can look at it on Streetview or in the field. 

 

This did not:

IIF([MUTCD_ID]="UNK", "no image",IIF([SIGN_CATEGORY]=1, "regulatory/"+LCase([MUTCD_ID]),"no image"),IIF([SIGN_CATEGORY]=2, "warning/"+LCase([MUTCD_ID]),"no image"))

 

because the 2nd "no image" was supposed to be the beginning of the falsepart which was the next IIF

This worked:

IIF([MUTCD_ID]="UNK", "no image",IIF([SIGN_CATEGORY]=1, "regulatory/"+LCase([MUTCD_ID]),IIF([SIGN_CATEGORY]=2, "warning/"+LCase([MUTCD_ID]),"no image")))

Even when I thought I understood the pattern I still messed up. In this case I forgot the falsepart "no image" at the very end

IIF([MUTCD_ID]="UNK", "no image",IIF([SIGN_CATEGORY]=1, "regulatory/"+LCase([MUTCD_ID]),IIF([SIGN_CATEGORY]=2, "warning/"+LCase([MUTCD_ID]),IIF([SIGN_CATEGORY]=3, "warning/"+LCase([MUTCD_ID])))))

I built the expressions in Notepad++ because it was easier and the parentheses pairs will highlight when the cursor is next to one. I could also keep track of what worked and what didn't. The final expression:

 

IIF([MUTCD_ID]="UNK", "no image",IIF([SIGN_CATEGORY]=1, "regulatory/"+LCase([MUTCD_ID]),IIF([SIGN_CATEGORY]=2, "warning/"+LCase([MUTCD_ID]),IIF([SIGN_CATEGORY]=3, "guide/"+LCase([MUTCD_ID]),IIF([SIGN_CATEGORY]=4, "school/"+LCase([MUTCD_ID]),IIF([SIGN_CATEGORY]=5, "marker/"+LCase([MUTCD_ID]),"no image"))))))

After some additional work it turns out my logic was incorrect and more testing was needed. Here is what is working now:

 

IIF([SIGN_CATEGORY]=1 AND [MUTCD_ID]<>"UNK", "regulatory/"+LCase([MUTCD_ID]),IIF([SIGN_CATEGORY]=2 AND [MUTCD_ID]<>"UNK","warning/"+LCase([MUTCD_ID]),IIF([SIGN_CATEGORY]=3 AND [MUTCD_ID]<>"UNK","guide/"+LCase([MUTCD_ID]),IIF([SIGN_CATEGORY]=4 AND [MUTCD_ID]<>"UNK","school/"+LCase([MUTCD_ID]),IIF([SIGN_CATEGORY]=5 AND [MUTCD_ID]<>"UNK","marker/"+LCase([MUTCD_ID]),"unknown/unknown_"+CStr([FACILITYID]))))))

 

 

 

 

 

Outcomes