Solved: Re: Customize search? Make it search a table?

tobyinla · 07-24-2020 07:12 AM

Hi everyone

I have a simple home inventory app, with tables for items, categories and locations.

But when I’m on a “view” in the app that shows categories, clicking the search icon at the top just searches categories. I’d like to make it instead search items. Is that possible?

If not, can I hide that top search icon?

The other issue I’m having is that “search” is so literal. Searching “Usb cable” finds an item called ‘usb cable’ but not ‘usb black cable’. Is there a simple way to fix that?

Thanks in advance for any help :)))

Toby

Steve

You’re missing some important operators:

View solution in original post

JuneCorpuz

Just go to Items view and search the category there. But please make sure you make the category column searchable.

I believe the search bar is searching for string like usb cable and not usb & cable

tobyinla

Thanks but I think you misread what I was asking completely 😕

Steve

No, it only searches the displayed table or slice.

Unfortunately, there’s no way to hide just the search bar but keep the main menu.

Depends on your definition of “simple”. The built-n search feature is a simple exact-text search, as you’ve noticed. You could implement your own search feature (but it wouldn’t use the search bar), but it’s not trivial, but it also wouldn’t be horrifically difficult, either. You could also “easily” expand the scope of search while you were at it.

IAR

I understand we cant hide the top bar while keeping the (hamburger) menu.
But i noticed your sample app (community28312), doesn’t have the search button on top. Where can I change that?

Steve

The search bar doesn’t occur in form views.

IAR

That makes sense. Thank you.

Grant_Stead

The assistant is your best bet for searching the entire app… It actually works really quite well…

tobyinla

Thanks guys! Assistant is so much better than search, you’re right. It’s quite counter intuitive though - I wish I could make the “search” button return proper search results like the Assistant does (ie. Not only find perfect phrase matches but rather look for any combo of the search terms in its results).

But yes, looks like I’m stuck with hiding the search button (and also the menu!). 😕 thanks though for your help!!

Now to work out how to craft an expression for my own search :))

I guess, in plain English, it needs to “**

take search terms, split it up into separate words, look for “word 1 in table X columns A B C & D, AND word 2 in table X columns A B C & D, AND word 3 in table X columns A B C & D

**” etc. Plus ideally it would then - after showing those results - show the “or” version of this.

Why do I suddenly feel like my “no code” adventure with AppSheet is just turning into my standard PHP/MySQL coding?

Hmmmm anyone made these sorts of search expressions before?

Toby

Steve

Try:

FILTER(
  "table",
  ISNOTBLANK(
    INTERSECT(
      SPLIT(TRIM([_THISROW].[search terms]), " "),
      (
        SPLIT(TRIM([search target column 1]), " "))
        + SPLIT(TRIM([search target column 2]), " "))
        ...
        + SPLIT(TRIM([search target column N]), " "))
      )
    )
  )
)

Note that punctuators will confound the search some, as the SPLIT() above is strictly on (single) spaces.

Yup.

See also:

tobyinla

Thanks again Steve for your help!

It’s working, except it only wants to search the “NAME” column. As you can see below, I’m trying to search NAME, STARNUM and DESCR_SIMPLE columns.

I tried adding an “OR” but it gave an error. Help me, oh wise one! ;-))

LINKTOFILTEREDVIEW(“L2_items_bottom_listphotos”,
ISNOTBLANK(
INTERSECT(
SPLIT(TRIM([_THISROW].[SearchInput]), " "),
(
(SPLIT(TRIM([NAME]), " ")
SPLIT(TRIM([STARNUM]), " ")
SPLIT(TRIM([DESCR_SIMPLE]), " ")
)
))
)
)

Steve

You’re missing some important operators:

tobyinla

Brilliant! It’s working!! Thanks!!

LINKTOFILTEREDVIEW(“L2_items_bottom_listphotos”,
ISNOTBLANK(
INTERSECT(
SPLIT(TRIM([_THISROW].[SearchInput]), " "),
(
(SPLIT(TRIM([NAME]), " ")
+SPLIT(TRIM([STARNUM]), " ")
+SPLIT(TRIM([DESCR_SIMPLE]), " ")
)
))
)
)

The + trips me up because it’s a plus but it’s actually an “or”, sorta. lol.

Please don’t waste your Saturday replying to this today lol - but my next question is… If any of the columns contain “butter”, and I search for “but”, it doesn’t return a result. Looking through all the functions, I’m assuming I need to add some sort of “contains” function. I’m just not sure where! lol

Seems to me that INTERSECT is doing the heavy lifting in the statement, yes? It’s finding whether there is any “commonality” between my search form’s input and some columns in the table. And I need to tell it to “don’t just look for exact matches, but partial matches are fine”. Hmmm.

Steve

No, it’s a plus. See Constructing a List with Subtraction here:

Correct.

That’s an entirely different requirement than you originally expressed. I’m not aware of a way to do partial matches with multiple terms from a single input without resorting to some ugly contrivances. It’s doable, but it’s ugly.

Steve

How I spent my weekend…

https://www.appsheet.com/samples/community28312?appGuidString=2ea965d7-8656-4d9d-a9d2-ed3dc5ca5813

Thanks for the inspiration!

Koichi_Tsuji

Thank you Steve. I would gently suggest you open up new thread with tips and tricks category so that this sample app stands out!

tobyinla

You’re awesome, Steve! You’ve been so helpful to me. I really wish I had time to post all the stuff I’ve learned and a quick learning video as I’ve managed to do what I wanted (ie. make a full inventory app for myself) but it was extremely difficult, particuarly with the search issues but also with trying to implement a “categories /subcategories/subsubcategories/subsubsubcategories” system in AppSheet. (I’m glad I already knew javascript/php/asp/mysql so I could figure it out, otherwise I would have been even MORE lost! lol)

I will check out your search thing when I get a chance!!!

Fabio_J_Palacio

Hello Steve: do you think it is possible to make a search engine that search for the exact text? For example, if I search for "red" in a given column, it only returns red and not redstone? Or if I search for “cold” it only returns me columns with "cold" but not "coldplay" please.:)

Steve

That's what this entire topic is about. How does the solution given above not meet your needs?