0

This started as a question, but I figured out the answer as I went, so just posting for reference.

Desired Behaviour

Use the OData Filter Query field in the SharePoint Get Items action in Power Automate to get list items that have already expired or are about to expire and send an email notification containing details about them.

What I've Tried

I have three date-only columns:

Lease_x0020_Expiry_x0020_Date  
End_x0020_of_x0020_Life  
Contract_x0020_End_x0020_Date  

The columns do not require values, therefore they may or may not have a value.

I want to create a flow that sends a notification when the following conditions are true:

  • if any of the date values of these columns are in the past
  • if any of the date values of these columns are within the next 30 days

I have successfully defined this logic in column formatting with:

  "style": {
    "color": "=if(Number([$Lease_x0020_Expiry_x0020_Date]) == 0, '', if([$Lease_x0020_Expiry_x0020_Date]>(@now +2592000000), 'green', if([$Lease_x0020_Expiry_x0020_Date]>@now && [$DueDate]< (@now +2592000000), 'Red', 'Red'))"
  }

The logic is saying:

  • if there is no date, then don't apply color
  • if there is a date, more than 30 days in the future, make text green
  • if there is a date, within 30 days, make text red
  • for everything else (ie for dates in the past), make text red

I am now trying to recreate this logic using OData Filter Query in the SharePoint Get Items action.

Related Reading

OData query operators supported in the SharePoint REST service

OData Filter query in SharePoint Get items in Power Automate

0

So the requirements are:

1) Start with a Scheduled Cloud Flow and Get Items action with the Filter Query defined below

2) Define the syntax to use when applying multiple filters in an OData filter:

It looks like this is as simple as using a combination of and and or operators, and brackets () if required:

condition_01 and (condition_02 or condition_03)

3) Define actual logic to use:

This successfully returns all items, that have a date, where the date is before today (based on this):

Lease_x0020_Expiry_x0020_Date lt '@{utcNow('yyyy-MM-dd')}'  

To use the date is in the past logic for all three fields, it seems I can use:

Lease_x0020_Expiry_x0020_Date lt '@{utcNow('yyyy-MM-dd')}' or End_x0020_of_x0020_Life lt '@{utcNow('yyyy-MM-dd')}' or Contract_x0020_End_x0020_Date lt '@{utcNow('yyyy-MM-dd')}'  

I can also initialize a variable above the Get Items action, where the value is the expression utcNow('yyyy-MM-dd'), and reference that variable, rather running a new expression for each condition.

The date is within the next 30 days logic seems to be:

Lease_x0020_Expiry_x0020_Date gt '@{variables('utc_now')}' and Lease_x0020_Expiry_x0020_Date lt '@{variables('utc_now_plus_30_days')}'

where I have previously initialized the variables:

utc_now = utcNow('yyyy-MM-dd')
utc_now_plus_30_days = addDays(utcNow(),30,'yyyy-MM-dd')  

So putting all 3 columns together, with both logic checks (in the past and in the next 30 days) looks like:

Lease_x0020_Expiry_x0020_Date lt '@{variables('utc_now')}' or End_x0020_of_x0020_Life lt '@{variables('utc_now')}' or Contract_x0020_End_x0020_Date lt '@{variables('utc_now')}' or (Lease_x0020_Expiry_x0020_Date gt '@{variables('utc_now')}' and Lease_x0020_Expiry_x0020_Date lt '@{variables('utc_now_plus_30_days')}') or (End_x0020_of_x0020_Life gt '@{variables('utc_now')}' and End_x0020_of_x0020_Life lt '@{variables('utc_now_plus_30_days')}') or (Contract_x0020_End_x0020_Date gt '@{variables('utc_now')}' and Contract_x0020_End_x0020_Date lt '@{variables('utc_now_plus_30_days')}')

4) Setup email content and send the email

After the Get Items:

  • Add a Select action
  • If you want to later display the count of items returned in your email, initialize a string variable (eg item_count) with the expression value: length(body('Select'))
  • Initialize a blank string variable (eg item_rows) that you can append details about each item
  • Add an Apply to each, where the input is @{body('Select')}
  • Within the loop, append to the string variable item_rows referencing details from the Select, eg @{items('Apply_to_each')?['SomePropertyHere']}, add a </br> at the end of each line
  • Add a Send an email (V2) action and the item_rows in the body (make sure to be in </> view, so that it handles your </br> values correctly.

Aside:

Interestingly, if I use Get Items, without any filter, and then copy and paste the results into a code editor, and then search for Lease_x0020_Expiry_x0020_Date there are only 11 instances of it, even though there are 30 items in the list. It seems, therefore, that Get Items only returns properties, if a value has been defined for the associated field.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.