cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Paultys
Frequent Visitor

Nesting "AddColumn" within a Search and Filter Function

Hi Everyone,

 

Im trying to find the correct way to nestle an add column function into an existing filter/search function.

 

I have a number of health and safety documents in the library 'Health and Safety', The library has three key column, Categorisation (choice column) COSHH Risk Rating (choice column) and Keywords (Enterprised Managed Metadata)

 

I have two drop downs and one text search box.

 

The below formula works just fine and allows me to filter the document library by Categorisation and COSHH Risk Rating and search by file name....

 

   Search(          

        If(

    Catdd.Selected.Result="All" And COSHHdd.Selected.Result="All", 'Health and Safety',

 Catdd.Selected.Result="All" And COSHHdd.Selected.Result<>"All", Filter('Health and Safety','COSHH Risk Rating'.Value=COSHHdd.Selected.Result),   

 Catdd.Selected.Result<>"All" And COSHHdd.Selected.Result="All", Filter('Health and Safety',Categorisation.Value=Catdd.Selected.Result),  

 Catdd.Selected.Result<>"All" And COSHHdd.Selected.Result<>"All", Filter('Health and Safety', 'COSHH Risk Rating'.Value=COSHHdd.Selected.Result And Categorisation.Value=Catdd.Selected.Result)),

 'Chemical Search'.Text,"{Name}")

 

But I would also like to search through the enterprise managed metadata column named "Keywords"

 

If I use this formula on its own it works fine and allows me to search via the Keyword column.....

 

Search(AddColumns('Health and Safety', "KeyWordValue", Concat(Keywords,Label)), 'Chemical Search'.Text,"KeyWordValue")

 

But when I try to add the two together it falls apart and Im not sure how the formula should look.

 

Ive tried the following...

 

Search(       
     If(
Catdd.Selected.Result="All" And COSHHdd.Selected.Result="All", 'Health and Safety',
 Catdd.Selected.Result="All" And COSHHdd.Selected.Result<>"All", Filter('Health and Safety','COSHH Risk Rating'.Value=COSHHdd.Selected.Result),
 Catdd.Selected.Result<>"All" And COSHHdd.Selected.Result="All", Filter('Health and Safety',Categorisation.Value=Catdd.Selected.Result),
 Catdd.Selected.Result<>"All" And COSHHdd.Selected.Result<>"All", Filter('Health and Safety', 'COSHH Risk Rating'.Value=COSHHdd.Selected.Result And Categorisation.Value=Catdd.Selected.Result)),

 

AddColumns('Health and Safety', "KeyWordValue", Concat(Keywords,Label)), 'Chemical Search'.Text,"KeyWordValue", "{Name}")

 

But it does not want to play.

 

Any ideas are gratefully received.

 

 

3 REPLIES 3
zmorek
Super User
Super User

Let's try and clean that up a little bit; please try this:

 

 

With({wDataSource: 
	AddColumns('Health and Safety',
		"KeyWordValue",
		Concat(Keywords,Label)
	)},

Search(
	Filter(wDataSource,
		(Catdd.Selected.Result="All" || COSHHdd.Selected.Result = "All"),
		(Categorisation.Value = Catdd.Selected.Result || 'COSHH Risk Rating'.Value=COSHHdd.Selected.Result)
	),
	'Chemical Search'.Text,
	"{Name}",
	"KeyWordValue"
)
)

 

Hi, 

 

Thank you so much for taking a look, but sadly no that's not working. I don't get any returns from the search box on its own and the filters only return results if one is selected, if I use both filters no results are returned.

 

Strangely though if I select an option on a filter, instead of leaving at "All" then the search box does work.

 

 

 

Paultys
Frequent Visitor

Just to give this a bump is anyone able to suggest how to solve this?

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

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

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,625)