change original fields in the multi-fields Formula

[arbel11]

Hi All,

i am using the multi-field formula and get some issues i can't understand why and be happy for some help:)

i an using this command in the formula:

if findstring([commercial_name],' '+[_CurrentField_]+' ')>-1
then
[legal_name]=[commercial_name] else null() endif

when curentField is a word that i try to find in [commercial_name]-(original field) . the problem that in the output, nothing happen as i expected, but under a field that was true, i get '0'.

can someone please elaborate me what the reason for that?

thanks:)

[OllieClarke · Accepted solution]

Hi @arbel11 what's happened here is that you've written a boolean statement. What your formula is saying is:

IF the current value of the ticked fields exists in [commercial_name] then does [legal_name]=[commercial_name]?

IF the current value isn't in [commerical_name] then NULL().

I'm guessing you want to update [legal_name] to [commercial_name] if another column exists in [commercial_name]? If so use a normal formula tool to update [legal_name]:
IF CONTAINS([commercial_name],[whatever field you've ticked]) THEN [Commercial_name] ELSE NULL() ENDIF

Does that help?

[arbel11]

thanks @OllieClarke for your quick respond.

yes,this is exactly what i want to do. i am checking in multiple fields whether i can find the value of the field inside [commercial_name]. i still don't understand why after 'then' nothing happen . why its regards the statement after the 'then' to as a question. 

working with regular formula will not help me in this case.

[OllieClarke]

@arbel11 the reason nothing is happening is because you're not telling Alteryx to update a field, you are asking it whether [legal_name]=[commercial_name] (which will either be 0 or -1), this will update the columns you've ticked in the multi-field formula (or create new versions depending on your settings). In order to update [legal_name] (and not the columns you've ticked in the multi-field, use a normal formula tool - set to update [legal_name] and the following formula:

IF

CONTAINS([commercial_name],[col1])

OR

CONTAINS([commercial_name],[col2])

OR

CONTAINS([commercial_name],[col3]) etc.

THEN [commercial_name]

ELSE NULL()

ENDIF

I hope that makes things clearer

[arbel11]

@ollieClarke first of all yes, so thanks a lot.

in general undarsstanding, their isn't an option in situation like this(updating other column) in the multi-field formula?

[OllieClarke]

Hey @arbel11 you can update 1 or more columns using the multi-field formula tool. However, you can't do it the way you were trying. You can do something like:

[_Currentfield_]*100

which will multiply the value of all the ticked fields by 100, but you can only update ticked fields with the formula you've written, you can't use ticked fields to update another (unticked) field, and the formula you write will apply to all the ticked fields. 

There's more info on the multi-field formula tool here: https://help.alteryx.com/current/MultiFieldFormula.htm Or if you click on the examples in Alteryx, that might help you understand more too 🙂 

[arbel11]

Hi @OllieClarke ,

could be nice to able update original inside the Formula:)

thank you anyway,realy helpful.