Skip to main content

Notifications

Power Automate - General Discussion
Unanswered

filter array for a calculated date field

(0) ShareShare
ReportReport
Posted on by 2

Hello, 

 

I have created a simple flow that is triggered once a day. The objective of this flow is to go and check, on daily basis, the expiry date and if any item meets that condition an email will be sent to the user. 

 

So on the sharepoint list I have created a calculated column which calculates the expiry date based on the issuance date. 

 

I know that Flow doesn't accept filtering calculated fields/column in Get Item action. Some suggest to create a Filter Array. I did but unfortunately the filter is not filtering and it is sending an email to everyone in the list on daily basis even though the expiry date is way far from today's date. 

 

Any one can help please? 

Thanks

 

Flow_FilterArray.JPG

  • Re: filter array for a calculated date field

    i tried this but I am getting the error:

    Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'.

     

    Not sure why, since I have already filtered the array to exclude null values for this column. Is it possible that the text in red is the issue?

     

    formatDateTime(item()?['Expiry_x0020_Date'],'yyyy-MM-dd')
  • Ellis Karim Profile Picture
    Ellis Karim 10,547 on at
    Re: filter array for a calculated date field

    I think what is happening is that the date values you are comparing in the Filter array action are not equal, even though they appear to have the same date in SharePoint:

    ekarim2020_5-1631647443481.png

    The CertificateExpiryDate field is a calculated field and the value we see displayed in SharePoint in the above example is 14/09/2021. But internally SharePoint stores this calculated date value along with a time stamp (midnight) as 2021-09-14T00:00:00Z (UTC format):

    ekarim2020_6-1631647573289.png

    So when you use the CertificateExpiryDate field in the Filter Array action and compare it with the formatted date from the UtcNow() function you are actually comparing two different values:

    ekarim2020_4-1631646890468.png

    I suggest that you try formatting the CertificateExpiryDate as yyyy-MM-dd in the Filter Array action:

    ekarim2020_2-1631645776205.png

    Try that and let me know how you get on.

    Ellis

  • The_Parisian Profile Picture
    The_Parisian 2 on at
    Re: filter array for a calculated date field

    Hello, 

     

    Thank you for your response? Do you mean the formula that I used for the date field calculations? So basically, the date is calculated based on the another a number field called "Critical". The expiry date varies subject to the content of the Critical field. 

     

    Here's the formula I used to create the calculated field "CertificateExpiryDate" - 

    =IF(Critical=1,DATE(YEAR([Certificate Issuance Date]),MONTH([Certificate Issuance Date])+11,DAY([Certificate Issuance Date])),DATE(YEAR([Certificate Issuance Date]),MONTH([Certificate Issuance Date])+35,DAY([Certificate Issuance Date])))
     
    Looking forward for your reply. 
     
    Thanks
    Tony
  • Ellis Karim Profile Picture
    Ellis Karim 10,547 on at
    Re: filter array for a calculated date field

    I would need to see some sample data for CertificateExpiry to help figure out why your filter is not working. In the mean time uou should add a condition to check if the Filter Array action returns any results. If not, do nothing, else process the results in your Apply to each loop:

    ekarim2020_1-1631395448816.png

    ekarim2020_2-1631395476140.png

    Ellis

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,567

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,907

Leaderboard