Retrieving specific value from a sharepoint list

[stefbenham]

Hello, 

I'm trying to set up a flow which follows those steps : 

1. When a file is created in a Sharepoint folder the flow triggers
2. A list named Approvers is read by this flow with the "get items" statement and looks for the record containing a specific value
3. Two columns values of this list are returned to two variables (QA_mail and MGT_Mail) using the first(body(‘Get_Items’)?[‘QA Email’]) expression
4. Those variables are used in the "start and Wait for approval" step in the "Affected to" field, which fails with the following error: "InvalidApprovalCreateRequestAssignedToNoValidUsers"

The "Affected to" field allows UPN, person and emails types which is the one used in my flow. Any idea on what could be wrong ?

Thanks in advance.

Stéphane

[Expiscornovus]

Hi @stefbenham,

I think you approach is fine Can you share a couple of screenshots of your flow setup? This makes troubleshooting a bit easier.

Below btw is an example of a setup which I would use.

1. Add a When a file is created in a folder action

2. Add a Get file metadata action. Use the x-ms-file-id field from the trigger action

triggerOutputs()?['headers/x-ms-file-id']

 

3. Add a Get file properties action. Use the itemid field of the get file metadata action

outputs('Get_file_metadata')?['body/ItemId']

4. Add a Get items action. Use a filter query to retrieve the correct list item from the Approvers list.

In this example I am matching the title of the approvers list to a Approval Category choice column in the source library 

Title eq '@{outputs('Get_file_properties')?['body/ApprovalCategory/Value']}'

Below is my example approverslist btw.

5. Add a start and wait for approval action. Use the following expression for the mail addresses for the Assigned To field.

concat(outputs('Get_items')?['body/value']?[0]?['QA_Mail'], ';', outputs('Get_items')?['body/value']?[0]?['MGT_Mail'])

Hope this helps a bit?

[stefbenham]

Hi,

I wish I could have inserted a few screenshots. They were visible while composing the post, but disappeared when posted yesterday. My flow is the following one : 

When a file is dropped in a specific Sharepoint folder, in order to make him routed to the right person, I need to check who is the approver in the "Approvers" sharepoint list through the get items directive

I filter on the ID field. Each ID values are unique. 

Once I've got the values, each value initializes a variable

And last, I need to fill the "Affected to" field of the approval with this variable to direct the approval to the right person.

The flow fails in this step with the following message

Hope this clarifies my flow. It's definitely clearer with screenshots !

Thanks for your help

[OliverR-82]

What's the output from the QA Mail Variable action? Is that a valid e-mail address?

[Expiscornovus]

Hi @stefbenham,

It looks like your QA column is of data type Person or Group. In that case you can use the QA Email field like you are trying to do.

However, in an expression the syntax for that field is a bit different. It is QA/Email instead of QA Email. Notice the syntax of the tooltip in the screenshot below.

Can you try this expression instead?

outputs('Get_items')?['body/value']?[0]?['QA/Email']

[stefbenham]

How could I check the result ? Is there a way to display the result of this variable ?

[stefbenham]

I tried this expression modified with the right name of the field, but still the same error...

[Expiscornovus]

Hi @stefbenham,

You should be able to see the value of that field in the last flow run. Expand the get items action and look at the outputs of it. Should be something similar like below.

[stefbenham]

I don't have this presentation at all. When I download the output, I get a block of text which ends by

"body":{"value":[]}} 

Do you use a plugin to get this block readable in an easier manner ?

Looks like nothing has been returned...

Seems that the way to address and read the values through GetItems is not the right one. Any ideas ?

Thank again for your help and expertise !

[OliverR-82]

Hi @stefbenham ,

If the output of the Get Items action is like you say:

"body":{"value":[]}} 

 then it would seem to me that the action is not returning any results. This can be because there are no items in the list, or if you have configured a filter for that action that is causing it to not retrieve anything.

If you use FireFox, clicking on the 'download output' link displays the output in a nice and structured way as shown in @Expiscornovus ' screenshot automatically. If you use another browser, there are probably extensions that do the same thing.

If you click on the QA Mail Variable action in the run history, you should see the value that it returns. If the Get Items action does not return anything, then the output of the QA Mail Variable will probably be empty as well.

So, first step is to check if any items are actually being retrieved by the Get Items action.

[Expiscornovus]

Hi @stefbenham & @OliverR-82,

If I would have to guess I would say the internal name of the ID field is probably something different?

Because by default Microsoft also provides a ID field which stores integers and not strings (in your screenshot it shows the word Test).

Can you check the internal name of your ID column and use that in your filter query of the Get Items action?

[stefbenham]

You're right ! The field name has nothing to do with ID...

I now have the correct data, and the correct fields passed to the variables, but the flow is still failing on the approval step. 

Is there a way to display the content of the variable to check if this could be the issue ?

[Expiscornovus]

Hi @stefbenham,

If your set variables actions don't fail you should be able to see the content in the outputs section of your flow history.

[stefbenham]

Here is what I got. In the output section, I should have the output value instead of the expression right ? 

I want to pass in this variable the email of the person listed in the SP Approvers list. The get items works well now, but I can't get the value to be stored in the variable.

I don't understand where the issue is...

[Expiscornovus · Accepted solution]

Hi @stefbenham,

It looks like you pasted the expression as regular text string in your set variable action.

Can you make sure that you paste in the expression editor. Press the fx button first before you paste the expression code.

[stefbenham]

That was the issue ! Thanks a lot to @Expiscornovus and @OliverR-82 for helping me in that flow. 
I'm a newbie in this powerful platofrm. 

Kind regards

Stéphane