Interface Tools: Update Value with Formula Issue

[ripcityscorp15]

Hi All,

Here is my current scenario:

I have an excel file with multiple tabs, all with different schema. I am currently trying to combine these tabs into one output from Alteryx.

My current workflow looks like this:

 Main Workflow

1. Input has "LIST ALL SHEET NAMES" selected, & Ouput File Name as Field = "Full Path"

2. Text to column tool splits out The File Name and the Sheet Name as such:

• FileName: C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx|||`2018 Budget$`
• FileName1: C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx`
• FileName2:  ||List of Sheet Names>

3. Select Tool: Changes the Name of "FileName1" to "File Name"

4. MACRO TOOL:

• Macro Workflow
• Currently My Macro Tool Looks like this:
• The First Action Tool is "Update Value (Default)" 
• I select the box "Replace a specific string" and then remove the  "||List of Sheet Names>" so all I have remaining is the Filename1 value listed previously.
• • The Second Action Tool is "Update Value (Default)" 
  • I select the box "Replace a specific string" and then remove the  "C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx||" & " S' " so all I have remaining is the First Tab Name.
  • This will have same Tab name as the Tab selected in the Input File Listed below.
  • Input File: Choose the First Tab of the excel file
• (It can be any tab, but easiest to just choose the First. Also, this Tab name will be the one listed in the 2nd action tool previously mentioned)

RESULT: When I currently do all of this -- the workflow works as I imagined. Pulling all the different excel tabs into one Alteryx table that can then be manipulated. 

HOWEVER! I do not want future users of the tool to have to go into the Macro, and have to manually manipulate the "Replace a Specific String" in the Action Tools in order for it to work. 

Therefore, I then used "Update Value with Formula". It is my understanding that the same string of data would flow into the Action Tool (aka the FileName listed above), and that using a formula would be able to get me tot he same result I had been manually typing on the First and Second Action Tool. 

As a result, here are the two formulas I currently am using on the action tools in the Macro:

• First Action Tool:
• Substring([#1],0,Length(Left([#1],FindString([#1], "|"))))
• This should get me the result:
• C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx 
  
• Second Action Tool:
• Substring([#1],Length(Left([#1],FindString([#1],"|")+1)),
  (Length(Left([#1], FindString([#1], "$")))
  -Length(Left([#1],FindString([#1],"|")+1)))) 
• This should get me the name of my first tab:
• 2018 Budget 

When I run the workflow, though, with the Macro using these formulas I get this error:

• Error: New MULTIPLE TABS BATCH MACRO_V2 (79): Record #1: Tool #11: File not found "C:\Users\zszykowny006\Documents\2018 Budget"

I have confidence these Formulas above are correct after testing them in a different workflow using the formula tool.

So my question is:

• Why does the full file name not come through? The problem seems to be with my First Action Tool formula...As it only only pulls through " C:\Users\zszykowny006\Documents\" and is missing "Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx"
• If the full file name comes through, I feel like this should work the same way as when I was manually manipulating the Update Value (Default) option.

I have spent many a hour on this, so any help would be truly appreciated!

Thanks!

[patrick_digan]

@ripcityscorp15 I will have a look at why your version is not working. In the meantime, I've attached the way I would do it using reference shortcuts. I find these much easier to work with.

[patrick_digan · Accepted solution]

@ripcityscorp15 You have 2 action tools, and the second one is completely overwriting everything that the first one is doing. I would connect both of your control parameters to 1 action tool. Perhaps I'm missing something, but this simpler formula seemed to work with your dataset (where #1 is the file path and #2 is the sheet name):

For future reference, you can use the field [Destination] in the formula to use what's already in the tool. The action tool is the 1 case where you can use data coming in as well as the data that's already in your tool to build your formula. In this particular case, i think it's easiest to just use 1 action tool. For an example, there are times where I use a function like:

Replace([Destination],"text to replace", DateTimeParse([#1],"%m/%d/%Y"))

[ripcityscorp15]

Incredibly helpful! There was very little, via search, I could find in the Community, giving good detail about this specific functionality of the Action Tool. Thank you for taking the time to explain it all out!

[MatteoFedrizzi]

Great tip!!! Thanks a lot @patrick_digan, this has helped me fix a similar issue.

Cheers!