cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
evaristo
Helper I
Helper I

Fill in a column with YES/NO based on the latest date of each field value

Hi,

I need to fill in a YES/NO column (LATEST REPORT) based on the Report Date of a specific field value:

image SP 1.jpg

In order to get a view as the one below:

 

image SP 2.jpg

 

The objective is to know what are the latest report dates by Project.

 

How can I do it in SharePoint? According what I have read this is only possible with Power Automate, but so far I wasn't able to create the workflow...

Can you please help me with it?

Many thanks!

 
1 ACCEPTED SOLUTION

Accepted Solutions
v-yiwenxie-msft
Microsoft
Microsoft

Hi @evaristo 

 

The problem of getting several YES to the same project is maybe you didn't add '[0]' to the function:

outputs('Get_items_2')?['body/value'][0]

 

The [0] here is used to get the first item in an array. Without [0] in the function, the flow will update all the items for the same project as YES.

 

You don't need to add an condition here. Because 'Report Date' has already in a descending order. And the flow only need to get the first item(whose report date is the latest date) in an array.

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

6 REPLIES 6
v-yiwenxie-msft
Microsoft
Microsoft

Hi @evaristo 

 

In my understanding, you want to update ‘Latest Report’ as YES for the latest report date of the same project.

Yes, this is possible with Power Automate.

 

First, the flow will get to know how many different projects are there in your sharepoint list and will get the project name of each kind. All of the project names will be saved in one array. The array is named ‘project array’.

 

Then, in an ’apply to each’ using the outputs from the ‘project array’, use ‘Get items’ for sharepoint using ‘filter query’ to get all rows for one kind of project each time and ‘order by’ to sort ‘Report Date’ in descending order.

 

The whole flow is for your reference:

2.18-11.PNG

 

The detailed step for ‘initialize variable’ and ‘append to array variable’:

2.18-12.PNG

 

2.18-13.PNG

The ‘Value’ in ‘Append to array variable 2’:

{"project array":@{variables('project')}}

 

The detailed step for ‘compose’:

2.18-14.PNG

“Compose”:

last(variables('project array'))

 

“Compose 2”:

outputs('Compose')?['project array']

 

“Compose 3”:

union(outputs('Compose_2'),outputs('Compose_2'))

 

Up till now, the union() get all projects name for each kind.

Next, the ‘Apply to each 2’ is used to get specific item and update it each time:

2.18-15.PNG

 

The detailed steps for ‘Get items 2’ and ‘Compose 4’:

'Get items 2' is used to get items each time whose ‘Project’ is the same and whose ‘Report Date’ is in descending order.

'Compose 4' is used to get the specific item's value.

2.18-16.PNG

The function used above:

outputs('Get_items_2')?['body/value'][0]

 

The detailed step for ‘Update item’:

2.18-17.PNG

The function used above:

outputs('Compose_4')['ID']

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

evaristo
Helper I
Helper I

Hello,

 

first of all thank you for your support and detailed explanation. It seems this will sort my problem. I decided to adapt the workflow so I will update the list whenever an item is created or modified. Hope it's ok.

 

evaristo_1-1613641835562.png

 

However when I run the workflow I am not able to put working successfully.  When I edit the workflow the Flow checker gives me this Warnings below:

 

evaristo_2-1613641881656.png

 

and to test it, when I modify an item list, the test goes well until the last step:

 
 
 
 
 
 
 
 

image.png

 

with the following error:

 

image.png

 

 

I already tried several combinations at the FILTER QUERY and ORDER BY parameters but I am not able to sort the problem:

 

image.png

 

Can you help me understand what am I doing wrong?

 

Cheers!

v-yiwenxie-msft
Microsoft
Microsoft

Hi @evaristo 

 

That's because even though your column display name is 'Project', but actually Power Automate can only recognize its field name which is in the column's URL as below:

2.19-13.PNG

 

Mine here is 'gtfc', so I should replace 'Project' with 'gtfc' in Filter Query. But I would like to make it clear for you, so I change it as 'Project'.

 

If the problem still exists, please feel free to let me know.

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

evaristo
Helper I
Helper I

Hi,

 

ok, understood. Always at the URL field name.

 

After some running's I get several YES to the same project. However the condition is to have only one YES by project for the latest date.

To solve it I tried to create a Condition at the end to Update Item where the condition would be update with YES the Latest Value and the remaining with NO. 

 

What shall I paste as expression for the Condition? I was thinking to to a MAX(Latest Date) is equal to TRUE, but it didn't work. 😞

 

evaristo_1-1613731301709.png

 

Is this the best approach to solve my problem?

 

 

v-yiwenxie-msft
Microsoft
Microsoft

Hi @evaristo 

 

If you use the method I gave you, then it should only update only the row whose date is the latest for the same project as yes. 

If you use condition, then it's more complicate than 'desc' in 'order by'. And also, please make sure the field name in URL of your column 'Report Date' is the same as what your put in 'order by'.

 

You might miss something in your flow and when I made a test for the flow, it didn't show the error of 'an infinite trigger loop' for 'Update item'.  

 

If the problem still exist, could you show me the detailed screenshot of your flow and detailed error or your confusion?

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yiwenxie-msft
Microsoft
Microsoft

Hi @evaristo 

 

The problem of getting several YES to the same project is maybe you didn't add '[0]' to the function:

outputs('Get_items_2')?['body/value'][0]

 

The [0] here is used to get the first item in an array. Without [0] in the function, the flow will update all the items for the same project as YES.

 

You don't need to add an condition here. Because 'Report Date' has already in a descending order. And the flow only need to get the first item(whose report date is the latest date) in an array.

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (3,734)