cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SalTjx
Level: Powered On

Using O365 outlook connector, how to filter based on subject contain?

Hi,

Is there any way to find all events that contain "Test" word in their subject? I was able to find "Test Event" with "eq" statement but not able to get all events that contain the word "Test" in them.

Office365.V2CalendarGetItems("Calendar", {'$filter':"Subject eq 'Test Event'"})

How to change "eq" to contain?

 

Thanks,

-Sal

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Using O365 outlook connector, how to filter based on subject contain?

@SalTjx 

Tested...nope, it doesn't support that filter.  Oddly enough, the docs say it doesn't support startswith or substringof, use search instead - which is not supported in the connector.

 

So, here looks like the best you can do at the time:

Filter(Office365.V2CalendarGetItems("Calendar").value, "Test Event" in Subject)

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

11 REPLIES 11
Super User
Super User

Re: Using O365 outlook connector, how to filter based on subject contain?

@SalTjx 

Completely shooting from the hip here as I'm on the go and can't test this right now, but can you try:

{'$filter':"substringof(Subject,'Test')"}

I hope that is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
SalTjx
Level: Powered On

Re: Using O365 outlook connector, how to filter based on subject contain?

Thanks Randy,

I'm getting "An unknown function with name 'substringof' was found" on this.

 

Thanks,

-Sal

Super User
Super User

Re: Using O365 outlook connector, how to filter based on subject contain?

@SalTjx 

Blah... I was hoping that would be a quick answer for you.

I'll be back at a point where I can test out some other options if you haven't found a solution yet.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Super User
Super User

Re: Using O365 outlook connector, how to filter based on subject contain?

@SalTjx 

Tested...nope, it doesn't support that filter.  Oddly enough, the docs say it doesn't support startswith or substringof, use search instead - which is not supported in the connector.

 

So, here looks like the best you can do at the time:

Filter(Office365.V2CalendarGetItems("Calendar").value, "Test Event" in Subject)

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

SalTjx
Level: Powered On

Re: Using O365 outlook connector, how to filter based on subject contain?

Thanks Randy, that worked.

 

Can I ask if I have to remove that Event from Calendar how I would do that? CalendarDeleteitem use id, so I got the event in the collection to store its Id and then tried to run this formula below but "in" doesn't work on the collection. Any suggestions?

 

ClearCollect(ColSelectedEvent,(Filter(Office365.V2CalendarGetItems("Calendar").value, "Test Event" in Subject)));
Office365.CalendarDeleteItem("Calendar",Text((First(Filter(ColSelectedEvent.Id, "Test Event" in ColSelectedEvent.Subject)).Id)))

 

Thanks a lot,

-Sal

SalTjx
Level: Powered On

Re: Using O365 outlook connector, how to filter based on subject contain?

Thanks, I found the solution after testing, I had a mistake writing that formula, 


Office365.CalendarDeleteItem("Calendar",Text((First(Filter(ColSelectedEvent, "Test Event" in Subject)).Id)))

 

Office365.CalendarDeleteItem("Calendar",Text((First(Filter(ColSelectedEvent.Id, "Test Event" in ColSelectedEvent.Subject)).Id)))

 

Thanks,

-Sal

Super User
Super User

Re: Using O365 outlook connector, how to filter based on subject contain?

@SalTjx 

I'm not entirely sure what you are trying to achieve with that First(Filter( formula. 

The filter looks like it is intended to filter the collection by events with Subject that have "Test Event" in them.  This is not necessary as your collection has already been filtered by that in the ClearCollect.

So, a couple options depending on where you're going with this...

1) Delete the first item in the collection:

Office365.CalendarDeleteItem("Calendar",First(ColSelectedEvent).Id)

2) Deleted an item based on some other factor (Start Date in this case):

Office365.CalendarDeleteItem("Calendar",Lookup(ColSelectedEvent, Start=DateValue("5/21/2019 11:00:00 AM")).Id)

So, a couple take-aways here - first, don't convert the Id to Text...it already is. Second, don't re-filter the collection, you already have it filtered.  And third, which I didn't focus on (because you shouldn't need to do it), check your syntax for your filter - it should have been Filter(ColSelectedEvent, "Test Event" in Subject)

 

I hope that is clear and helpful.

 

EDIT: Looks like you found some things out while I was typing this response and we cross posted.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
SalTjx
Level: Powered On

Re: Using O365 outlook connector, how to filter based on subject contain?

Hi,

actually, I wanted to remove the event with a unique id in the subject of the event from outlook calendar, and in order to remove the event from the calendar I needed "Id of the Event" as CalendarDeleteItem(Table, Id), and the only way I can get to Event Id is from V2CalendarGetItems function. So here is a complete function that I'm using now.

 

Office365.CalendarDeleteItem("Calendar",First(Filter(Office365.V2CalendarGetItems("Calendar").value, "UniqueID" in Subject)).Id)

 

Thanks,

-Sal

Highlighted
Super User
Super User

Re: Using O365 outlook connector, how to filter based on subject contain?

@SalTjx 

Yes, that is all good, but then why have a collection with the values in it that we first worked on?

That would already have the information you need and you would not need to make another round-trip to the systems to re-lookup the same information you already have in the collection.

 

And, keep in mind with the "First" function, you are only deleting 1 event...the first one that is returned.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 191 members 5,362 guests
Please welcome our newest community members: