If and If nested statements

[Sean_Lake1]

Good day all.

I have a formula that currently works:

IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),

{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name})&" "&{Engine Version}

However, I now need to add an additional IF statement to check for Archived:

IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),

IF({Course Status}='Archived(DO NOT DELETE)', “ARC-”&CONCATENATE(LEFT({Course Level},1),

{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},

CONCATENATE(LEFT({Course Level},1),

{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name})&" "&{Engine Version}

As you can guess, my second IF apparently isn't working.

I get the formula has an error.  

Any help would be appreciated.

Sean

[Brian_Swanson]

Hello @Sean_Lake1

It looks like your issue is more about closing out statements with a paren than anything.  The formula should read:

IF({Course Status}='Retired',

"RET-"&CONCATENATE(LEFT({Course Level},1), ""),

IF({Course Status}='Archived(DO NOT DELETE)',

  "ARC-"&CONCATENATE(LEFT({Course Level},1),

{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},

CONCATENATE(LEFT({Course Level},1),

{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name}))&" "&{Engine Version}

The first Concatenate line was still looking for some level of input and the paren to complete its function.  Also the end of the equation did not fully close out the first IF statement with a paren.  The midde "ARC-" is using curly quotes not straight quotes which also causes issues.  You can tell that AT is not picking up that a string because it is not colored.

There seems to be a lot of repetition within the formula.  As I understand it you are trying to add RET- or ARC- in front of a common concatenation of various field information.  The field informaiton woudl appear to stay the same regardless of the presence or absence of the RET- or ARC- designation.  This is a bit cleaner way of putting this together although I am sure there are still better ways.

IF(

   {Course Status}='Retired',

   "RET-",

   IF(

      {Course Status}='Archived(DO NOT DELETE)',

      "ARC-", Blank()

   )

)

&CONCATENATE(

   LEFT(

      {Course Level},1

      ),

   {Course Type Coding (from Course Type)},

   ".",

   {Sequence Number}

   )

&"-"&{Course Name}

&" "&{Engine Version}

Have a great day!

[Sean_Lake1]

Your first solution, worked, however the bottom one wiped out all the data!  No worries, ctrl-z fixed it fast!  Thanks!

[Sean_Lake1]

I finally got it.  AT support(Sean K), and I worked on it, but when we logged off, I hadn't noticed that the FULL field was opened and it'd duplicated the course id 2x. EEK!
No worries, I worked it out:

SWITCH({Course Status},"Retired","RET-"&CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name}&" "&{Engine Version},

"Archived(DO NOT DELETE)","ARC-"&

CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name}&" "&{Engine Version},

CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name}&" "&{Engine Version})
Making it pretty, also led to breaking, so this is how I wound up writing it into the formula field.
PHEW, one down!