Nesting "AddColumn" within a Search and Filter Function

[Paultys]

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.

[zmorek]

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

[Paultys]

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]

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