Advanced Formula and Expression Techniques
Advanced expression questions probe the Formula tool's function library and syntax. Core functions: IIF(condition, true, false), IF...THEN...ELSE...ENDIF (multi-line), Switch, ToString, ToNumber, Trim, Left, Right, Substring, FindString, Replace, REGEX_Replace, DateTimeParse, and DateTimeFormat. Know that string comparisons are case-sensitive unless you use UpperCase/LowerCase, and that Null() tests for null while IsEmpty() tests for empty string - they are not interchangeable.
Multi-row techniques: Multi-Row Formula references [Row-1:Field] and [Row+1:Field]; it requires a grouping field to reset per group. Multi-Field Formula applies one expression to many fields and can output new fields with a prefix/suffix. The Record ID tool often precedes a Multi-Row Formula to establish ordering.
Traps: IIF evaluates both branches in some engines - prefer IF/ENDIF when the false branch would error (e.g., division by zero). REGEX_Replace uses $1 for capture groups, not backslash. Date functions expect ISO yyyy-MM-dd unless you parse first. Concatenation uses + for strings but silently coerces numbers - use ToString explicitly.
Rule of thumb: when a formula must handle nulls, wrap with IIF(IsNull([Field]), 0, [Field]) before arithmetic.
Worked example: parse 20240115 into a date: DateTimeParse(ToString([Raw]), "%Y%m%d"), then format with DateTimeFormat(..., "%Y-%m-%d").