I just completed a project where we used Power Automate for some reporting functions. Based on a set of rules notifications should be send to users. These notifications were against a large set of documents, and those documents held around 25 columns of metadata. The focus of the rules was about timing, so that each notification could send a reminder against when a document was due for review. While it sounded simple, I did spend quite some time debugging the scenario.
Get items
Using Power Automate you can get items using different actions. In our case I used the Get File Properties as we are working with files. The downside was that by default this action returns all columns. Something that fails if you have more than 12 lookup columns. So that means specifying the correct view on the action to work around that issue. We used the Get list views action to get all list views, iterate through them and then use the DisplayName to find the list view id. This can easily be deployed to different environments.
Query for items
We ended up with an ODATA Filter Query to get the files we want. The funny thing there was that we had a date column ValidOf. This date column only stores the date (so no time). This required us to use a different format when querying. Instead of using the yyyy-MM-ddTHH:mm:ssZ you need to use yyyy-MM-dd. This does make sense; it is a date without a time. However, it took me way too much time to figure that one out as most samples out there don’t mention it.
(ContentType eq 'My ContentTYpe' and (Status eq 'Effective' or Status eq 'Expired') and (ValidOf lt '@{formatDateTime(variables('today'), 'yyyy-MM-dd')}' and Modified gt '@{formatDateTime(variables('today'), 'yyyy-MM-ddTHH:mm:ssZ')}')
Get People columns
Once we got our items, we needed to send the notifications to people who where captured in a SharePoint Column. So, we had two columns with people to notify per document. Instead of adding those columns to the view we chose to retrieve the item again. You could use the Get file properties actions, but we made the choice for a SharePoint REST call. That way we had more control over the fields we retrieved. We used the ID from the foreach action, and used a select and expand in the Get request.
_api/web/lists/getbytitle('Documents')/items('@{items('ForEach_Item')?['ID']}')?$select=PeopleField1/EMail,PeopleField2/EMail&$expand=PeopleField1,PeopleField2
When you have that data, you can use the parse json action and use a schema based on sample data. Just execute the above REST call in a browser to get results and use the generate from sample. Perhaps this can help you in building your solutions with Power Automate!
Originally posted at: https://www.cloudappie.nl/powerautomate-odata-queries/
