Answered

Finding Last User Entries in SharePoint List

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I have a SharePoint list that basically is a history list of user submissions. One of the fields is the type of submission (kind of like a status). For each user, I need to find out what their latest submission status was.

User	Submission Type	Date submitted	Other Fields
Frank	A	11/1/2021	Info
Jeff	B	11/3/2021	Info
Frank	B	11/2/2021	Info
Jeff	A	11/5/2021	Info
Sue	C	11/7/2021	Info

Output I'm looking for: Frank = B, Jeff = A and Sue = C.

I thought I'd use an ODATA query to first just get the fields I need, and then go from there. I actually thought it would be best to start from the bottom of the list. Once a person's name was encountered, that was their final status. I would then have to ignore that person as I go up the list.

In the end, I need to produce a report, as both an HTML table in an email and also, if possible, an Excel sheet.

I've done a little with ODATA, tables and emails, but nothing like this. I'm not sure if it's even possible with Power Automate. Can it be done using internal arrays in the Flow, or might I need it to create extra SharePoint temp/working lists?

Thanks!

Categories:

General topics

I have the same question (0)

All responses (8)

Answers (1)

mahoneypat 1,720 on at

Like (0)

Report
Copy link

Link copied!

This would be much easier in Power BI. Have you considered using that instead?

Pat

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Unfortunately, we're not licensed for Power BI. 😞

I was thinking of maybe adding a "current status" field and using Power Automate to designate the last entry. When a new entry is added, "current status" would always be set to true, then a flow would kick off that would set current status to false for all entries for that user, other than the one that kicked off the flow. Maybe it could grab the ID of the added entry to be sure it excludes that one.

Then, the report would only include any entries where "current status" is true.

Was this reply helpful? Yes No
Verified answer

mahoneypat 1,720 on at

Like (2)

Report
Copy link

Link copied!

Here is one way to do it, assuming the one to keep for each person is the last one for that name (that was the case in your example data). I put your table an an input array (in json format) to simulate your get items step. Then here are the steps:

- Select step to get just the values of the Name field

- Compose step with Union of the select to itself (to get distinct list of names)

- Initialize two variables - one array to hold the final result, and one string to hold the current name while iterating in the Apply to each step

- ATE step that iterates over the distinct array of names

- for each name, set the variable to that name, filter the input array for name = that variable, and then append the last() of that filtered array to the array variable

final result has the desired 3 rows

Pat

Was this reply helpful? Yes No
mahoneypat 1,720 on at

Like (0)

Report
Copy link

Link copied!

@UkimanPA Did this approach work for you?

Pat

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Thank you, Pat! I am sorry for not responding sooner, but the initiative I was involved in which had that requirement was canceled, so I couldn't put more effort into it. I have another Power Automate/Apps project that may require something pretty similar. Since a SharePoint list can't be queried like a SQL or Dataverse table, I am intrigued with your example of reading the entire list into an array and they querying that array for results.

I am trying to avoid the extra licensing for Dataverse, which I believe requires a premium per-user license at about $12/mo per user. I am seeing if Power Apps can also read and entire SharePoint list into an array for querying. Please let me know if you have any experience or thoughts around this.

Again, sorry for the late reply!

Was this reply helpful? Yes No
Rick_black 186 on at

Like (0)

Report
Copy link

Link copied!

Hey @mahoneypat , I want to achieve the exact same. However, I don't fully understand your solution. Could you expound, please?.

Was this reply helpful? Yes No
mahoneypat 1,720 on at

Like (0)

Report
Copy link

Link copied!

@Rick_black I tried to put the expression in the comments throughout. Which parts would you like me to clarify further?

Pat

Was this reply helpful? Yes No
takolota1 4,978 Moderator on at

Like (0)

Report
Copy link

Link copied!

This template gives the largest / most recent or the smallest / oldest records for unique values in given columns. Kind of a Group By, then Max or Min.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Get-Distinct-Records/td-p/2191533

Was this reply helpful? Yes No