cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrisMendoza
Resolver IV
Resolver IV

Get events (V4) Filter Query

Looking at the JSON output does anyone know why

 

 

 

 

Filter Query =
subject eq 'Out of Office'

 

 

 

 

image.png

runs successfully however

 

 

 

 

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

 

 

 

 

image.png

 

 

 

 

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

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

@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.

 

View solution in original post

7 REPLIES 7
ccc333ab
Solution Sage
Solution Sage

Try using Filter Array instead? Something like:

ccc333ab_0-1628723916780.png

 

@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.

@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. 

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.

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')

@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.

 

@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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (3,185)