Get events (V4) Filter Query

[ChrisMendoza]

Looking at the JSON output does anyone know why

Filter Query =
subject eq 'Out of Office'

runs successfully however

Filter Query =
organizer eq 'organizer.email@address.com'

Invalid filter clause
clientRequestId: 994af462-d35a-4d8c-8640-1c767fdc6b3a
serviceRequestId: 7a96997c-ed9c-4e62-b04b-ce7c3ce7b962

[ccc333ab]

Try using Filter Array instead? Something like:

[poweractivate]

@ChrisMendoza 

It could be because in the underlying Graph API the schema for subject is just "subject" (simple one-level schema)

"subject":"Plan summer company picnic",

whereas the schema for the organizer's email address is organizer/emailAddress/address and it is buried deeper.

  "organizer":{
    "emailAddress":{
      "name":"Adele Vance",
      "address":"AdeleV@contoso.onmicrosoft.com"
    }
  }

When I tested though, organizer/emailAddress/address results in InternalServerError - only organizer/emailAddress/name works for me (and I did not thoroughly test if it works. Since it's 'name' it would filter on the Name, not the email address).

So the above could be the reason.

In case the solution from @ccc333ab is not desirable to you due to the usage of workaround rather than usage of the Filter Query itself, you can try this filter query to see if it works:

organizer/emailAddress/name eq 'a'

Please note caveat, that you may need to give the name of the user (not the email address) where it says 'a' above (and keeping the single quotes of course) for above to work. Replacing the part that says 'name' above with the one that should be there, 'address' - unfortunately gives an unexpected result of the infinite retry and InternalServerError for me. I might see if I can check more closely in case whether I can give you the working solution with using Filter Query and using the Email Address of the organizer in it.

[poweractivate]

@ChrisMendoza 

To tell you the truth, I think that even if you apply what I believe is the most ideal answer to your question, which is to use an expression like this:

organizer/emailAddress/address eq 'organizer.email@address.com'

This cannot be done because it might be impossible with query to filter by email address of organizer in particular.  The suggestion even in this link is to filter by name instead such as in my given reply.

I might check closer in case for another way still using the filter query somehow, but this might be why. 

[ChrisMendoza]

Sure that would/should work. Understanding and learning the ODATA filter query and why it doesn't work when the values are in same 'object' is my goal. Thank you.

[ChrisMendoza]

The JSON does not show an object of organizer. I did at first try body/value/organizer at which point I tested subject. Trying your solution the same result occurs. Incidentally, using the same logic as a trigger condition on 'When a new event is created (V3)' works just fine.

@equals(triggerBody()?['organizer'],'someEmailAddress@onmicrosoft.com')

[poweractivate · Accepted solution]

@ChrisMendoza 

---

@ChrisMendoza wrote:

The JSON does not show an object of organizer. 

Despite this, once you use Filter Query, you are entering another world - the world of where the underlying infrastructure is.

For example, try this. I just tried it and it worked, it might amaze you:

1) Go to e-mail inbox and look at the display name of the person for which there is an event invite on the organizer. Example: an event coming from My Events with an email address of mycoolevents@example.com - would mean you should use My Events as the test value

2) Use filter query of 

organizer/emailAddress/name eq 'My Events'

3) To your possible bewilderment, you may notice it has returned a collection including events by the organizer you might have been expecting it to. This is despite the fact that organizer: "some value" is the only JSON shown. That simple organizer: "some value"  is actually the abstraction, that is not what is really returned in the intermediate step the infrastructure is using. Power Automate is telling you organizer: "some value" but that is not really what is happening. It is actually:

    "organizer":{
        "emailAddress":{
            "name":"Samantha Booth",
            "address":"samanthab@a830edad905084922E17020313.onmicrosoft.com"
        }
    }

In most of Power Automate you don't worry about this, but because you want to use the Filter Query and OData you are interacting with the layer below the abstraction so then this reasoning I introduced may begin to apply here.

There is an additional layer of an issue though. You can only filter by the name in this way. I believe the underlying endpoint has not implemented the email address filtering so even if you use 

organizer/emailAddress/address eq 'organizer.email@address.com'

It will unfortunately not work for Email due to filter not being implemented and this may explain why the above will end up actually returning InternalServerError in Power Automate - so instead, only this query will work and you must use the Name instead only (which also happens to be mentioned in the link as well):

organizer/emailAddress/name eq 'My Events'

This one works correctly for 'name' whereas the one for 'address' does not work, for the reason specified above..

Currently, I don't have another idea if you have to use Email other than to resort to a workaround like the one posted by @ccc333ab  of just getting the whole thing and filtering it. I do believe that in some cases, that specific workaround may only work if the sample size is small enough, so depending on your scenario, you might need to just resort to filtering by name rather than email address in the OData Query as I am suggesting here:

organizer/emailAddress/name eq 'My Events'

if you need a solution involving the OData Filter specifically.

I do understand this may not be ideal to filter by the name and not the email address - however, the filtering of email address in that specific way might unfortunately not be supported right now.

You also mentioned that this worked:

@equals(triggerBody()?['organizer'],'someEmailAddress@onmicrosoft.com')

The reason for that is, since the Trigger is not a Filter OData Query, this uses the abstraction, and would use the object as you saw it returned in Power Automate JSON, and not the underlying one. The underlying one is only applicable in the OData queries specifically.

Check if this helps explain it.

[ChrisMendoza]

@poweractivate- Thank you for the in-depth reply. So much more to learn. Great 'Super User' explanation! I will be looking into your review links tomorrow. I must admit I do need to figure out the 'name' of the 'organizer' as this mailbox is generated by Bookings but I am sure it can be done.