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 Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (4,218)