I am creating a workflow that creates three sheets within the same Excel file every time it is run. The Excel file has a consistent name among all three output tools so that they all refer to the same piece of data while the workflow is running.
When the last output tool is finished, it should replace the name of the entire file with a unique name generated by a formula tool and stored in a field. As I'm currently aware, this is done by checking "Take File/Table Name From Field" and selecting "Change Entire File Path" within the output tool.
The problem I'm encountering is that I can't find a way to control the order of execution of the output tools. The three sheets that need to be created within the output file receive data from different streams, each of which may run faster or slower than others depending on the data I provide as an input. Due to this, the order the sheets are created in may change.
I've used Block Until Done tools throughout the workflow, but the only way to know which output executed when, is to read through the workflow messages in the results window. Is there anything I can do to ensure a chosen output tool is the very last thing that executes in my workflow so that I can be certain all the prior steps are complete before changing the file name?
Solved! Go to Solution.
There are 2 approaches you can follow:
1. Use the Parallel Block Until Done tool from the CreW Macros:http://www.chaosreignswithin.com/p/macros.html
2. You can force the sequence of execution by using something like the Append fields tool with the Block Until done tool.
The Append fields tool will "hold" the data until data from both inputs have arrived. This may add unwanted columns to your 2nd and 3rd data streams, but you can unselect them in the Append fields tool configuration. Here's a mockup with 3 streams of data.
However, this will probably not do what you want it to do. If you change the filename using a field with the last Output data tool, it will only write the data of the last sheet to the file with the new name. The previous Output data tools already wrote data to the original file.
Again you have a couple of options.
1. Use the change filename using a field option in all the output data tools
2. Use another method to change the filename after the all the data is written - this will require a run command tool that implements a DOS copy or rename command
The only reason why you wouldn't use option 1 is if you're writing data to an existing file/template. In that case I'd suggest creating a copy of the file with the correct new filename first and then write data for all the sheets to it. This post contains an example of how to do that. https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Editing-and-saving-a-macro-enabled-fil...
Hi David, thanks for all of the insight on this. Right now I've been debugging my workflow to see which sheet tends to get written last under normal circumstances. In my case, the file rename should always happen after the last sheet is written. Undertsanding some of the limitations I'm working within, I think I'm leaning towards method 2 in your last comment but using the Python tool rather than Windows command prompt to handle file rename.
If inconsistencies develop in the normal exceution order I've observed, I will probably need to explore chaining together those block and append tools as you mentioned. I may reach out again in another post if I need to go further down that route.
I did see the CREW blog and the macro tools availble there but if I recall correctly I think I need to be using native Alteryx tools to be able to publish to my orgaiznation's gallery.
Thanks for letting me know about these workarounds, definitely appreciate it!