Block Until Done - cant figure out how to use

[craigja]

Hi,

I want to write to 3 separate tabs on an Excel sheet but cant as Alteryx tries to write to the Excel file simultaneously and it fails.  I see there is a Block tool but I dont see how I can get that to work in my workflow, can anybody suggest an alternative way?

[CharlieS]

Here's a very relevant article for this scenario:

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/How-to-write-to-multiple-tabs-separately-wit...

The key idea is that each of the Block Until Done outputs will execute in a sequential order. So each sheet output should be on a different output connector of the Block Until Done tool. 

[craigja]

Yeah I looked at that but it wont work with my workflow, if you look at the PDF you should see why

[TrevorY]

To accomplish this you can leverage the Block Until Done tool in your workflow. I have attached an example of how you might use this to write to two different sheets within the same Excel document simultaneously. The Block Until Done tool does not need to be configured and it will ensure that all tools prior to it have completed processing the data before it passes data to the tools after it. In this example the Block Until Done tool is tool #6 of our workflow, so all the previous 5 tools (including our first sheet output) will be completed before the tools after the Block Until Done process the data stream. 

[TrevorY]

To accomplish this you can leverage the Block Until Done tool in your workflow. I have attached an example of how you might use this to write to two different sheets within the same Excel document simultaneously. The Block Until Done tool does not need to be configured and it will ensure that all tools prior to it have completed processing the data before it passes data to the tools after it. In this example the Block Until Done tool is tool #6 of our workflow, so all the previous 5 tools (including our first sheet output) will be completed before the tools after the Block Until Done process the data stream. 

[CharlieS]

The Block Until Done can still be used here, but it's going to get a little tricky. The idea here is that you need to force the order of tools using a Block Until Done. Here's an example to try:

- Use the Block Until Done to write the first Output.

- Once that is complete, the Block Until Done will start the Count Records.

- The second output must wait until the Counts Records can be appended, which forces the order of operations.

- The Count Records field can be deselected in the Append tool so the output data is not affected by this field.

[adamorse]

There's  CReW macro called "parallel block until done" which will run all records through the "1" stream before starting the "2" stream. It works perfectly for writing to different Excel sheets in the same file, and you can chain them to write to more than two sheets. Download/install the CReW macro pack here.

[Joe_McCoy]

This should work

[craigja]

Yeah I think in order to get it to work I need to look at the order/number of the tool - which means dismantling the workflow a little and doing it in the right order.  One of my colleagues has suggested that I can union all my outputs but also add an extra column to signify what tab I want it in, then use the block until tool to write to each tab in order.....

[Joe_McCoy]

The version I posted works for me on several workflows as the block on the bottom stops everything past it until all previous trails have been exhausted.