Copy a row of data in SharePoint list automatically when a cell in the row equals a specified value

Like (0) Share

Report

Posted on 7 Jul 2022 13:28:50 by TheJM15

Hi All,

I am currently working on a tracker that monitors a status of a given task (in my case, its a submission of a report). The reports that I aim to track are of different cadence. I would like to automate the creation of a placeholder for the "next" report, once the "current" has been recorded as "submitted".

For example, let's take the table below:

If the user fills in "ReportSubmissionDate", the "ReportStatus" changes to "Completed":

What I would like to achieve is that once the status turns into "Completed", a new record shows up with the next period (depending of what cadence we have, it will be either next month or next quarter or next year):

This is only a simplified version of the table, there are many other columns that store data specific for each submission (like "comments", "feedbacks" etc.) - I do not want those to be copied into the new record for the new reporting period, just a basic set of data, like "ReportName", "Cadence" and "Period"

In addition to that, I would like this solution to work both when the user enters the data via user form (created in Power Apps - already in use) and "Edit in grid mode".

Has anyone worked on a similar problem? I would appreciate any feedback - tips, suggestions are more than welcome.

Thanks!

Categories:

Dataflows

All responses (2)

Answers (1)

vhn92 3 on 19 Jul 2022 at 12:12:51

Like (0)

Report

Re: Copy a row of data in SharePoint list automatically when a cell in the row equals a specified value

Hi Charlie,

Thank you for your comprehensive guideline, I really appreciate it. I used most of your tips (had to modify the "new reporting period" formula and create one additional variable), but other that I followed your instructions and they were very useful.

Thanks and Best Regards,
Pawel
Verified answer

v-yujincui-msft on 12 Jul 2022 at 09:24:16

Like (2)

Report
Re: Copy a row of data in SharePoint list automatically when a cell in the row equals a specified value

Hi @TheJM15 ,

If you want this solution to work well both when the user enters data via the Form and "edit in grid mode", maybe you could consider creating a Flow.

I have made a simple test for your reference.

1. Here is my SharePoint list.

To prevent Flow from getting stuck in an infinite loop, I created an extra status column.

UPDATE: ProcessStatus column is not required.

2. Here is my Flow.

@not(equals(triggerBody()?['ReportSubmissionDate'], null))
@not(equals(triggerBody()?['ReportStatus/Value'], 'Completed'))
@not(equals(triggerBody()?['ProcessStatus/Value'], 'Processed'))
@not(equals(triggerBody()?['Created'], triggerBody()?['Modified']))
concat(substring(triggerOutputs()?['body/ReportPeriod'],0,add(length(triggerOutputs()?['body/ReportPeriod']),-1)),add(int(substring(triggerOutputs()?['body/ReportPeriod'],add(length(triggerOutputs()?['body/ReportPeriod']),-1),1)),1))

Result Screenshot:

In addition, here are some links for your reference.

Trigger Conditions in Power Automate - EnjoySharePoint

A simple way to create a trigger condition in Power Automate (tomriha.com)

Best Regards,
Charlie Choi