Help with Approval Workflow

Options
WinaHath
WinaHath ✭✭✭✭
edited 06/26/23 in Formulas and Functions

I have been trying to figure out how to efficiently create the approval workflow described in the attached spreadsheet. I have hit walls so many times, I am now confused and my brain is fried. Can anyone help?


Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    Hi @WinaHath

    Your Excel file is hard to comprehend.

    Could you provide "IF-THEN" type information as the following so we can help you with the workflow?

    Approver Selection

    1. If the total cost is less than $2999.99:
      1. Request approval from "Approver 1".
      2. Then, If the department is "DEPT SP":
        1. Request approval from "SP Approver 1", as well.
    2. If the total cost is greater than $2999.99:
      1. Request approval from "Approver 2".
      2. Then, If the department is "DEPT SP":
        1. Request approval from "SP Approver 2", as well.
    3. If the total cost is greater than $4999.99:
      1. Request approval from "Approver 3".
      2. Then If the department is "DEPT SP":
        1. Request approval from "SP Approver 3", as well.

    When a request is approved or denied, 

    1. If approved:
      1. Alert the requester.
      2. Generate a form.
      3. Close the process.
    2. If denied:
      1. Alert the assigned person.
      2. Close the process.


  • WinaHath
    WinaHath ✭✭✭✭
    edited 06/27/23
    Options

    When a request is approved or denied, 

    1. If approved:
      1. Alert the requester.
      2. Generate a form.
      3. Close the process.
    2. If denied:
      1. Alert the assigned person.
      2. Close the process.

    The above will apply for every track when the next approver is blank. SP is Special Projects so this will only be in play if Dept SP is not blank and SP Approver 1 is not blank. I have a formula that collects the approver name from a reference sheet and outputs to the main sheet for each department. So, if the next approver name column is blank or empty, then close the process and notify the requester if approved or denied and generate the approval form. I just don't know how to connect each track. Also, is it a condition or a condition path? where do I place a condition? before an action or after or both?

    Thank you! I hope it makes more sense to you than it does me. I've lost a lot of sleep over this! 😕


    Wina

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    Hi @WinaHath

    I created a demo sheet with workflows and a dashboard to show relevant contents, as explaining the workflow in English is challenging.

    Please take a look at the dashboard and images in the folder link.

    https://drive.google.com/drive/folders/1yGjb8es-FIBQfTOIKapUqiaFTFZnagSw?usp=sharing

    The essence of the workflow is as follows. (Here, Approver# stands for Approver 1, Approver 2, etc.)

    When Approver# Approved

    IF SP Approver# is blank: 

    IF Approver#+1 is not blank:

    Request approval to Approver#+1

    Else:

    Change cell value, Final Approval to Approver# approved.

    Else

    Request approval to SP Approver#

    When SP Approver# Approved

    IF Approver#+1 is not blank: 

    Request approval to Approver#+1

    Else:

    Change cell value, Final Approval to Approver# approved.

    Please get in touch with me with the following form if you need a copy of the sheets to check the workflow and formulas.

    https://app.smartsheet.com/b/form/8af18bf7138d421d834bc38f67027e14

  • WinaHath
    WinaHath ✭✭✭✭
    Options

    @jmiyazaki@cloudsmart.jp

    Ohayo! this will have multiple triggers though - can it not flow from one approver to the other without having to submit again to next approver?

    My boss wants to have just one workflow with all approvers. Is that possible?

    Thank you for all your effort! I will also try this route and show my boss.

  • WinaHath
    WinaHath ✭✭✭✭
    Options

    Please disregard my previous comment. I started to build the workflow using the example you sent but I ran into a problem. When I select the SP Approver in the Conditions, and I need to Request an Approval to names in a cell, I am not able to see the SP Approvers.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    Hi @WinaHath

    In the approval workflow "send to contacts in a cell" setting, you need to set the column properties of the cell as "Contact List."

    https://community.smartsheet.com/discussion/71614/approval-workflow-send-to-contacts-in-a-cell

    If you want to show the Approver's first and last names instead of email in the contact list, the best way is to register them at user management.

    https://community.smartsheet.com/discussion/comment/381234#Comment_381234


    BTW, I have shared the sheets with you. Please check if the problem still persists using the shared sheets.😀

  • WinaHath
    WinaHath ✭✭✭✭
    Options

    There have been changes to the approval workflow. I am hoping you can help me.

    if approver 1 is not blank - send to approver 1 -

    -if total cost is less than $2999.99 and approver 2 is not blank - send to approver 2 - if approver 3 is blank then approver 2 Final Approved

    but if total cost is greater than $2999.99 and approver 2 is not blank then send to VP - if approved; then Approver 2 Final approved

    or if total cost is greater than $4999.99 and approver 2 is not blank then send to CFO - if approved; then Approver 2 Final approved

    -if approver 3 is not blank and total cost is less than $2999.99 then send to approver 3 - if approver 4 is blank then approver 3 final approved

    but if total cost is greater than $2999.99 and approver 3 is not blank then send to VP - if approved; then Approver 3 Final approved

    or if total cost is greater than $4999.99 and approver 3 is not blank then send to CFO - if approved; then Approver 2 Final approved

    -if approver 4 is not blank and total cost is less than $2999.99 then send to approver 4 - if approver 5 is blank or No approver 5, then approver 4 final approved

    but if total cost is greater than $2999.99 and approver 4 is not blank then send to VP - if approved; then Approver 4 Final approved

    or if total cost is greater than $4999.99 and approver 4 is not blank then send to CFO - if approved; then Approver 4 Final approved

    -if approver 5 is not blank and total cost is less than $2999.99 then send to approver 5 - if NO approver 6 then approver 5 final approved

    but if total cost is greater than $2999.99 and approver 5 is not blank then send to VP - if approved; then Approver 5 Final approved

    or if total cost is greater than $4999.99 and approver 5 is not blank then send to CFO - if approved; then Approver 5 Final approved

    they now separated the special projects and it will be same workflow as above.


    THANK YOU SO MUCH!! I appreciate it very much!

    Wina

  • WinaHath
    WinaHath ✭✭✭✭
    edited 07/07/23
    Options

    @jmyzk_cloudsmart_jp

    PLEASE DISREGARD PREVIOUS WORKFLOW!!!

    1) request to approver 1 - if approver 2 is blank,, then approver 1 final approved

    if approver 2 is not blank - send to approver 2 -

    2) if total cost is less than $2999.99 and approver 2 is not blank - send to approver 2 - if approver 3 is blank then approver 2 Final Approved

    but if total cost is greater than $2999.99 and approver 2 has approval of >$2999.99 then ALSO send to VP - if approved; then Approver 2 (VP) Final approved

    OR if total cost is greater than $4999.99 and approver 2 has approval of >$4999.99 then ALSO send to CFO - if approved; then Approver 2 (CFO) Final approved

    3) if approver 3 is not blank and total cost is less than $2999.99 then send to approver 3 - if approver 4 is blank then approver 3 final approved

    but if total cost is greater than $2999.99 and approver 3 has approval of >$2999.99 then ALSO send to VP - if approved; then Approver 3 (VP) Final approved

    OR if total cost is greater than $4999.99 and approver 3 has approval of >$4999.99 then ALSO send to CFO - if approved; then Approver 3 (CFO) Final approved

    4) if approver 4 is not blank and total cost is less than $2999.99 then send to approver 4 - if NO approver 5 then approver 4 final approved

    but if total cost is greater than $2999.99 and approver 4 has approval of >$2999.99 then ALSO send to VP - if approved; then Approver 4 (VP) Final approved

    or if total cost is greater than $4999.99 and approver 4 is not blank then send to CFO - if approved; then Approver 4 (CFO) Final approved

    5) if approver 5 is not blank and total cost is less than $2999.99 then send to approver 5 - if NO approver 6 then approver 5 final approved

    but if total cost is greater than $2999.99 and approver 5 is not blank then send to VP - if approved; then Approver 5 Final approved

    or if total cost is greater than $4999.99 and approver 5 is not blank then send to CFO - if approved; then Approver 5 Final approved

    they now separated the special projects and it will be same workflow as above.


    Summary:

    Total Cost of Less than $2999.99 required to be approved by approvers 1 - 5

    only if Total Cost is Greater than $2999.99 they must be approved by VP

    only if Total Cost is Greater than $4999.99 they must be approved by CFO

    Note: VP or CFO may be approver 2,3,4 or 5 in the sequence


    THANK YOU SO MUCH!! I appreciate it very much!

    Wina

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    Hi @WinaHath

    Can we summarize the decision tree as follows?😅

    1. The approval process progresses sequentially from Approver 1 to the highest available approver (Approver 5) as long as each step's approver exists. (We have already accomplished this step.)
    2. When the process reaches the highest available approver, the Total Cost condition is evaluated.
    3. If the Total Cost is greater than or equal to $3000, the request is furtherescalated to VP.
    4. If the Total Cost is greater than or equal to $5000, the request is further escalated to the CFO for approval.

    If the above assumption is correct;

    Add Total Cost, VP, CFO, VP, and CFO Approval columns.

    Modify 6. Final Approval workflow

    • After Condition(1), Where final approval is not one of Declined
    • Add a condition Where Total Cost is less than 3000
      • and Otherwise and add an action Request approval to VP
    • Then,
    • Add a condition Where Total Cost is less than 5000
      • and branch Otherwise and add an action Request approval to CFO
    • Add appropriate Alert some action after each approval request to VP and CFO.
  • WinaHath
    WinaHath ✭✭✭✭
    edited 07/11/23
    Options

    Thank you so much!

    However, the assumption is incorrect.

    1. The approval process progresses sequentially from Approver 1 to the next available approver as long as each step's approver exists and if less than 3000 or less than 5000.
    2. However, If the Total Cost is greater than 3000 then request is escalated to VP after the last approver in the sequence whose approval limit is less than 3000; (i.e., Approver 1 - 3 +VP; otherwise Approvers 1-3 only)
    3. if the Total Cost is greater than 5000 then request is escalated to CFO (i.e., Approvers 1-4 + CFO; otherwise Approvers 1-4 only)

    The highest available approver depends on the approver track for that Dept.

    See samples below: (Approvers 1 & 2 + VP if greater than 3000, otherwise Approvers 1 and 2 only)

    (Approver 1 + VP if greater than 3000, otherwise Approver 1only)

    Approvers 1 - 5 required - no escalation

    Approver 1 only if greater than 3000, Approver 2+ CFO if greater than 5000, otherwise Approver 2 only


    Some Departments have 1 approver, some have 2 or up to 5.

    I am so sorry for making this so complicated. This is why I needed help. I couldn't figure out how to correctly sequence the workflow>

    Please accept my sincere apologies for any inconvenience.


    Wina

  • WinaHath
    WinaHath ✭✭✭✭
    Options

    We can separate the approval for depts and projects since they will now run independently.

    thank you again!

    Wina🙏

  • WinaHath
    WinaHath ✭✭✭✭
    Options

    @Paul Newcome

    Good Morning, Paul!

    Thank you for checking my formula. I don't get an error message but I don't get data either. I know there's data in the cell and it is a contact list. I just get a blank.

    Wina

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options
  • WinaHath
    WinaHath ✭✭✭✭
    Options

    @Paul Newcome

    Ooppps!! Sorry! wrong discussion thread! that was the response to your question regarding my formula to collect the email from another sheet:

    output data from another reference sheet — Smartsheet Community


    @jmyzk_cloudsmart_jp is helping me with my issue on the approval workflow in this thread.


    thank you!

    Wina

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!