You’re offline. This is a read only version of the page.
Hello,
I would like to set up a quarterly recurring follow up using an excel list, and i need it to filter out certain things in 2 different columns. For example, a few clients do not need this follow up email so i need to filter out their business names, (BusinessName column in my table) aswell as if someone has already sent something in (Status column in my table) i dont want to send them another email following up with them. I have tried using "Filter Query" within "List rows present in a table" but i can only figure out how to make it work with one filter. example (BusinessName eq 'Company Name')
Is there a way I can list multiple business names in the filter query along with filtering by the Status column or is there a different way I need to do this?
Thanks
You can add an Update a row action within the Apply to each, so it updates each of the items. Note that this will require you to have a column in your Excel Table that contains unique values (key to uniquely identify each row).
Using the example from before, I've added an Update a row action using the Business column as the unique identifier. The expression used to get the business name for each row is:
Share
Report
All responses (
Answers (items('Apply_to_each')?['Business']
@grantjenkins if i wanted to take it one step further and have the flow update the Status column to "Completed" for the people it emailed during the flow, how would I do that?
Since you can only have a single query within List rows present in a table, I would filter on the Status, then use Filter array to continue the filtering on business.
I've used the following Excel Table for this example.
See full flow below. I'll go into each of the actions.
List rows present in a table has the following expression that will exclude any rows where the status is "Completed"
Status ne 'Completed'
Initialize variable creates an Array variable called businessList that contains all the businesses I want to exclude. Not sure where you would have your list of businesses.
Filter array takes in the value from List rows present in a table and has a condition to check if the array of businesses does not contain the current business.
The output in this example would be:
[
{
"@odata.etag": "",
"ItemInternalId": "e93983f1-fb02-4067-b795-696c0d70cc34",
"Business": "Business 001",
"Email": "email@business001.com",
"Status": "In Progress"
},
{
"@odata.etag": "",
"ItemInternalId": "25c6b5ec-4c8d-4d22-a88d-9ea09d64787b",
"Business": "Business 006",
"Email": "email@business006.com",
"Status": "In Progress"
},
{
"@odata.etag": "",
"ItemInternalId": "0389cdf6-bf7e-4127-bef1-b2a32f328cb5",
"Business": "Business 008",
"Email": "email@business008.com",
"Status": "In Progress"
}
]
Apply to each can then iterate over each of the items returned from the Filter array and send an email. The expression used to get the email for each business would be:
items('Apply_to_each')?['Email']
Hi @kaylee ,
Is there a way I can list multiple business names in the filter query along with filtering by the Status column or is there a different way I need to do this?
I'm afraid not , it is a known limitation:
The List rows present in table action supports basic filtering and sorting:
- Supports the following filter functions: eq, ne, contains, startswith, endswith.
- Only 1 filter function can be applied on a column.
- Only 1 column can be used for sorting.
I think this link will help you a lot:
https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/?WT.mc_id=BA-MVP-5003630#known-issues-and-limitations-with-actions
In addition , you could use the 'filter array' action to achieve your needs.
Best Regards
Bof
#1
WarrenBelz
146,660
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,999
Most Valuable Professional
