Multi-Field formula with Concatenated string for [_CurrentFieldName_]

[MartinJacobsen]

Hi, I am trying to set certain fields NULL depending on what the fieldnames are, from a specific list.

So the non-dynamic version in the multi-field formula tool is:

IF [_CurrentFieldName_] IN ("FieldNameA","FieldNameB","FieldNameC")
THEN NULL()
ELSE [_CurrentField_]
ENDIF

Which works fine.

But I would like to make it dynamic, so I make a new field containing the relevant fieldnames concatenated with quotes and commaseparated:

ConcatVariable = "FieldNameA","FieldNameB","FieldNameC"

But when I then change the syntax in the multi-field formula tool, it doesnt work anymore:

IF [_CurrentFieldName_] IN ([ConcatVariable])
THEN NULL()
ELSE [_CurrentField_]
ENDIF

I have of course Googled and searched, but in vain :( Any tips on why this doesnt work, or alternative solutions?

[patrick_digan · Accepted solution]

@MartinJacobsen I think you would need to switch to a contains formula. Here would be something simple:

If contains([ConcatVariable],[_CurrentFieldName_]) then Null() else [_CurrentField_] endif

Note that if you had fields Field1 and Field11 and you wanted to Null Field11, it would null both Field1 and Field11. If you want to avoid an issue like this, then you could just add the quotes around your fieldname.

If contains([ConcatVariable],'"'+[_CurrentFieldName_]+'"') then Null() else [_CurrentField_] endif

[MartinJacobsen]

Fantastic, I implemented your second solution and it worked flawlessly! Thank you very much!

[patrick_digan]

👍 For what it's worth, Alteryx reads the field name list as 3 separate items, and it compares the currentfieldname name to each:

IF [_CurrentFieldName_] IN ("FieldNameA","FieldNameB","FieldNameC")

When you change it to a formula, it unfortunately is seen as a single string (not 3 separate items)

IF [_CurrentFieldName_] IN ([ConcatField])

where [ConcatField] = '"FieldNameA","FieldNameB","FieldNameC"'