Smart Filter of a Description in Gallery

[LynetteCotter]

Hi All, 

Long time reader - first time posting. 

I have a Stock searching app that allows staff to search for a stockcode by typing in the code or the description. 
However when typing in the description it has to be in the exact order: for example typing in "Bolt Nut" will only bring up stock items with Bolt Nut in their description, not " nut bolt" 
Currently this is my filter:

Filter(StockInfos,Or(SearchField.Text in StockCode,SearchField.Text in DESCRIPTION))

Stockinfos is the dataverse table 

Searchfield is the text input for the user to search. 

StockCode and DESCRIPTION are the columns in the dataverse table I need to search. 

Effectively I need to Split the search field and then perform this filtering on each item in the table result of the Split. Which I cannot seem to do successfully. 

I have a lot of records in my gallery as well. 

Thank you for your help. 

[WarrenBelz]

Hi @LynetteCotter ,

Try this

Ungroup(
   ForAll(
      Split(
         SearchField.Text,
         " "
      ) As _Items,
      Filter(
         StockInfos,
         _Items.Value in Stock ||
         _Items.Value in DESCRIPTION 
      )
   ),
   Value
)

There is a small glitch I found in testing that initially a space at the end of a word temporarily brings up everything, but it works fine once the next string is entered.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[LynetteCotter]

Hi Warren, That has worked except pulling through all results for each of the Split items. 

For example if I put in "TEST BOOT" Its pulling up items that have Test in the description OR Boot in the description. 

I want it to show only if it has both Test and Boot in the description or Stockcode. 

Thank you so much for your help. 

[WarrenBelz]

@LynetteCotter ,

I was going to say instead or Or at the bottom, but that does not work - I will do more testing

[LynetteCotter]

Thank you Warren, 

Ive tried a couple of things and I just cannot seem to get it to work. It seems like it should be an easy enough solution... I am happy to have two text boxes - one for description one for stock code if this simplifies it, as the stock code with generally be just one word searching. 

[WarrenBelz]

Hi @LynetteCotter ,

The below works on two items - you would need to keep adding on for more

With(
   {
      _Items: 
      Split(
         SearchField.Text,
         " "
      )
   },
   With(
      {_Count: CountRows(_Items)},
      Filter(
         StockInfos,
         First(_Items).Value in Stock &&
         First(_Items).Value in DESCRIPTION && 
         If(
            _Count > 1,
            Index(_Items, 2).Value in Stock &&
            Index(_Items, 2).Value in DESCRIPTION
         )
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[LynetteCotter]

Hi Warren, I am now getting a Delegation warning unfortunately. 
😞

[WarrenBelz]

@LynetteCotter ,

OK try this

With(
   {
      _Items: 
      Split(
         SearchField.Text,
         " "
      )
   },
   With(
      {_Count: CountRows(_Items)},
      Filter(
         StockInfos,
         First(_Items).Value in Stock &&
         First(_Items).Value in DESCRIPTION && 
         (
            _Count <= 1 ||
            (
               Index(_Items, 2).Value in Stock &&
               Index(_Items, 2).Value in DESCRIPTION
            )
         )
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[LynetteCotter]

Hi Warren, You are trying so hard an I really appreciate this!

But that one only shows whether the text is in both the stock code and the description. I need it to show if all the words are in either the stock code or the description. 

[WarrenBelz]

@LynetteCotter ,

This is getting more complex - if this does not work, please have a think about what may be wrong with it

With(
   {
      _Items: 
      Split(
         SearchField.Text,
         " "
      )
   },
   With(
      {_Count: CountRows(_Items)},
      Filter(
         StockInfos,
         (
            _Count = 1 &&
            (
               First(_Items).Value in Stock ||
               First(_Items).Value in DESCRIPTION
            )
         ) || 
         (
            _Count = 2 &&
		    (
               First(_Items).Value in Stock &&
		       Last(_Items).Value in Stock
            ) ||
            (
               First(_Items).Value in DESCRIPTION &&
               Last(_Items).Value in DESCRIPTION
            )
         )
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps