Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Interface Tools: Update Value with Formula Issue

ripcityscorp15
7 - Meteor

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 WorkflowMain 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 WorkflowMacro 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!

4 REPLIES 4
patrick_digan
17 - Castor
17 - Castor

@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
17 - Castor
17 - Castor

@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):

Capture.PNG

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
7 - Meteor

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
5 - Atom

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

Cheers!

Labels