Automated update after an automated report has already sent

Options
kevans33
kevans33 ✭✭✭
edited 06/12/23 in Formulas and Functions

Hi Community,

I have set up some automations for my PM team where every Thursday, each one of them receives an update request to give a weekly status update on their projects. When they submit their updates, it populates to specific columns on a master Smartsheet depending on date of submission. This sheet stores all historical updates as well that they have ever submitted, sorted by week.

Then, I have a report built that only pulls in the updates from that week. That report is set to email our managers every Friday afternoon. Today, someone missed submitting their update so their section turned up blank on the report. Is there an automation or formula to set up this report to auto send again if this person completes the update request AFTER the report automation runs? So, if there are any updates or changes made after the automation runs, could it email the group again?

Thank you!

Answers

  • Cory Page
    Cory Page ✭✭✭✭✭
    Options

    Good day, Reports don't have very good automation but if you dont mind creating a new column in your data sheet I might have an idea that might work.

    Option 1: First we need to add a filter to the report that hides anything that's blank like the example you mentioned, if you ask them to submit a date or comment then that makes it easier. Once they submit the update then the row will show on the report otherwise that row shouldn't show AKA report is blank. Next add a new column in your raw data sheet and create an automation that at the same time you trigger the report the automation updates the Column (Automation: "Recording a date") with the timestamp so you know what has been sent already and what hasn't. Now any submissions after your report is triggered will not have a time stamp and can be added to a new report or populated on your existing report. This way you can either schedule two exports the second one being a backup and if you filter it correctly it will not re-send the first export, it will only send Rows added after the first was triggered. Unfortunately if the second report is blank it will still trigger which stinks but its a really simple solution.

    --An alternative to a second report would be to setup an automation directly in your sheet, after X number of days or daily you could say if the time stamp column is blank and the user has submitted data (again what happened in your example) "Alert someone" and then at the same time "Record a data".

    Sorry that was a bit long and probably sounds more confusing than need be but I use the same thing at my work and it works great. Basically you just need to have two flags, one indicating the new data is populated and should be on the report and two that the new data has been sent. It sounds like you already have the first part you just don't have the second part, which would only be needed if you didn't want to send duplicate rows of course.

    Finally, if your recipients don't actually need an excel or PDF and your reports row count is always less than 100 using the automation in the actual Sheet "Alert someone" is much more powerful. I really do wish Smartsheets would provide an export to excel or PDF in the Sheet automation's as life would be way easier for sure.


    hopefully this is helpful, let me know if you have any questions..

  • kevans33
    kevans33 ✭✭✭
    Options

    Thank you! Based on your suggestions, I added a flag column where the PM clicks the flag if they are submitting their update after 3p on Fridays, which will then trigger the update to be sent to managers!

    I thought about filtering out the blank columns, but was afraid that management would question if the rows are missing completely off the report....but that's still something I should ask the team about and if that's what they would prefer. It's a good idea, especially if they know the data is there, it's just blank.

    Thanks again, this was so helpful and got my brain going on some good solutions!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!