CODEWITHSUNDEEP
sharepointodatapower-automate
willuwontu
willuwontu

Reputation: 159

Using OData Filter Query to get open items with past due date from Sharepoint list using Microsoft Flow

In my sharepoint list of items, I have a "Date Due" field, and a "Status" field. I'm trying to create a recurring flow that occurs once a week. It will grab all items that have a Status of "Open", and a Date Due of either that day or prior to it.

This is my current flow:

  1. Recurring Trigger of 1/Week.
  2. Current Time
  3. Get Items from a share point list.
    • So far for the filter I have Status eq 'Open' and Date_x0020_Due le Date(@{body('Current_time')})
      • @{body('Current_time') is the current time retrieved from step 2
  4. I take those items and turn them into an HTML table
  5. I send an email containing that html table.

Currently the filter for step 2 does not work. Status eq 'Open' is fine, however Date_x0020_Due le Date(@{body('Current_time')}) does not work.

I'm guessing this is because OData cannot convert the DateTime object of Current Time into a Date object and is unable to compare them.

How can I change this filter so it returns items that have both a Status of closed and a Date Due of that day or prior?

Restrictions:

Upvotes: 0

Views: 5730

Answers (1)

willuwontu
willuwontu

Reputation: 159

I somehow solved this.

My end flow looks like this:

  1. Recurring Trigger of 1/Week.
  2. Get Items from a share point list.
    • For the filter I have Status eq 'Open'
  3. I use a filter array to remove the items that are past due.
    • The filter is @lessOrEquals(ticks(concat(item()?['Date_x0020_Due'], 'T00:00:00Z')), ticks(utcNow()))
      • Ticks() Converts a timestamp into the number of ticks since January 01 1601 (according to flow at least, the documentation seems to be off in this case).
      • Using this I convert my date into a timestamp by using concat() to add time to the date.
      • I compare this to the current UTC time and see if it's less that the current date.
  4. I take the filter array and input it into an HTML table.
  5. I send an email containing that html table.

Upvotes: 1

Related Questions