How to use Decline in an Approval Workflow to change other cell values

Tim Sheehan

I am quite frustrated trying to figure out how to use the Approval Workflow. I am working on a team task template which as an "assigned to" field and a "approval by" field. There is also a dropdown column for Status, with options "Not Started, In Progress, or Submitted". A separate column is used for Approval Status (Approved or Rejected). My goal is that when the person assigned the task thinks it is complete, they change the "Status" field to "Submitted". The "approval by" person then receives a notification. If happy, they Approve, and the row locks (that part is easy). If unhappy, they add a comment, and reject the task, and an update request goes back to the assignee.

My problem is that I want the "status" task to change back to "in progress" when "approval status" is set to Rejected by the workflow. That is so that the Workflow will trigger again when the assignee is ready to resubmit the task for approval. (Otherwise there is no way to re-trigger the approval workflow). I cannot find any way to accomplish this. Similarly, once approval status goes to Approved it would be great for Status to change to "Complete" instead of just "submitted".

I considered instead combining the Status and Approval Status in one but that leaves open the possibility that the assignee could simply change the status to 'approved' without the Approver actually reviewing it.

Am I missing something? Maybe there is a better way to accomplish my goal. I am very surprised that the Workflow features do not enable a cell to be set to a certain value by the workflows.

Many thanks!

David Joyeuse

I think you have a problem with the way you want to use the submission of the row. If it's done by the person submitting the row, that kills any formula within the status column.

Basically, you'd want the status column to be locked, preventing anyone from doing anything within it (and thus overriding the process).

I'd rather use a 4th column, like a "completed task" column (which is a boolean type), to trigger the submitted option, then go with some NESTED IF.

e.g.: (within a 4 options dropdown column: Not Started / In Progress / Submitted / Complete)

Status column:

=IF(ISBLANK([assigned to]@row),"Not Started", IF([completed task]@row=1,"Submitted",IF([Approval Status]@row="Approved","Complete","In Progress"),"In Progress"))

Then add an automated workflow for the approval based on the submitted option.

Now, I don't have an easy solution for now regarding the automation of resubmitting the approval request. At some point, you'll have to either uncheck the % done in the cell, or the rejected status to allow trigger of this row once again. I will have to think about that.