Excel Output, Overwrite (Drop) Sheet with 0 records

[Scott_Snowman]

I have a workflow that produces two outputs: a Task List and an Exception List. Both are Excel files.

Both output datastreams are output to static Excel files via the "Overwrite (Drop)" functionality.

The intent is for users to generate the task list via the Gallery, and then review the exception list. If there are exceptions on the list, they need to adjust their input files and re-run the workflow. The goal is to generate 0 exceptions and then start assigning the tasks on the task list output file.

However if there are no records in the "Exception" datastream, the output doesn't overwrite. The Exception List will remain with whatever records existed in the file before the workflow was run. This seems consistent with other reported Community topics where a record count of 0 won't overwrite other file types such as .yxdb

So two questions:

1. Is it possible to force an overwrite if there are 0 records sent to the Output tool for the Exception List?
2. If not, is there a way to add a row of all 0 values if and only if the record count is 0 for exceptions? Right now I am using a Union tool to add the line of all 0s regardless as an 'end of file' marker and therefore forcing an overwrite but that doesn't seem elegant. I'm also interested in the 'if and only if' functionality for broader purposes.

Thank you!

[danilang · Accepted solution]

Hi @Scott_Snowman

Your method of adding the line of total exceptions at the end will work if you change it to an active count of the number of exceptions.  With this you'll be able to 

1. Add a record that provides value to your users, i.e. the number of exceptions in their data

2. Provide a confirmation that the process actually completed when the number exceptions is zero

Elegance is in the eye of workflow creator

Dan

[DavidP]

You can use the Count Records tool like this and append it to your dataset. That ensures that even if there are zero records, one record will be created to overwrite the sheet. The value for your empty columns will be null.

If your columns are numeric, a Data Cleanse tool can be used to change the nulls to zeros.

Hope this helps.

[DavidP]

Sorry, one more thing: If you don't want the Count column in your output, can can add a dynamic select tool with formula [Name]!='Count'.

Sample workflow attached.

[Scott_Snowman]

Thanks @danilang and @DavidP for the input.

I'm marking @danilang 's suggestion as a solution to the overwrite issue but @DavidP 's would work as well, depending on whether I wanted to indicate the "0ness" with an extra row or an extra column.

I'd still love to know if there's a way to achieve the "if and only if" functionality... coming from a programming background there are lots of points in my workflows where I'd like to do A in one case depending on the incoming data and B in another. I've found a number of workarounds depending on the scenario but nothing that is simple enough to adopt as a best practice.