Yesterday I documented in "Get data from SharePoint as HTML table with Azure Logic Apps" how data is retrieved from SharePoint Online with an Azure Logic app. To dig a little deeper into working with SharePoint list data, here are a few more helpful tips.
Query for empty fields (eq null)
To find items in an SPO list that have NO value in an Azure Logic App SharePoint Get items action, we can use "<field> eq null" (and not eq '' ). Simple as that, but good to remember. So in our sample, the ODATA filter query is as here: SubscriptionEndDate lt addDays(utcNow(),-30) and Invoice eq null
Get all list items that have a subscription end date in the next month and don´t have an invoice entry.
Working with SPO dropdown fields
Often, lookup data is used in SharePoint custom lists to retrieve a predefined set of data. Such a field is usually populated with data from another SharePoint list. The list definition in SPO looks as here. In this sample, the dropdown column name is "Kunde" which is German for "customer" and there´s another list named "Kunden" holding the data.
In If we run a SharePoint Get items action as here…
…and look at the results, we see how dropdown values are delivered: as a subset of column "Kunde" showing the linked Id and the value.
To get the values of such lookup fields, we need to expand the column. I found a tip to accomplish that here. Switch to the code view.
In the editor, locate the SharePoint Get items action and the queries and add another key value pair
, "expand": "<field-name>"
After that is done, switch back to the designer. Now, the designer knows that property and we can use the expanded properties as the Id and the value for ODATA filter and order by expressions. This is expressed as "<field>/<property>". In my sample to get the Id, we can use "Kunde/Id" as here: SubscriptionEndDate lt addDays(utcNow(),-30) and Kunde/Id lt 5
Voila, we can use the lookup field in our query.
I hope this quick How-To with SharePoint helps for such operations.