Flow: when a date changes in one SP list, copy the list item to a second SP list and email the reviewer

[kmcoop]

In my first SP list (Document Backlog), I have an ASSIGNEE COMPLETION DATE column. When that date equals today, I want two things to happen:

1. Copy list item to SP list 2 (Document Review Tracker)

2. Email Reviewer 

If anyone has an idea how to set up this flow, I would appreciate it.

[jitheshk]

Not sure why you need 2 lists, since it can be done using one list itself by adding few more columns.

As per your requirement you may follow the below process:
1) Create a scheduled flow to trigger Everyday

2) Get Items on SP list (Document Backlog) for list of items with "ASSIGNEE COMPLETION DATE column" eq UTCnow() format it as per your time zone.

(Note: There should be a way to indicate the items which are already moved to review stage so that your flow don't have to iterate through those items. For instance you can have a Status column which will be marked as ReviewStage when you have moved this item to review stage)

3) Apply to Each of Get Items and then "Create Item" to add records to Reviewer SP List.

4) Send out the mail

[kmcoop]

I appreciate your suggestion:

The Document Backlog is for the addition of document links that are due for annual review. In this SP list, the documents are assigned to the technical editor who implement the changes. It is when the technical editor is finished implementing the changes that the review/approval process begins (SP list 2 Document Review Tracker). I like the idea of the ReviewStage column but would that be added to SP list 1 or SP list 2 or both? Did you have a chance to review my screen shots?

Document Backlog

Document Review Tracker

[jitheshk]

I still feel that you can manage the entire process with one list. This will reduce the duplication of common fields in two lists. You can have a separate column for technical editor (email ID) and Reviewer (email ID). This way you can also save few API calls in flow as you will not have to create the row for reviewer in Document review tracker. As soon as the Status for the Document changes to "Reviewer" Approval flow can be triggered to Reviewer eMail ID.

Coming back to your queries (if you still feel there is a need for multiple lists). Regarding "Status" column to indicate the document process status like "ReviewStage" depending upon your business requirement can be maintained in both the List. 

[kmcoop]

I took your advice and created one list.

Now what I want to do is:

When an item is created, automatically update the Status to Not Assigned and email the administrator to choose an Assignee. 

Here is my flow that simply times out ( I didn't even try Send an Email since I couldn't get this part of the flow working).