Concat Equation - Hide comma and space when empty field/string

App Development
1

Greetings Community-

Can anyone help with an equation…I seem to be stuck…

I’d like to concatenate several fields in a table and separate with a comma and space. However, when a field is empty/NULL, I’d like to hide the comma and space.

Current Equation:

CONCAT({Prevent Cap Txt Formula},CHAR(44),CHAR(32),{Protect Cap Txt Formula},CHAR(44),CHAR(32),{Mitigate Cap Txt Formula},CHAR(44),CHAR(32),{Response Cap Txt Formula},CHAR(44),CHAR(32),{Recovery Cap Txt Formula})

Example when field is empty
2023-06-11_15-56-01

When I tried Iris AI it gave me a formula of:

CONCAT({Prevent Cap Txt Formula}, IF(NOT({Protect Cap Txt Formula} = ''),CONCAT(', ', {Protect Cap Txt Formula}),''), IF(NOT({Mitigate Cap Txt Formula} = ''), CONCAT(', ', {Mitigate Cap Txt Formula}),''), IF(NOT({Mitigate Cap Txt Formula} = ''), CONCAT(', ', {Mitigate Cap Txt Formula}),''), IF(NOT({Response Cap Txt Formula} = ''),CONCAT(', ', {Response Cap Txt Formula}),''), IF(NOT({Recovery Cap Txt Formula} = ''),CONCAT(', ', {Recovery Cap Txt Formula}),''))

However, “NOT” is not a valid function in Tadabase.

Any help would be appreciated!

Adam

2

There may be better way, but what I have done in similar situation is to accept that the concat will create multiple commas. And wrap the concat statement inside a Replace statement(s) that look for and replace the commas will null.

3

Did some searching and trial and error. This worked for me:

CONCAT_WS(‘’,IF(LENGTH({Prevent Cap Txt Formula}), {Prevent Cap Txt Formula},NULL), IF(LENGTH({Protect Cap Txt Formula}), {Protect Cap Txt Formula},NULL), IF(LENGTH({Mitigate Cap Txt Formula}), {Mitigate Cap Txt Formula},NULL), IF(LENGTH({Response Cap Txt Formula}), {Response Cap Txt Formula},NULL), IF(LENGTH({Recovery Cap Txt Formula}), {Recovery Cap Txt Formula},NULL))