how to define who the approval is from multiples

paldous
paldous
edited 12/09/19 in Formulas and Functions

I have migrated our Engineering change to smartsheets which is working fine.  I now want to progress this by adding approval requests.

We can get changes approved by 3 different people, so I have created an approval request which gets sent out once a box is checked to declare the change is ready for approval.  At present once the request is approved, the cell gets populated with "approved" - but I don't know which one of the three people has done it (apart from viewing cell history).   Instead of writing "approved" could the cell be populated with the approvers initials?

I have tried individual approval requests which all default to submitted

I can add an "IF" statement for a single approval to generate initials or write "TBC" using the formula: =IF([PA APR]5 = "Approved", "PA", "tbc")

But I cant get it to look at two more columns (see image - I know its wrong, its to give the column titles etc).

My end goal is to take approval initials and send them to a google word doc with mail merge.

Any help would be greatly appreciated.

SMARTSHEET APPROVAL PROBLEM.PNG

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    Unfortunately, it's not possible at the moment, but it's a great idea!

    Please submit an Enhancement Request when you have a moment.

    A possible workaround could be to have three Approval Requests (one for each approver) and then depending on who answers the status would change to Approved by CM or similar.

    Would that work?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • paldous
    paldous
    edited 01/08/19

    Andrée - thanks for responding so fast!

    "A possible workaround could be to have three Approval Requests (one for each approver) and then depending on who answers the status would change to Approved by CM or similar."

    This is what I'm trying to achieve, but can't get the logic statement to work.  I'm trying to get the "approved_by" to show PA, CM or ST depending on who gives approval, or "TBC" if a false statement is required.

    Can this be done?

  • Andrée - that would be perfect!  but how do I get the logic statement to work?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Great!

    You would use the same column for all approvers and then you can use an IF statement to get the initials of the one that approved. 

    You'll have to add the different states that you'd want to use and then you'll need to change the Approved save status for each Approver in the advanced settings of the Approval request.

    Hope that helps!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I use an Update request.

     

    Change your Approved By column to a contact type column.

     

    You can then enter the three different people's contact info and include that column in the update request. Whoever checks the box for "Approved" or "Denied" will simply select their name from the dropdown list (similar to how a form works).

     

    With it being a contact type column, you can also use that column in other alerts and actions to send further update request (if they denied but didn't say why, etc.)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To use this...

     

    In Row 1, enter the initials in the corresponding approval column.

     

    Then in your Approved-by column you can use something along the lines of

     

    ="Approved by: " + JOIN(COLLECT($[First Approval Column]$1:$[Last Approval Column]$1, [First Approval Column]@row:[Last Approval Column]@row, "Approved"), " - ")

     

    What this does is pull the initials from row 1 for whatever row the formula is in where "Approved" is the status. If only CM approved, it would show

     

    Approved by: CM

     

    If CM and ST approved

     

    Approved by: CM - ST

     

    Etc...........

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!