I am assuming that each row is a different record.
Field 1 = "FIRST_YEAR-ANNUAL Anguilla" ; "1300ANG" ;
Field 1 = "FIRST_YEAR-ANNUAL TRS" ; "4440" ;
Field 1 = "FIRST_YEAR-ANNUAL Preco" ; "4420" ;
A better option would be to use the Looked Up Value option to look up these codes from a related table so that you can edit this list without having to redefine a calculation. There's an option to specify a value if there is no exact match where you can enter: 4005CL1.
1 of 1 people found this helpful
You're on the right track with Case() statements, however the last part of a case() statement is what it returns if all other conditions are false. Like so:
If this is true ; Return this ;
If this other thing is true ; Return this other thing ;
Otherwise Return This
Note that the last line doesn't have an "IF" condition, it's just returned as the last parameter if any other condition above was not met.
So in your case, something like:
Subscription_Period = "FIRST_YEAR-ANNUAL Anguilla" ; "1300ANG" ;
Subscription_Period = "FIRST_YEAR-ANNUAL Preco" ; "4420" ;
etc.... for all others follow the same format: If this ; Then result ;
When you use Case() that way, if none of your Subscription_Period values match the listed conditions, it will return 4005CL1 as the default result for things that do not match.
There are 3 Case(...) so each of them is executed. Since PRECOA is found in the second and third Case, the value is changed twice in the order.
Don't use multiple case functions. Just use one as per my example.
I think that you figured this out, but I'm pointing this out to be sure.