Conditional Workflow Output Based on Success of Earlier Outputs

[anthonyzhao]

Hello,

I wanted to find out if there is a way to run an Output Data tool in an Alteryx Designer workflow based on success/failure of previous Output Data tools - essentially, if the workflow runs successfully and the previous tools output data, then the final tool will also execute and generate an output.

If the workflow does not complete successfully and the previous Output Data tools do not output data, then the final Output Data tool should not execute. I would like to keep this process within a single workflow, if possible.

The following two approaches make use of batch macros, however I need to determine the best way to adapt them to fit my use case.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Controlling-the-Order-of-Operations...

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Dynamically-enabling-disabling-work...

For the first approach (Order of Operations), a potential adaptation I'd implement would be to set each container as a batch macro, run them in succession, and the final output would be contingent upon successful execution of all batch macros; I am not sure if this would be best practice.

In the example in the second approach (Dynamically Enabling/Disabling Workflow Sections), the condition has to do with the input data itself and not success/failure of previous workflow output; is there a way to enable/disable conditional output?

Please see attached screenshot of a simplified representation of my workflow, which contains four containers that need to run successfully in order for the final output to execute as well. Please advise if this is possible, much appreciated.

[apathetichell]

Why would the first output data tool not work - that's kind of key to understanding the next steps.

[anthonyzhao]

We schedule and run our Alteryx workflows through Alteryx Server, via Windows Server remote desktop; on occasion, a workflow may not run successfully due to failure connecting with SQL Server, where a lot of our input data come from, e.g. the remote host forcibly closes the existing connection. Another possible cause of failure would be if we make a name or permissions change within our input/output source and do not update the corresponding tool queries in our Alteryx workflow before it runs.

The first set of output data tools are normally expected to work, it is when they unexpectedly do not that we also do not want the final output data tool to execute and update a different external table.

[ArtApa]

Hi @anthonyzhao - You may use a Detour tool. Here is an example: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/New-output-file-every-month/m-p/944470...

In this example the Workflow checks if a file exists (success of a previous Output) then it appends the file, if it does not (failure of a previous Output) then it creates a new file.

The other option would be leveraging Events, but I think that the one above may satisfy your requirements.

[apathetichell]

Assuming the potentially failing output creates a file with a specific naming convention - I'd use a directory tool to search for that file and if it does exist don't trigger a container and if it does exist trigger a container to create it. the containers would be inside of a macro. This is a fairly complex workflow any way that you slice it.

[anthonyzhao]

@ArtApa @apathetichell update on this issue, we have created a .bat file that gets called by the Alteryx workflow as an Event upon successful completion. We were able to test the command successfully, however when I added the new Event to our Alteryx workflow and ran the workflow, we get the following error. It appears that the workflow runs successfully (the output tables are updated), however the Alteryx workflow itself returns this error and the job is marked as a failure.

The error code is 1: The operation completed successfully. (0) – does this indicate that somehow, the successful run of the workflow and subsequent calling of the file is being read as an error by Alteryx, or that something in the .bat file is being read as an error? The script contains just one line: insert into Database.dbo.Table(Column) values (dateadd(hh,-2,getdate())); I’m not sure if, for instance, an exit code should be added (see links below)?

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/quot-Error-running-Event-1-The-exte...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/To-run-the-bat-file/td-p/327780

Appreciate the continued assistance.

[gabrielvilella]

Well, if the bat file is doing what it's supposed to do, you can do the workaround from the first link you posted. 

[anthonyzhao]

That makes sense, would the exit code simply be added to the bat file script like below, or would it go e.g. into Command Arguments in the Edit Event window?

insert into Database.dbo.Table(Column) values (dateadd(hh,-2,getdate()))

if %ERRORLEVEL% == 2 (
exit /b 2
)
if %ERRORLEVEL% == 1 (
exit /b 0
)
else (
exit /b 0
)

[anthonyzhao]

An update on this topic - I have revised the command to the following to specify the server host (required) and include error reporting -b:

sqlcmd -S “serverhostname” -q “insert into Database.dbo.Table(Column) values (dateadd(hh,-2,getdate()))” -b

It appears that I cannot use the "exit" keyword when working with SQL Server, so the above workaround code involving "exit /b 2 ..." does not work here.

I still receive the same error message as before about an error code of 1 that the operation completed successfully (0). I changed the Run As user to ensure access to the database and permission to insert the value, but this did not resolve this error.

Thus, I have opened a separate support case for this issue. Appreciate everyone's help here, thank you all.