Get Most Recent SharePoint List ID

[BS10]

Hi,

I have created a power app and I am trying to create an approval so when the form is submitted it can be approved/rejected. I am having a few issues trying to get the most recent SharePoint ID to display in the details box so the approver can see the information submitted. Could someone please advise on how to do this? 

Thanks in advance.

[BS10 · Accepted solution]

This is now solved, I used this post below which fixed it.

Solved: Get Most recent item from SharePoint List - Power Platform Community (microsoft.com)

[rzaneti]

Hi @BS10 ,

To answer this post, I'm using this List as example:

Note: for this example, the last inserted item has the ID 8.

In Power Automate, add a 'Get items' action, from SharePoint connector. Populate it with your List details and click in 'Show advanced options'. Some new input fields will be displayed. Fill the 'Oder by' with 'ID desc' (highlighted in yellow) and the 'Top Count' with 1 (highlighted in green):

This will order your List by ID number, but in descending order, and restrict the output for it first item, which is the highest (and most recent) ID number.

To access the ID of the retrieved item in any future action, use the following expression:  outputs('Get_items')?['body/value'][0]['ID']

With this expression, you are accessing the outputs from 'Get items' action (text in red). As it is an array, you need to access it first item (that's why we have the 'zero' in blue), and then you can access any property from your List record (in our case, we accessing the 'ID', in green).

I inserted this expression into a 'Compose' and here is the output:

If you are not comfortable with writing expressions, there is a section that quickly will walk you through it inthis article that I recently wrote: http://digitalmill.net/2023/07/31/using-dynamic-content-in-power-automate/ 

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/ 

[BS10]

Hi @rzaneti,

Thank you for you reply, that is working. 

I have a StartTime and EndTime column in my SharePoint list which displays the date and time in the column. At the moment I have the flow to automatically pick up the dynamic content from the SharePoint list, but it is displaying it like this below.

Could you please advise on how to make this appear as it does on the SharePoint list? For example this above on the SharePoint list is like this below (the time on the flow is an hour behind):

19/08/2023 07:00

19/08/2023 13:00 

Thanks in advance.

[rzaneti]

Hi @BS10 ,

Sorry for sending that answer after your reply: I was writing when you posted and didn't notice that you got a solution!

About your date formatting issue, you can solve it by using the formatDateTime() expression. To make it match to the format that you want, the complete expression may look like this:  formatDateTime([dynamic_content], 'dd/MM/yyyy hh:mm'), just replacing the red text for the dynamic content of your timestamp.

[BS10]

Hi @rzaneti,

Thank you for the fast response, I have tried this below but I get an error when I click 'OK' saying expression is invalid.

Thanks

[BS10]

Apologies I didn't add the code, here it is.

formatDateTime([outputs('Get_item')?['body/StartTime']], 'dd/MM/yyyy hh:mm')

[BS10]

Hi @rzaneti,

Apologies for all the posts but it is all sorted, thank you very much!

[BS10]

Hi @rzaneti,

Sorry just realised the hour is still 1 behind on the flow after using the formatdatetime expression?

[rzaneti]

Hi @BS10 ,

About the expression, we just need to remove the brackets surrounding the dynamic content. The expression below will replace this one.

About the -1h, it's probably happening because of the time zone. By default, Power Automate uses the UTC, but we can convert it with this expression:  convertTimeZone(utcNow(), 'UTC', 'Eastern Standard Time', 'dd/MM/yyyy hh:mm')

Replace the red text for the time zone that you need. The expression above will apply the same formatting of the formatDateTime() expression, that we used before. Also, there is a specific action in Power Automate to convert time zones, but honestly, in your case I think that it will be easier by just using the expression.

[BS10]

Hi @rzaneti,

Thank you, sorry just to confirm, where do I need to add the convert expression within the flow?

Thanks in advance.

[rzaneti]

Hi @BS10 ,

You can add it to the same action and field where you were first using the 'formatDateTime()' 🙂 As the convert expression already to the formatting, you no longer will need the 'formatDateTime()'.

[BS10]

Hi @rzaneti,

Thank you, I really appreciate it. Sorry the last issue...I replaced 'Eastern Standard Time' with 'British Summer Time' but it says the value provided for the time zone id 'British Summer Time' was not valid?

[BS10]

Hi @rzaneti,

By changing the expression to convertTimeZone, where should I place the dynamic content for the flow to pick up from? Sorry to be a pain - I have also found GMT Standard Time works. 

I have tried this below but I get an error when clicking 'OK' which says invalid expression.

convertTimeZone([outputs('Get_item')?['body/EndTime']]),(utcNow(),'UTC','GMT Standard Time')),'dd/MM/yyyy hh:mm')

[rzaneti]

Hi @BS10 ,

I ran some tests here. Try to use 'GMT Standard Time'.

[rzaneti]

Don't worry! It's my pleasure to help!

Here is your complete expression:

convertTimeZone(outputs('Get_item')?['body/EndTime'], 'UTC' , 'GMT Standard Time' ,'dd/MM/yyyy hh:mm')

[BS10]

Thank you very much, I really appreciate it. Everything looks good now. 

Thanks again for your time and patience. 😊

[rzaneti]

It's my pleasure! I'm happy to know that everything is working!!

Feel free to reach me if you need any additional help 🙂