Multiple IF statements combined with CONCAT possible?

[ScottHi]

Hello All, I am trying to complete a formula where a calculation will only CONCAT fields if there is data to CONCAT. CONCAT(IF(custom field called Role 1 has data,CONCAT(Role 1 GL Code," | ",)," ", IF(custom field called Role 2 has data,CONCAT(Role 2 GL Code," | ",)," ", IF(custom field called Role 3 has data,CONCAT(Role 3 GL Code," | ",)," ", IF(custom field called Role 4 has data,CONCAT(Role 4 GL Code," | ",)," ", ....ec up to role 10 I have tried writing it out but I can't figure out if I can do multiple ID statements because I can't even get my IF statement to work. Would I put IF(Role 1>" ") so if role 1 is greater than nothing then CONCAT? Role 1 can be many different things so its not as easy as saying that the result will be, its better to say if its anything but blank. Any help with this would be great. I am also trying to create a line break in the result but that doesn't appear possible so as you can see I am trying to separate the potential results with a " | " provided there is data in the Role # field. Thank you in advance! Scott Scott Hinchman Automatic Data Processing, Inc.

[AnthonyIm]

Hi @Scott Hinchman , I think we have multiple things going on. First, just looking at what you have so far, you shouldn't have the CONCAT before the IF statement there in the first line. CONCAT should be used for your True or False values Second, my first thought was something like IF(ISBLANK(DE:Role 1),IF(ISBLANK(DE:Role 2),"",CONCAT( DE:Role 2 GL Code," | "),CONCAT( DE:Role 1 GL Code," | ")). However, this will only work if you are returning 1 value. It shows like you could have multiple values returned. Something you might want to try is changing your Custom Field to a Checkboxes Field. You can then list your the various roles. After you are down, you can click the settings gear and select Show Values. You can then put in the GL Code in the Values box. I hope that makes sense. LOL! Let me know if it doesn't. Anthony Imgrund FCB

[Doug_Den_Hoed__AtAppStore]

Hi Scott, If I'm following, I'd suggest you use this pattern: CONCAT ( IF(ISBLANK({DE:ParameterA},'',{DE:ParameterA} + ' | ') ,IF(ISBLANK({DE:ParameterB},'',{DE:ParameterB} + ' | ') ,etc... ) With this approach, you will end up with a trailing " | ", which if you can live with, great...if not, There's More. Regards Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[ScottHi]

Thank you both for your suggestions. I think I'm stuck with a CONCAT and trailing |||| for instances where there is no GL Code entered. As I would need it to show all the GL codes used in that forms submission. I was trying to rid myself of the | with the if statement so it would only return IF there was a value in the Role X GL Code field. Having said all that, do either of you know how I might have the result return a new line for each GL Code? If so then I can drop the separator " | ". so instead of showing GLCODE1 | GLCODE2 | GLCODE 3 | | | | | | | The result would instead be GLCODE1 GLCODE2 GLCODE3 Scott Hinchman Automatic Data Processing, Inc.