How to use Odata to filter query , to get the events from past 7 days until the following 28 days

Question

enter image description here

Hi,

I am trying to filter the query in Odata in logic app. as this is a calender app, I want to get the events in the past 7 days and the following 28 days.

I have tried various different solutions but it doesn't work.

Answer 1

Alternative to filter query, you can achieve this taking createdDateTime variable and calculate for the expected results since createdDateTime's format and the utcNow() format will be different. Now you need to convert both into one format and check for the difference. Below is the flow of my logic app.

Below is the expression I have used to get the difference.

dateDifference(formatDateTime(outputs('Compose'),'yyyy-MM-dd'),formatDateTime(utcNow(),'yyyy-MM-dd'))

For demonstration purpose I have taken the difference as 7 but you can mention as per your requirement.

Below is the complete code view of my logic app for your reference.

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Compose_3": {
                "inputs": "@variables('data')",
                "runAfter": {
                    "For_each": [
                        "Succeeded"
                    ]
                },
                "type": "Compose"
            },
            "For_each": {
                "actions": {
                    "Compose": {
                        "inputs": "@items('For_each')?['createdDateTime']",
                        "runAfter": {},
                        "type": "Compose"
                    },
                    "Compose_2": {
                        "inputs": "@dateDifference(formatDateTime(outputs('Compose'),'yyyy-MM-dd'),formatDateTime(utcNow(),'yyyy-MM-dd'))",
                        "runAfter": {
                            "Compose": [
                                "Succeeded"
                            ]
                        },
                        "type": "Compose"
                    },
                    "Condition": {
                        "actions": {
                            "Append_to_array_variable": {
                                "inputs": {
                                    "name": "data",
                                    "value": "@items('For_each')"
                                },
                                "runAfter": {},
                                "type": "AppendToArrayVariable"
                            }
                        },
                        "expression": {
                            "and": [
                                {
                                    "lessOrEquals": [
                                        "@int(slice(outputs('Compose_2'),0,nthIndexOf(outputs('Compose_2'),'.',1)))",
                                        "@int('7')"
                                    ]
                                }
                            ]
                        },
                        "runAfter": {
                            "Compose_2": [
                                "Succeeded"
                            ]
                        },
                        "type": "If"
                    }
                },
                "foreach": "@body('Get_events_(V4)')?['value']",
                "runAfter": {
                    "Get_events_(V4)": [
                        "Succeeded"
                    ]
                },
                "type": "Foreach"
            },
            "Get_events_(V4)": {
                "inputs": {
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['office365']['connectionId']"
                        }
                    },
                    "method": "get",
                    "path": "/datasets/calendars/v4/tables/@{encodeURIComponent(encodeURIComponent('xxx'))}/items",
                    "queries": {
                        "$top": 10
                    }
                },
                "runAfter": {
                    "Initialize_variable": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            },
            "Initialize_variable": {
                "inputs": {
                    "variables": [
                        {
                            "name": "data",
                            "type": "array"
                        }
                    ]
                },
                "runAfter": {},
                "type": "InitializeVariable"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "manual": {
                "inputs": {},
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {
        "$connections": {
            "value": {
                "office365": {
                    "connectionId": "/subscriptions/xxx/resourceGroups/xxx/providers/Microsoft.Web/connections/office365",
                    "connectionName": "office365",
                    "id": "/subscriptions/xxx/providers/Microsoft.Web/locations/eastus/managedApis/office365"
                }
            }
        }
    }
}