Dynamically update a formula based on user selection

[OscarM]

Hi there,

I'm a newbie with Alteryx, currently attempting to build a workflow app that complete the following activities: 

1) User upload a file (*.xlsx file)

2) User to select a column within that file

3) For the selected column, the workflow dynamically applies a formula on that column. (In this use case, the formula would change the last 4 digits/letters of the selected column into "XXXX", thus creating a masking element)

4) File output.

So far, I am able to complete 1) and 2), and currently stuck in 3). Does anyone know how we can configure the workflow to allow the formula input tool to be updated based on the column selected by the user? 

Thanks for all your help!

[grossal]

Hi @OscarM,

you are already doing an amazing job! You can directly connect your list input with your formula tool and it should give you the option to change parameters!

I would recommend connecting it to the bolt-anker to create a new Action-Tool to get more options for the configuration.

Let me know if you need further help.

Best

Alex

[OscarM]

Thank you for your response and encouragement 🙂

I just tried connecting the input list to the formula tool and it gave me an option to change parameters, but when I run it, the workflow only works for the one specific column in my formula (F2 in this case). 

I attached my workflow for reference, any guidance is appreciated.

Thanks!
Oscar 

[grossal · Accepted solution]

Hi @OscarM,

it was actually a bit trickier than expected, but I got closer to your solution:

The Text Box allows you to pick the column that should be masked. I had to use the 'Update Raw XML with Formula' option in the Action tool. The Interface looks like this:

I also tried a Multi-Field-Formula, but you can't use your Input List for it because it is set to "Select Tool Mode" instead of Custom Generated List.

I'll try to switch the Select tool with a Dynamic select and tell you if I got some improvements. Current version attached.

Best

Alex

[grossal · Accepted solution]

Okay, that worked actually quicker than expected and looks like this:

Workflow attached. Let me know if this solved your problem!

Best

Alex

[OscarM]

Hi Alex,

It worked!! Thank you so much for your help!

I wasn't able to get your 2nd workflow to work (this perhaps could be due to my limited understanding to the Dynamic select feature and not able to configure the tool correctly), but your 1st workflow worked like a charm!

I'll study your 2nd workflow and hopefully I can arrive at the same result!

Again, much appreciate all your help.

Oscar

[grossal]

Hi @OscarM,

would you mind accepting the 1st Workflow as a solution if it solved your problem?

I'll also try to explain what I do in the second:

Example: 

Let's just say your List in the List Box Contains the following:

- F1

- F2

- F3

Setting - Listbox

This generates:

"F1","F2","F3"

Setting - Dynamic Select: 

The IN Operator works like this:

[Name] IN ('F1', 'F2', 'F3') 

If [Name] is F1, F2 or F3 three, the condition applies and the Select tool will Select the column. All other columns are deselected.

I used 'Liste' (German word for list) as a Placeholder in our Dynamic Select. We will update the Placeholder with the Action-Tool above.

Setting - Action Tool 1:

This replaces only the 'Liste' in our Dynamic Select with the List we created in our List Box. The final condition looks basically like this:

[Name] IN ("F1","F2","F3")

We do something very similar with the Multi-Field Formula in a second.

Setting - Multi-Field-Formula

Let's look at the full formula first:

IF [_CurrentFieldName_] IN ('Liste')
THEN left([_CurrentField_], length([_CurrentField_])-4)+"XXXX"
ELSE ""
ENDIF

The Multi-Field-Formula works for all columns you selected, therefore we can grab the Field-Name of the currently processed column with [_CurrentFieldName_] and check again if it's in ('Liste') (we also update Liste here with the Action).

If the Field-Name of the currently selected column is in our List (that come from the List-Box and only lists that checked Fields), then we want to apply our Formula. We can't say a specific column name again, because we work with multiple columns, therefore we need to use [_CurrentField_] which holds the value of our currently processed column.

The Else-Parts just cleans up all other columns with a blank. 

If you create it from scratch, you should uncheck the "Copy Output Fields and Add" -> this creates new columns for all processed columns. You also need to make sure, that the data type is right:

This tells the Multi-Field we want to process only Text-Columns.

I hope this helped to understand it better. Feel free to ask if something isn't clear and I'll try my best to explain it even better 😃

Best

Alex

[OscarM]

Hi Alex,

Thank you so much for the detailed explanation. It definitely did the job and I am able to get the 2nd workflow to work too!

I agree that while both workflows were able to get to the job done, the 2nd workflow seems to work better.

Thanks again for all your help with this!

Regards,

Oscar 

[CHIRANJEEVI-IANALA]

Hi,

I'm having some difficulties implementing your idea as above; could you kindly provide the entire Alteryx workflow?