Let’s say you have an Alteryx workflow that filters a large data set to find recently created orders which then does some basic analysis and emails the results out in a file to end users. Simple, right? Well, what happens when there aren’t any recent records? One would suppose that there would be no email, right?
In this imagined scenario you may have end-users calling you to ask why they didn’t get an email. They may presume that instead of there simply being no records to receive that there was some sort of error, and that can be one last thing you don’t need to manage in your day.
In just a few steps, you can add functionality to your Alteryx workflows that will instead email a file that tells the user “No records were returned”. I’ll show you how.
First, because in my imaginary scenario we’re looking to email out a list of orders from the current month, I’ll add a formula tool that will find the current month and year, as well as the month and year based on the order date. The sample data I’m using only contains values through December of 2022, so finding the current month’s orders will return zero results. Because I also want to test what happens when there are results, I’m also going to add a formula that creates a month/year value for January 2019.
I’m later going to use some of these fields to populate a dynamic input tool so that we can toggle back and forth between the current month and January 2019 to see the difference in results, but for now, let’s presume we’re going to filter out records based on the current month. ([Current_MonthYear] = [Order_MonthYear])
After we filter, I’m going to add a count. A formula after the count tool will add a “no records found” message if the count is zero and makes the field NULL if there are records. After this point, we no longer need the count field, so we’ll remove it with a dynamic select tool, then join the potential message back to the rows of data with an Append tool.
Did you know that you can remove columns that are completely NULL with a Data Cleansing tool? It’s a pretty sweet way to remove the message field if our report has records and there is no message or leave it in if there is.
Once we’re through that step, we can get rid of the fields I added previously to find the month and year of our various dates so that they don’t end up in the output.
So, let’s get into testing this. I decided it’d be fun to add a Drop Down interface tool so that we could toggle between our current month/year (zero results) and fake month/year (January 2019 with 84 results). When I created my current month and fake month fields, you may have noticed that I created them as “V_String” fields. Because they’re the only fields of that type in my workflow, I can then use that formula tool as the input for my interface tool and select that it uses only V_String types.
Then, in the action tool I can tell Alteryx to remove part of the formula I’m using in the Filter tool with whatever’s selected in the Drop Down.
If you don’t already have the Interface Designer window open, you can access it by clicking “View” “Interface Designer”. I have mine pinned to the left side of my Designer pane, and you’ll need it if you want to test the workflow at this step.
On the left side, clicking the “magic wand” icon will bring you to the Test View. Select one of the two options from the Drop Down and click “Open Debug”. This will open another Alteryx workflow that is set up to use the value you selected in the Interface tool. There, you can click “Run” and view the results. Selecting the Current_MonthYear option will return no records and therefore only a field with the message “The file produced no output.” Selecting Fake_MonthYear will return the 84 records found from January 2019 and therefore no message field.
From there, you can add an Email tool and have it send the results via email, or add an Output Data tool and write the results to a file. Please, feel free to download my sample workflow file and Excel data source and truly see behind the scenes. Hope this has been useful!
.jpg)
