Is there a way to send alert if a form isn't submitted everyday by a certain time?

Andre Hester

My company requires a vehicle inspection everyday before vehicle usage. Every vehicle is assigned a truck number. Is there a way to send an alert for all the truck numbers that don't show up on the smartsheet for that day, by a certain time? I want an alert to go to fleet manager by 8am Mon-Fri with the truck numbers that haven't submitted a vehicle inspection.

Thanks

Zachary Hall

@Andre Hester

Sorry for the confusion. The Invalid Column Value error means that the data that is being returned (in this case "True") does not match the type of column. Can you right click the column header and go to "Edit Column Properties" to see if this is set up as a checkbox column and if not change it to one?

Setting up the automation is a lot to try to explain if you aren't familiar. I'd recommend starting with this help article: https://help.smartsheet.com/learning-track/level-2-intermediate-users/automation. Feel free to message me directly if you would like to get some additional help.

Colleen Patterson

@Andre Hester

You may need a helper sheet. We can use a MAX(COLLECT to find the MAX date (most recent date) based on criteria: Be sure to use a date column here.

=MAX(COLLECT({Date Column}, {Truck Column}, [Truck #]@row))

You would then be able to have a formula to check a box if that date = today.

From there, you would be able to set an automation for an alert that is sent to the manager based on the check box being checked.

See: COLLECT Function / MAX Function 

Zachary Hall

It would definitely be possible to do this. Assuming the inspections are entered on one sheet you would want the most recent inspections at the top of the form. Then I would create a second sheet with the inventory of truck numbers and have a column that checks to see if an inspection was entered today. Then all you would need is a daily automation that sends out the communication about the vehicles that were not inspected.

The formula that you would need to use to look up the inspection would be something like:

• =IFERROR(IF(INDEX({Inspection Date}, MATCH([Truck Number]@row, {Truck Number}, 0))=TODAY(),True),"")

Where {Inspection Date} is the column that logs the date inspected on the submission form and {Truck Number} is the column that logs the truck number on the submission form.

I hope this helps!

Andre Hester

Thanks guys. Can you dumb it down. I'm HR. This is my first time with Smartsheets, so please forgive me. Smaller company and wearing many hats. Also @Zachary Hall, what would daily automation look like? Also @Zachary Hall, I used your formula and the inspections that haven't been submitted show blank, but the inspections that have been submitted show invalid. Is that normal?

Zachary Hall

@Andre Hester

Sorry for the confusion. The Invalid Column Value error means that the data that is being returned (in this case "True") does not match the type of column. Can you right click the column header and go to "Edit Column Properties" to see if this is set up as a checkbox column and if not change it to one?

Setting up the automation is a lot to try to explain if you aren't familiar. I'd recommend starting with this help article: https://help.smartsheet.com/learning-track/level-2-intermediate-users/automation. Feel free to message me directly if you would like to get some additional help.

Andre Hester

Thanks @Zachary Hall. After some messing around yesterday, I got it to work. Your formula was pretty much plug and play. I used this automation below and now it's easy on the eyes with changing the verification column to a checkbox. Thanks for all the support.