Move Rows with Auto-Number System Column - retaining an auto-number value

katie.dickinson

Hey everyone,

I am working on my first automation to move a row from Sheet A or Sheet B to Sheet C when the Approval Status is Declined. Sheet A is a list of a specific type of project and Sheet B is a list of a different type of project. Sheet C will essentially be a Backlog project list.

Both Sheets A and B have an Auto-Number system column which serves as the Project ID but they utilize different alpha prefixes to denote the different type of projects so it's easy to tell by the Project ID the type of project. In this case, the projects from Sheet A use "DB-XXX" and Sheet B uses "LITE-XXX"

During the approval process in both sheets A and B, the approver has the option to "Backlog" (decline) the project. At that point, I would like to move the declined row from Sheet A or B to Sheet C - our Backlog list. Eventually, I would like a process that takes place in Sheet C where we review the Backlog and determine whether we should move it back to the Active projects (sheet A or B). I want to preserve the Project ID though.

So for example. A project is submitted to Sheet A. It is assigned Project ID DB-001 by the system. The approver decides to decline it. The row is moved from Sheet A to Sheet C. At the end of the month, the team reviews Sheet C, deciding to keep/kill projects on Sheet C. The team decides that Project ID DB-001 should be approved now. The automation then moves the row Project ID DB-001 back to Sheet A. Will the row retain its DB-001 Project ID? Or will it be assigned a new Project ID?

Paul Newcome

You will want to test it, but I feel like you are going to run into a problem with trying to get the automation to populate a system generated column. You may need to create a new column that records the "Original Project ID" using a basic cell reference to the auto-number column. When the Move Row Automation grabs the roe, it will transfer static data which means that the original project ID should be captured for the row.

katie.dickinson

Thanks for your thoughts, Paul. What you described is what I was worried about and before I went through the effort of building it out, I wanted to check with others first. Since you suggested testing it, I decided to invest the time.

Good news - it saved the original value from the System Generated column and, better yet, it kept it when I moved the row back.

This is how I have it set up from start to finish in case anyone else has a similar need.

1. Sheet A and Sheet B act as the intake sheet and the active project sheet. When a project is proposed through the related form, it is dropped into Sheet A or B (they have separate forms).
2. When the form is submitted, the row is populated and a Project ID (auto-number column) is created for the line item. In Sheet A, the Project ID is formatted with a prefix of "DB-XXX" and Sheet B is formatted with a prefix of "LITE-XXX". For my use, this denotes the project type without needing a category and works because the projects have different intake requirements.
3. Once the row is populated and the Project ID is generated, an approval process kicks off. The Approver has a choice of Approving for Work or Sending to Backlog. Approving for Work does nothing except change the Approval status to Approved. Sending to Backlog kicks off a "Move Rows" automation.
4. When a row's Approval status is set to "Backlogged" (caused by a declined approval) for either Sheet A or Sheet B, the system moves the row to Sheet C, my Project Backlog sheet.
5. The Project Backlog sheet has a system generated auto-number column without any pre-determined format for the auto-number. The column is named Project ID just like on Sheet A and Sheet B.
6. The Move Rows automation described in #4 here, successfully places the Project ID from either Sheet A or Sheet B into the Project ID field in Sheet C. It doesn't create a second auto-number when the row is added to Sheet C.
7. I also have an automation in Sheet C (the Project Backlog sheet) to move projects off the Backlog into the original sheets they came from. When the row's Approval status is set to "Approved", it kicks off a "Move Rows" automation.
8. The Move Rows automation considers the Project contents (Automation Condition "when Project ID contains") to determine whether to send the row to Sheet A or Sheet B. In my case, this works because the prefix in the Project ID is different - if Project ID contains DB then go to Sheet A; if Project ID contains LITE then go to Sheet B.
9. Upon being returned to its original sheet, the Project ID continues to persist even if other projects have been created and approved after it.

Paul Newcome

You will want to test it, but I feel like you are going to run into a problem with trying to get the automation to populate a system generated column. You may need to create a new column that records the "Original Project ID" using a basic cell reference to the auto-number column. When the Move Row Automation grabs the roe, it will transfer static data which means that the original project ID should be captured for the row.

katie.dickinson

Thanks for your thoughts, Paul. What you described is what I was worried about and before I went through the effort of building it out, I wanted to check with others first. Since you suggested testing it, I decided to invest the time.

Good news - it saved the original value from the System Generated column and, better yet, it kept it when I moved the row back.

This is how I have it set up from start to finish in case anyone else has a similar need.

1. Sheet A and Sheet B act as the intake sheet and the active project sheet. When a project is proposed through the related form, it is dropped into Sheet A or B (they have separate forms).
2. When the form is submitted, the row is populated and a Project ID (auto-number column) is created for the line item. In Sheet A, the Project ID is formatted with a prefix of "DB-XXX" and Sheet B is formatted with a prefix of "LITE-XXX". For my use, this denotes the project type without needing a category and works because the projects have different intake requirements.
3. Once the row is populated and the Project ID is generated, an approval process kicks off. The Approver has a choice of Approving for Work or Sending to Backlog. Approving for Work does nothing except change the Approval status to Approved. Sending to Backlog kicks off a "Move Rows" automation.
4. When a row's Approval status is set to "Backlogged" (caused by a declined approval) for either Sheet A or Sheet B, the system moves the row to Sheet C, my Project Backlog sheet.
5. The Project Backlog sheet has a system generated auto-number column without any pre-determined format for the auto-number. The column is named Project ID just like on Sheet A and Sheet B.
6. The Move Rows automation described in #4 here, successfully places the Project ID from either Sheet A or Sheet B into the Project ID field in Sheet C. It doesn't create a second auto-number when the row is added to Sheet C.
7. I also have an automation in Sheet C (the Project Backlog sheet) to move projects off the Backlog into the original sheets they came from. When the row's Approval status is set to "Approved", it kicks off a "Move Rows" automation.
8. The Move Rows automation considers the Project contents (Automation Condition "when Project ID contains") to determine whether to send the row to Sheet A or Sheet B. In my case, this works because the prefix in the Project ID is different - if Project ID contains DB then go to Sheet A; if Project ID contains LITE then go to Sheet B.
9. Upon being returned to its original sheet, the Project ID continues to persist even if other projects have been created and approved after it.

Paul Newcome

Happy to help. 👍️