Send email with multiple list item link and get items filter with lookup value

[Eric_Cheng]

I have two questions on microsolt flow.

1. I have a list which has a column status. I want to get all the list item record where status is 'expired' and send only one email which include all the list item link in the email body. For example, The list has 10 records with status expired and I want to send only one email where the email body include all the 10 list item link. I know how to send a email by a record using "apply to each" only but I don't know how to append all the list item link into a single email. Is it possible to do that?

2. For the same list in question 1, the column status is a lookup column.  I want to get all the list item record where status is 'expired'  using the action Get Items with the Filter Query "status eq 'expired'". However, it fails to get the records. I think I should filter the status's value instead of status. I would like to know how to filter the lookup value using Filter Query?

(P.S. I don't want to get all the record and use apply to each and condition to filter it as it may cause performance problem when It has large amount of list data).

Regards,

Eric

[v-xida-msft]

Hi @Eric_Cheng,

For your first question, I think it is possible to do that in Micorosft Flow. I have made a test on my side and please take a try with the following workaround:

• Add a proper trigger, here I use Flow Button trigger.
• Add a "Get items" action, specify Site Address and List Name.
• Add a "Filter array" action, From set to output of "Get items" action, within Condition box, left input box set to Status dynamic content of "Get items" action, right input box set to expired. Within middle drop down, choose is equal to.
• Add a "Select" action, From set to output of "Filter array" action. Within Map entry, add a entry, key set to Link to item and value set to following formula:

• item()?['{Link}']

  Add a "Create HTML table" action, From set to output of "Select" action.
• Add a "Send an email" action, Body set to output of "Create HTML table" action, Is HTML field set to Yes.

Note: The Status column is Single line of text type column within my SharePoint list.

Image reference:

The flow works successfully as below:

For you second question, using OData query to filter items based on Lookup column of SharePoint list is not supported in Microsoft Flow, if you would like this feature to be added in Microsoft Flow, please submit an idea to Flow Ideas Forum:

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

In condition, you could consider take a try to filter items based on Lookup column of SharePoint list using "Filter array" action. I have made a test on my side and please take a try with the following workaround:

The flow works successfully as below:

Best regards,

Kris

[EricCheng]

Hi Kris,

Thank you for your reply. For the first question, I have tried with your solution and it seems work fine but the link send to the email is not hyperlink. (it is hyperlink in your figure) How to change the link into hyperlink?

 

Regards,
Eric

[v-xida-msft]

Hi @EricCheng,

Please check if have set Is HTML field to Yes within your "Send an email" aciton, if not, please set Is HTML field set to Yes.

Best regards,

Kris

[EricCheng]

Hi Kris,

Sorry for late reply. I have already set the field Is HTML to yes. However, the hyperlink still not exists.

a

Regards,

Eric

[Bill_Hand]

Any chance you were able to get this working?

Thanks