04-05-2024 06:31 AM
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
Solved! Go to Solution.
04-05-2024 07:08 AM
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:
Nested IF Function:
Default Value:
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,
04-05-2024 07:08 AM
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:
Nested IF Function:
Default Value:
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,
04-09-2024 05:05 AM
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
Get industry news, expert insights, strategies, and hot tips, served up fresh every week.
Visit the Validity blog