Saving output .XLSX on SP site vs Parallel Block Until Done

[magdaemem]

Hello Everyone - I spent last 2 days trying to resolve my issue and searched multiple Community topics but unfortunately did not find the answer. 

I am running a workflow which creates one XLSX output file with 3 different sheets inside (1, 2,3) - I attached the screenshot. I am using Parallel block until done to make sure sheets are being saved down in the correct order. It all works perfectly when output file is saved down on a shared drive but when I set it up to save down output on SP site there is an error when 3rd sheet is being saved down. Apparently it looks like there is a small delay between Sheet1 and Sheet2 but not enough delay between Sheet2 and  Sheet3. Designer cannot open file to write Sheet3 because it appears file was not closed yet after saving down Sheet2. 

Wait a second macro doesn't work as Sheet3 has 22k records so the wait time would be way too long, I tried Throttle as well but it doesn't seem to be delaying the last output sheet execution, only limiting number of records being processed. 

Error text below:

Output Data (148) Unable to open file for write: <Sharepoint Path>: This file is checked out or locked for editing by another user. (220)

Did anyone have similar issue? Would you be able to help me please to find a solution?  

[messi007]

Dear @magdaemem,

Have you tried to split the workflow and then use the crew macro (Runner) in order to run step by step.

Hope that helps,

Regards,

[magdaemem]

Thank you for your prompt response. I just looked up some more information on the Runner macro you mentioned but as far as I understand it requires splitting the workflow into completely separate workflows that are producing Sheet1/Sheet2/Sheet3. The issue is that this is pretty big workflow using 9 data inputs. Moreover outputs are not completely independent but are using different data sets which are being processed throughout the entire workflow run. I am afraid that splitting it would either require rebuilding the entire workflow or just copying it & removing part of the outputs which does not seem to be too efficient. Is my understanding correct?

[messi007]

@magdaemem,

Yes you're correct. However you can you convert only the output part into macro and use the block until done before the each macro that create the files.

Hope that helps!

Regards, 

[AkimasaKajitani]

Hi @magdaemem 

Have you tried putting in the BlockUntilDone tool before all the data output tools?

[magdaemem]

Thank you messi007 and AkimasaKajitani. Isn't my Parallel Block until done I used supposed to do the same? Am I wrong?

My issue is around timing of writing Sheet3 after Sheet2 but order itself is already sorted out within the workflow as sheets are being saved down correctly when I use a drive as an output directory. Problem starts when I am saving it on SharePoint site. 

[AkimasaKajitani]

I think that your problem may be SharePoint-specific.

How about "Wait A Second" macro?

You can stop the execution of the workflow at the specified time.

[magdaemem]

As I mentioned in my first post - Sheet3 includes 22k records and I tested Wait a Second macro yesterday, wait time would be way too long 😉 

[AkimasaKajitani]

Sorry, I missed the description of Wait A Second macro.

How about saving it once locally and then copy it to SharePoint by batch file?

[magdaemem]

No problem at all, I really appreciate the ideas - I am not an expert so even the tools I tried may still have a lot to offer in this situation 🙂 

In relation to your idea about saving locally and copying to SP - would that work with Alteryx Server later on?

I did not mention it before but the reason I wanted to connect it to SharePoint site is that the workflow is written for a team who does not have access to my team's shared drive so I wanted to make sure all Input and Output files are on publicly accessible SP Site within the company to avoid issues with permissions.