Action Item Generation

brianw
brianw ✭✭✭
edited 11/08/21 in Smartsheet Basics

I am trying to create a form with a specific set of questions that depending on how they are answered would automatically generate a standard action item, which is captured on another sheet for tracking.

For example, if a person completes a form and answers yes to the question "Will a new chemical be used?". An action item is automatically generated on a separate action log that states "Complete new chemical approval form". This action item would need to be linked back to the form entry number and assigned to an individual with a due date.

Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭

    Hey Brian,

    Sounds like what you're wanting to do is doable, so here's how I'd accomplish it, below. For the sake of keeping things straight, I'm going to call the sheet that gets the Form responses "Form Sheet" and the Action Item sheet "Action Item" sheet.

    I'll offer you two paths:

    1. Option 1 stays entirely within Smartsheet (but has some limitations)
    2. Option 2 connects to a service like Power Automate or Zapier and doesn't have any limitations, other than cost.

    Option 1: Automating this entirely within Smartsheet

    • Pros: Doesn't include any extra tools or costs
    • Cons: Will copy over unneeded data from your "Form" sheet. Copies over every column from the Form sheet.

    First, if you don't have an Auto-Number column setup yet on your "Form" sheet, create one. This column will be utilized to reference the "Form" sheet to the "Action Item" sheet. Think of it like a unique ID per row (but don't call it a "Row ID", because if you ever use the Smartsheet API now or in the future, you'll just confuse yourself and others). Name the column something like "SubmitID", with a format something like "Form_0001".

    Within the "Form" sheet, go to "Automation">"Create a Workflow".

    Under "Trigger", select, "When rows are added". Then for the "Any field" value, insert your "Will a new chemical be used?" column, and add "yes" to the field for the change. Run workflow when triggered.

    Under "Select an action", scroll all the way to the bottom and select Copy rows. Select your "Action Item" sheet. This will copy over the entire row, which will include ALL data from that row. So if there's any columns that are not on your Action Item sheet, Smartsheet will automatically create them. That's the downfall of doing this within Smartsheet; you may not want to have the same data from the Form sheet as on your Action Items sheet. You can always hide the columns, but they will always exist.

    ----

    Option 2: Use an Automation Service

    This one is for anyone who wants to customize how the data comes across.

    Using a service like Power Automate, you can set up an automation flow (with ZERO coding) that'll do this for you.

    If I was using Zapier or similar, I'd set up something like this:

    Trigger:

    • Smartsheet - When a row is created (select the "Form" sheet)
    • Filter by column "Will a new chemical be used" and value "Yes"

    Actions:

    • Smartsheet - Insert a new row (select the "Action Item" sheet)
      • Using the data from the "trigger", select the appropriate fields to keep on the Action Item sheet

    ----

    Let me know if either of those would work for you!

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • brianw
    brianw ✭✭✭

    Thanks Brett

    Your first option to do everything within smartsheets doesn't accomplish what I am trying to do. It does not populate the task of "Complete new chemical approval form". It seems to just copy over the row from the entry sheet.

    I do have access to Power Automate but am not familiar with the use at all. Is it relatively intuitive?