A very quick tip today: When using an Azure Logic App in combination with an Azure SQL Database, filtering rows can be done like here.
As example: A T-SQL query as this one…
SELECT * FROM [dbo].[Customers] WHERE [StorageDataSentDate] IS NULL ORDER BY [Id]
…will be formulated as filter expression with an OData query as here.
The definition for the Get_rows_(V2) action looks similar as here:
… "Get_rows_(V2)": {
"inputs": {
"host": {"connection": {"name": "@parameters('$connections')['sql']['connectionId']"} },
"method": "get",
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('[someserver].database.windows.net'))},
@{encodeURIComponent(encodeURIComponent('governancetoolkit365'))}/tables/
@{encodeURIComponent(encodeURIComponent('[dbo].[Customers]'))}/items",
"queries": {"$filter": "(StorageDataSentDate eq null)", $orderby": "Id"}
},
"runAfter": {"Initialize_variable": ["Succeeded"]},
"runtimeConfiguration": {
"paginationPolicy": {"minimumItemCount": 5}
},
"type": "ApiConnection"
}, …
Depending on the data size, remember to configure pagination.
The output can be processed in an ForEach action as needed.
…"For_each": {
"actions": {
"Append_to_string_variable": {
"inputs": {
"name": "Helper",
"value": "@{items('For_each')?['StorageDataSentDate']}, @{items('For_each')?['Domain']}"
},
"runAfter": {},
"type": "AppendToStringVariable"
}
},
"foreach": "@body('Get_rows_(V2)')?['value']",
"runAfter": { "Get_rows_(V2)": ["Succeeded"] },
"type": "Foreach"
},…
Happy developing Azure Logic Apps!
Categories: App, Azure, Cloud, Developer, English, Logic apps, Microsoft, Tools
Source: https://blog.atwork.at/post/Azure-Logic-Apps-with-OData-Filter