Looking for an example how to retrieve a value from an item in a SharePoint list

[hwielen]

I'm new to Power Automate and I want to create a workflow but can not find the exact information I need.

I have a simple list in SharePoint with a few columns. One column contains unique ID's.

What I want to do from the workflow is select an item from the list with a certain ID and then retrieve the value from another column of that item in a variable in the workflow for further processing.

The workflow is triggered by an incoming email that contains the ID in the bodytext. I already figured out how to get this ID from the email into a variable.

I know there are actions like GetItems and GetItem but i can't figure out how to use them.
I already searched this community but can not find the exact details I need.

Can someone please post an example for the steps / actions I need and the code for the query?

[abm]

Hi @hwielen 

To retrieve a record from the list with a certain ID you need to set your Filter Query. Under the filter query set the SharePoint columnname, operator and mapped value. If its string then add single quotes around it. 

Next add a foreach action step to iterate the values. For single or multiple records you need a foreach record to retrieve the record(s).

[burgett94]

Hello @hwielen 

You can use the Get item action and put the variable in the Id field as below:

Alternatively, you could use Get Items then use the Filter Query to the below to only retrieve the item you want - if you are only wanting one Item to return you can use Top Count to only retrieve a single item.

ID eq 'outputs('Compose')'

Hope this helps 🙂

Thanks

Bradley

[hwielen]

Hi abm,
Thank you for your reply.

The column I'm filtering on always returns only one item/record because the values in this column are unique.

Do I still have to use a foreach action?

And how does this foreach action look like? How can I retrieve the value from another column?

How does the expression look like that you use in the 'Limit Columns by view' field?

Also how can I check if no item is found?

[hwielen]

Hi burgett94,

What is the function of the action 'Compose' with 'Inputs' is 1? Why do I need that?

I guess I need to put the text string to search for in the field ID in your first example? 

But how does the query know in which column I want to search?

Also it's not clear to me how the result that I get back looks like.

What extra step do I need to retrieve the value from the desired second column in the list for the returned item?

[RobElliott]

@hwielen with the Get items, even if you are only returning 1 item via the filter query you will still have the apply to each. That is expected and quite normal.

For follow-on actions you can select the other columns from your list from the dynamic content box on the right.

In the filter query you enter your column name and what value you are expecting back, as in the following examples:

Rob

Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks. 

[burgett94]

Hi @hwielen 

The Compose was me simulating the ID number, so you mentioned you had managed to extract the ID number from the email - so it is where I have put my compose output is where you will put your output from the email.

Once you have that, all the columns within the default view within SharePoint will be available for you to use in whatever format you would like.

Looking at my screenshot below, you can change the view the action looks at by choosing from the dropdown highlighted yellow.

I have used another compose after to show that I could pull the Amount Paid from my list for item ID 1. Using this, I could use another action instead of the Compose like Send email or post message on teams to notify other users or Create item to create another item in another list using details from ID 1.

[hwielen]

Hi burgett94,

Now it becomes a bit clearer to me.

But I think there's some misunderstanding about the ID i'm using.

The ID is nothing more than a string value that I entered by hand for every item in a custom column I added to the list and that's unique for every item. It's not a record ID that's generated by SharePoint.

So I guess that I have to do some search first with the string value in the correct column to get back the item/record i'm looking for.

Should I combine the actions 'GetItems' (for the search) and 'GetItem' (for retrieve the item columns) to get what I need?

[abm]

Hi @hwielen & @burgett94 

If you planning to use @burgett94  suggestion (Get Item) you need to Id of the SharePoint item record. Then you don't need to use the for each loop to iterate the values. You can directly map the dynamic variables in subsequent steps.

For Get Items it may return one or more so you need to use For Each (Control) action step to iterate the values. 

Thanks

[abm]

Hi @hwielen 

It's all depends on your flow logic. If you have the Id then you can use Get Item. If you don't have the Id then use Get Items and use filter query to extract the values.

If you need further help post a screenshot of your SharePoint list. Then explain what filter you are trying to do and rest of the logic. 

Thanks

[burgett94]

Hello @hwielen 

You can have it set so that you select the item from the SharePoint List to trigger the flow for that item - would that work better rather than an email?

This way it will have the ID in the trigger which you can use for the Get Item action which means you can use any columns in your other actions.

[hwielen]

Your're confirming what I just tought.

Can I use 'GetItems' to retrieve this ID and than use 'GetItem' to retrieve the Item and it's columns?

Or is there a better way to do a search for this ID?

[hwielen]

Unfortunately that is not an option.

I have to use a trigger for incoming email messages.

These contain Project numbers (the ID's) I have to lookup.

[burgett94]

@hwielen 

Ok, so you will want to use the Get Items filter query to say [columnprojectid] eq '[outputfromtrigger]' - below is an example of one of mine:

So to explain, the Title is referencing the column "Title" in my SharePoint List then eq means equals to then 'Outputs' is the output of a compose holding a value provided from Power Apps.

More details of the Filter Query can be found here: https://docs.microsoft.com/en-us/previous-versions/dynamicsnav-2016/hh169248(v=nav.90)?redirectedfro...

I have it set to Top 1 so that it only provide one output which still forces an Apply to each when I do a Update Item but at least this way I know it will only do it the once.

Hope this helps

Thanks

Bradley

[hwielen]

And I can use the output of this 'GetItems' example as input for the ID-field of the 'GetItem' action?

[burgett94]

Hello @hwielen 

With the Get items action that will do everything you need as it will pull all the required columns, so you won't need to use the Get item action after.

Thanks

Bradley

[hwielen]

Hi guys,

Thanks for your help. I managed to retrieve the list value I needed.

See the screenshot for the steps I created (the workflow is in Dutch).

I still have a question:

What's the syntax for the value in the field 'Limit columns by view'

Grtz,

Henry

[RobElliott]

There's no syntax, you should just be able to select the view. Occasionally I have found it has taken several saves of the flow before it's found the different views.

Rob
Los Gallardos

[abm]

Go to your SharePoint List or library

Create the view and specify the columns you want.

Then go to flow and create the SharePoint connector. You should be able to see this view under that

If you have created the view after the connection then you may not see the view straight away.

[hwielen · Accepted solution]

Ahaa! Thank you! 🙂