Unanswered

Excel→Planner: Date filter not working or I get infinite loop. How to fix?

(0) Share

Report

Posted on by kwillich

Hello, I manage licensing for several pharmacies at my company. I use Planner to manage all of the renewal applications for all our pharmacies in all states and several pharmacists in about 20 or so states. I am trying to use PA to create a daily recurring lookup that will get the list items that have an expiration date ≤ 120 days from now and create a task tile. I have reviewed several MVP videos on the topic and none of them are 1:1 what I am trying to do so I end up running into one of a few issues.

ALL items in the list are added regardless of the value of the Exp Date column data and the flow stops at the end of the list
The apply to each cyles through all rows multiple times (presumably infinite times)
There is an error at the Conditional branch and the flow stops

My excel columns are laid out like so (the column "LicenseProgressStatus_CODE" is usually hidden

State	Abbrev	License Number	Expiration date	Submission of Application	LicenseProgressStatus_CODE	Status	LicenseType	License Image Stored?	NCPDP UPLOAD?
Alaska	AK	####	09/28/24	MKT's app #226498	2	Pending	C	N	N
Guam	GU	####	09/30/24	DRAFTED APP 8/29	1	In Process	C	N	N

There is conditional formating applied and Column G "Status" contains this formula (I don't know if this is negatively impacting the Flow)

=IF(ISBLANK([@[LicenseProgressStatus_CODE]]),"",IF(AND([@[Expiration date]]<(TODAY()+120),OR([@[LicenseProgressStatus_CODE]]=3,[@[LicenseProgressStatus_CODE]]=4)),"Renewal",XLOOKUP([@[LicenseProgressStatus_CODE]],'DATA LISTS'!$B$7:$B$14,'DATA LISTS'!$C$7:$C$14)))

Here is how the flow is laid out currently (9/4/24)

CurrentDate = convertFromUtc(utcNow(),'Central Standard Time','MM/dd/yyyy')

DateDifference = if(equals(outputs('CurrentDate'),outputs('ExpDate')),0,int(split(dateDifference(outputs('ExpDate'),outputs('CurrentDate')),'.')[0]))

DueDate = addDays(startOfMonth(outputs('ExpDate'),'MM/dd/yyyy'),-1)

The rest of the fields are unused, and the "No" branch is blank.

Exp = {formatDateTime(outputs('ExpDate'),'MM/dd/yy')}

Start Date Time = @addDays(utcNow(),30)

Main Question

How can I get the flow to return only row details where the Exp Date is ≤ 120 days, without duplicting rows or looping infinitely?

Thanks!

Categories:

Business Process Flows

All responses (5)

Answers (0)

creativeopinion 9,703 on at

Like (0)

Report

Excel→Planner: Date filter not working or I get infinite loop. How to fix?

Unfortunately there are limitations when defining a Filter Query in the List Rows present in a table action. To troubleshoot the Filter Array action, I'd recommend putting your expression for the date into a Compose action to verify the output. Also I would recommend that you format the dates from excel and from your expression so they are in the same format. (eg. yyyy-MM-dd).
kwillich 14 on at

Like (0)

Report
Excel→Planner: Date filter not working or I get infinite loop. How to fix?

I went back and updated a couple things and tried a few more things.

I renamed my column from "Expiration date" to "Expiration_Date" to make sure that there was not an issue there.

I attempted to run the Flow on a Page with fewer total entries with the Filter Query clause Expiration_Date lt 'fx: addDays(utcNow(),120)' and got an error stating that it can only be compared to a single filter.

From there I tried to run a Filter Array like this, with the filter being the same fx: addDays(utcNow(),120) → Should return 6 rows. but returns all 39

Thoughts on a next step to try?

Thanks!
creativeopinion 9,703 on at

Like (1)

Report

Excel→Planner: Date filter not working or I get infinite loop. How to fix?

Thanks for subscribing!

Can you show a screenshot of your Filter Query in the List Rows Present in a Table action? As mentioned in my initial post—in order to define a Filter Query in this action, the column name in your Excel Table cannot have any spaces.
kwillich 14 on at

Like (0)

Report

Excel→Planner: Date filter not working or I get infinite loop. How to fix?

WOW, how wonderful to be able to discuss this with @creativeopinion. Your video was the one that I had used as a guide through my initial build.

I had tested at every stage and was satisfied with the progress as I went. I used the count row Compose function along with the Top Count (per your guidance 😊) to save from cycling 90 rows each test. I had attempted to draft a Filter Query but was not able to get a successful run. I even watched your video "Are you using the Microsoft Power Automate Filter Array Action wrong?" and wasn't able to get a succesful run with that and some research into the syntax. The videos lacked enough diversity in the examples to cover scenarios that were comparable to the one that I was attempting.

If you could help with that as a first step, I can add that and test, then see if additional direction is needed.

Thanks!!
creativeopinion 9,703 on at

Like (1)

Report

Excel→Planner: Date filter not working or I get infinite loop. How to fix?

I would recommend starting a new flow and ensure you are testing at each stage. It's a lot easier to troubleshoot a flow as you build it rather than to troubleshoot it when you've "completed" your flow.

In this case, you need to first work on filtering out the Excel rows that you want to loop through.

You stated in your original post:

ALL items in the list are added regardless of the value of the Exp Date column data and the flow stops at the end of the list

Filter Excel Rows

I can't tell from the screenshot you've shared, have you defined a filter query? If you could click on show advanced options and upload a screenshot of your List Rows Present in a Table action, it would help to provide more insight.

The List Rows present in a Table action can only accept a single filter query. Also, you need to ensure that your column header doesn't include any spaces. If you need to filter out your rows by more than one condition, use a Filter Array action.

Return Count of Items

Whenever I use a Filter Query in a List Rows Present in a Table action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the value dynamic content from the List Rows Present in a Table action into the length() function.

Run a test. Review the outputs of the Compose action to ensure the number of items being filtered out is what you are expecting. If it's not, you'll need to check your filter query.

LMK when you've successfully filtered out the items you want to loop through.

---

In the meantime you might find these YT Tutorials helpful:
How to Work with 📆 Dates in Power Automate | Example Scenarios and Tips & Tricks

Are you easily stumped when working with Dates in Power Automate? In this Power Automate tutorial, I’ll show you how to compose an expression that will return a future date, a past date, how to count the number of days between two dates, how to check for a birthdate and anniversary date as well as tips and tricks when working with dates in Power Automate. I’ll cover some common use cases and concepts that can help you to build better Power Automate flows.

Feel free to skip ahead using the timestamps listed below. I’ve also linked a few other tutorials that you might be interested in as well.

IN THIS VIDEO:
✅ 4 Date Functions You Need to Know
✅ How to use the Convert Time Zone Action
✅ How to Get a Future Date
✅ How to Get a Past Date
✅ How to Return SharePoint Items Due in a Number of Days
✅ How to Return SharePoint Items Due within a Date Range
✅ How to write a Filter Query for SharePoint Items Due in 30, 60 and 90 Days
✅ How to Calculate the Number of Days between Two Dates
✅ How to Check for a Birthdate and Anniversary Date
✅ How to work with Dates and Times in Excel
✅ How to use a Manual Trigger with Date Input
✅ How to Output Dynamic Text with an Expression
✅ How to Check if a Date Falls on a Weekend

----

Are you using the Microsoft Power Automate Filter Array Action wrong?
In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.
1️⃣ Cross-Referencing Data
2️⃣ Filtering by Key
3️⃣ Substring Matching
Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.
IN THIS VIDEO:
✅ 3 Ways to Use the Filter Array Action
✅ How to use the Scope Action to Group Actions
✅ How to Check the Number of Items returned from a Filter Array Action
✅ How to Cross-Reference Data in Excel with a SharePoint List
✅ How the Filter Array Action Works
✅ How to Access the Dynamic Content from a Filter Array Action
✅ How to Filter Items by a Key
✅ How to Filter Items by Matching a Substring
✅ How to Use Multiple Conditions in a Filter Array Action
---
3 Mistakes YOU 🫵 are Making with the Apply to Each Action in your Microsoft Power Automate Flow
In this video tutorial I’ll go over how to avoid these common mistakes when using the Apply to Each action in a Power Automate flow:
1️⃣ Looping through a Single Item
2️⃣ Creating Unnecessary Nested Loops
3️⃣ Looping through an Unfiltered Array
At the end of the video I share a few helpful insights when it comes to using the Apply to Each action in your flow.

IN THIS VIDEO:
✅ How to avoid the Apply to Each action with a single item array
✅ How to use the item() function to access dynamic content in an array
✅ How to prevent unnecessary nested Apply to Each action loops
✅ How to use the Select action
✅ How to convert an array to a string with the Select action
✅How to use the Filter Query field
✅ How to count the number of items in an array
✅ How to use a condition control
✅ How to use the concurrency control
✅ How to set a top count
✅ How to use Compose actions for troubleshooting

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram