Accounting for Zero Records on Email Output

[taliatest]

Hello, I have attached an image of the workflow that I am currently working on. As shown by the highlighted filter tools, I am using the results of each output to configure a Table and Report Text tool to use in an email output. I need to account for the possibility of each of the filter outputs returning no results with some sort of message in the table like "No Results Found". Does anyone know the best method for this type of logic? Thanks in advance!

[jrlindem]

Do you have version 2023.1 or later?  I would address this conditional behavior using Control Containers.  This would enable you to go one way if data was there, and another if no data exists.

Hope that helps,

-Jay

[Erin · Accepted solution]

Hi @taliatest! 

You could add a separate data steam for this by using a Count Records tool after the T anchors of the highlighted filter tools, and then use another Filter tool for when the count = 0, after which you could write your message with a report text. Then you would need to add these streams back to your data sets before your Layout tool. That's just one way. 

Or, another way would be to still use a Count Records and then Append that to your data stream (have the Count Records go into the Target anchor vs the Source anchor so that the tool won't error since Count Records should always produce a count even if there's not anything coming through the source). Now that you have your count in your data, you could dynamically write a message with a formula tool (if count = 0 then "bla bla bla" else "yada yada" endif) and then you can call that field in the Report Text tool. Just make sure in the Basic Table tool that you select group by on the message you created with the formula tool so that you can use it in subsequent Report Text tools. 

[taliatest]

@Erin Thank you! That seemed to work. Do you know if there is a way to not have the email send if records are blank for all sections?

[Erin]

@taliatest  - Yep! There's a few ways to handle blank / empty rows. Let's talk about the pros/cons so you know which route is best for your situation. 

• Quickest way would be to add a filter tool in the Input and Clean Data box (or add to the one that's there). Filter a field for is not empty. Fun fact, the IsEmpty() function checks for both blank and nulls. The IsNull() function checks for nulls only. The downside here is that it's really only looking at one field - unless you add a bunch of AND clauses. 
• Next quickest would be the Cleanse tool which has an option to remove null rows. The pitfall here is that I believe it doesn't consider empty rows as null rows, and you would need it to be truly null for every single field of the data set, which sometimes isn't practical in real life. The cleanse tool is also a macro which some people prefer not to use because depending on your expected data set, it could bog down the workflow checking things that you don't even really need checked. Personally, I steer away from the cleanse tool. If you opt to use this, I would recommend a multi-field formula tool before hand that converts all empty fields to nulls (something like if isempty([__current field__]) then null() else [__current field__] endif
• Lastly, you could use a RecordID + Transpose + Filter + Crosstab - This route adds some tools but its practically full proof, which is why I like it. If you don't already have a unique ID, add a Record ID to your data. Then Transpose the data but make sure to select your Record ID or Unique ID as a Key Column. Next filter the Values for is not empty. Now use a Crosstab tool to flip your data back to the original format. You'll want to group by the Record ID or Unique ID, set the Name field as your column Headers and the Value field for your Values. I usually pick Concatenate for the aggregation. The 2024.2 version has a check box to Retain Special Characters in New Column Names which is nice. If your version doesn't have this, you might need a Dynamic Rename tool to clean up your field names. Replacechar() is your friend here. Your data types might get wonky (most likely will all be strings) so you can fix that with Autofields or a Select tool. The order of your columns will probably be alphabetical based on your column name. So that can be rearranged with a Select Tool as well. Like I said, this is a bit cumbersome and not for those trying to use the least amount of tools possible - but still practically full proof. Just depends on your use case and how well you trust the incoming data.

Also a word of caution - the email tool sends an email for every record that feeds into it. So 200 records going in means 200 emails going out. Likewise, 0 records in means 0 records out. I once accidentally spammed my team with 2000 emails or something ridiculous. Don't learn the hard way like I did! To be safe, you can add a test tool to count the number of records and error if it's more than however many you're expecting (especially useful if you're only expecting 1 email). Another option here that's more dynamic is a sample tool. First 1 row and group by all pertinent fields. Just a suggestion. 

[taliatest]

@Erin This is so helpful! Thank you so much!