In Modify Module use nested if to update a field

pgiovane — Fri, 05 Apr 2024 13:31:42 GMT

Hi All,

Am building a nested If statement to update a field based on the values of two fields:

IF(OR($(FIELD_A)="VALUE A" & $(FIELD_B)<>"VALUE X'",$(FIELD_B)="NEW VALUE",

IF(OR($FIELD_A)='VALUE B" & $(FIELD_B)<>"VALUE Z", $(FIELD_B)="NEW_VALUE_1", ETC))

So, for example, if Field A = 'Apple' and Field B <>'Red', update Field B = 'Red', if field A = 'Orange' and Field B <> 'Orange', updated Field B = 'Orange'

I used the solution posted by anthonyvalidity on 7/6/23 as a starting point but my results are all 'true' or 'false' instead of the updated value that i want.

Thanks!

Pam

Re: In Modify Module use nested if to update a field

AnthonyValidity — Fri, 05 Apr 2024 14:08:22 GMT

Hi @pgiovane !

There's a couple things that I'm seeing as problematic with the formula you provided. It sounds like you're wanting to use an AND formula in both IF formulas. The second IF formula will be used if the first IF statement is not true. This is what I imagine the formula would look like:

IF(AND($(FIELD_A)="VALUE A",$(FIELD_B)<>"VALUE X"),"NEW VALUE", IF(AND(($FIELD_A)="VALUE B",$(FIELD_B)<>"VALUE Z"),"NEW_VALUE_1",$(FIELD_B)))

Note that you don't tell the formula where you want the true value to be - that is done when you select the field you want to change on the left side.

Outer IF Function:
- The outermost IF function checks two conditions using the AND function.
- If both conditions are TRUE, it returns "NEW VALUE".
- If either condition is FALSE, it proceeds to the next part of the formula.
Nested IF Function:
- If the outermost IF function's conditions are not met, the formula enters a nested IF function.
- The nested IF function also checks two conditions using the AND function.
- If both conditions are TRUE, it returns "NEW_VALUE_1".
- If either condition is FALSE, it proceeds to the next part of the formula.
Default Value:
- If none of the conditions in the IF functions are met, the formula returns the original value of $(FIELD_B).

This formula checks the values of $(FIELD_A) and $(FIELD_B). If $(FIELD_A) is "VALUE A" and $(FIELD_B) is not "VALUE X", it returns "NEW VALUE". If $(FIELD_A) is "VALUE B" and $(FIELD_B) is not "VALUE Z", it returns "NEW_VALUE_1". If neither of these conditions is met, it returns the original value of $(FIELD_B).

IF(AND(logical1, logical2), value_if_true, IF(AND(logical1, logical2), value_if_true, value_if_false))

Regards,

Re: In Modify Module use nested if to update a field

pgiovane — Tue, 09 Apr 2024 12:05:56 GMT

Hi there,

Yes, your suggestion is working; duh me for not switching out the 'or' for the 'and'. So far, so good though.

Thanks!

Pam

topic In Modify Module use nested if to update a field in DemandTools

In Modify Module use nested if to update a field

Re: In Modify Module use nested if to update a field

Re: In Modify Module use nested if to update a field