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

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

Best Answer

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Answer ✓

    @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.

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

Answers

  • Colleen Patterson
    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 

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • Zachary Hall
    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!

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • Andre Hester
    Andre Hester ✭✭
    edited 01/11/24

    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
    Zachary Hall ✭✭✭✭✭✭
    Answer ✓

    @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.

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • 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.