Hello everyone,
I am new to Retool, and want to build some thing with the search function.
Suppose i have 1 column with Name, Address & Phone number. (ex. Rick, 23654, 22nd street, Melo, 1st street, 3344 etc.)
In the search function, if i type in search bar: Melo 23654 22
The table should be populated with the values that i have typed in the searched bar.
How can i achieve this? Any help will be greatly appreciated. TIA!
hello, welcome to the community
The table has a built-in search and using a fuzzy match should be close to what you need:
Create a text input box for users to enter their search term and the table should filter based on values or labels that match
Hello, i know this function and have implemented. But the search i need is with space seperated strings.
Thanks for explaining more - I think I'd like to know what the expected use case is here before jumping into answers. Searching on a compound field can be very challenging but it is possible.
I'm assuming that the search is used to find an individual record using any combination of name/address/phone?
7 - Melo? ie you have a matching name but the wrong address or phone number.12345 is a valid address for someone else (not Melo), do we want to see both records or just one?Separating the input value into words using space as a separator is easy enough but if they entered "22nd street" how would we know that's an address and not to separate on the space.
My personal preference here would be to have individual search boxes for each data type - ie a "name search" and "phone search" etc
You could have a generic "search" field that matches any value, as you have here, but it can be a lot more complex to code and for the user to understand.
Thank you for your response Dave.
It's possible, but I don't think it's the best user experience and it'll be challenging when the search term has a space in it (like "22nd street")
Here's an example though
const dataSource = {{ myData.value }};
const searchTerms = {{textInput1.value.trim().split(' ')}};
const exactMatches = {{switch1.value}};
return dataSource.filter(x => {
if(exactMatches) {
// do any of the search terms match the data
return searchTerms.some((e) => e === x.Data);
} else {
// do any of the search terms in lower case appear at the start of the data
return searchTerms.some((e) => x.Data.toLowerCase().startsWith(e.toLowerCase()));
}
});
You may want to change the search to be case sensitive or a contains not a starts with
forum (3).json (11.1 KB)
Thank you. I am not familiar with Jscript, so the above solution did not work for me. I used the PS query for the same :
SELECT
can you show us the resource where you put his code? if he has the right output and if data source structure in his picture is the same as yours then the problem is in the resource. is there a reason you can't use 3 text input boxes instead of 1 or is it just to keep it simple or something? i only ask cause i gotta agree w dcartlidge, unless you're the only one using this, there are going to be a bunch of possible inputs and/or combinations that include a space that won't work correctly. we'll help ya get there the best we can either way, just a bit curious is all i guess.
I am using internal Retool DB for now. I would be using Postgres DB in future. You are right, it would be complicated in fact it is complicated as its not searching the partial text. My query is only searching when the text is full ex. "22nd Street" & not " Melo, 22".
I also want to know how can i dynamically add textboxes all the content related to dynamic adding textboxes in Retool community is really old & difficult to understand. It will great to get help- Thanks
instead of dynamically creating stuff I'd suggest creating everything and dynamically hiding what you want to display. there are other ways to do it, but to do this you would put something like {{ query1.data.isSomethingHidden }} as the value for the hidden attribute of an input box.
as for your other reply about the query only searching with the text is full, do you mean with using the code from dcartlidge? or with the PS query you mentioned?
PS query i mentioned. Thank you
ah that's because you are checking for matches after splitting on all different placements of a comma but none of them check after splitting on a space.
try changing
from table1
where table1.data = ANY ({{textInput1.value.split(' ,')}}) OR table1.data = ANY ({{textInput1.value.split(', ')}}) OR table1.data LIKE {{'%'+textInput1.value.split(',')+'%'}} OR table1.data LIKE {{textInput1.value}} OR table1.data = ANY ({{textInput1.value.split(',')}})AND {{textInput1.value}} <> ''
to
from table1
where table1.data = ANY ({{textInput1.value.split(' ')}}) OR table1.data = ANY ({{textInput1.value.split(' ,')}}) OR table1.data = ANY ({{textInput1.value.split(', ')}}) OR table1.data LIKE {{'%'+textInput1.value.split(',')+'%'}} OR table1.data LIKE {{textInput1.value}} OR table1.data = ANY ({{textInput1.value.split(',')}})AND {{textInput1.value}} <> ''
I don't know how much it'll help, but now it will take 22nd Street and split it into 22nd and street then check each one. with the original one it would split 22nd Street into undefined, as it wasn't found, which will require the table to have that in it and since it doesn't (and can't?) it failed.
for reference, if it saw something like Melo, Priyanka 22nd Street w the change I added these are the splits that you will be comparing to the table defined in that PS query. these are in order:
'Melo,''Priyanka''22nd''Street''undefined''Melo''Priyanka 22nd Street''Melo'' Priyanka 22nd Street''Melo, Priyanka 22nd Street''Melo, Priyanka 22nd Street'(0+ spaces)'Melo' AND .value IS NOT ''' Priyanka 22nd Street' AND .value IS NOT ''do note the extra spaces and commas that are included from the splits.
you can also try:
from table1
where table1.data = ANY ({{textInput1.value.split(/\s*(?:,|\s|$)\s*/)}}) OR table1.data LIKE {{textInput1.value.split(' ')}} OR table1.data = LIKE ({{textInput1.value.split(/\s*(?:,|\s|$)\s*/)}})AND {{textInput1.value}} <> ''
this one uses a regular expression. it looks for zero or more spaces, followed by a comma, followed by zero or more spaces—and, when found, removes the spaces and the comma (with split).
I think I got that right, maybe someone good with regex will chime in if not, otherwise so you can read it and make changes where you need to:
/ regex delimiter
\s white space
* match '\s' 0+ times
(?: start capture group
, literal comma
| or
\s white space
| or
$ end of string
) end capture group
\s white space
* match '\s' 0+ times
/ regex delimiter
Thank you so much for your efforts 
Really appreciate a lot. The Regex thing nor the query is working with my data ( its sensitive so could not post it here )
Specially the partial search :
If i type Melo, 22nd - it gives just the results with Melo. ( no search with 22nd)
if i type Melo, 22nd Street - it gives both results since its full word.
yup, your welcome. I think the JS dcartlidge suggested would do the best, I can help the most with code anyway... so couple things:
{
string,
int,
var1: [
{
var2: string
...more key/values
}
]
}
really just interested in making sure everything is being accessed correctly. occasionally I find myself trying to iterate what I thought was an array but turned out to be an object and other things that technically work just not correctly and they've caused me big ol headaches 
I formatted the first one so you can see it better. I started with what you had since I know the data type and structure at this point (lol i think), but I noticed a bunch of your ANY and LIKE statements were comparing strings with more than 1 word/group of stuff. So this takes the result of the split you had, then it takes a look at each item and tests to see if it can split again on a space. if it can not it leaves it alone, but if it can it does the split and returns the resulting array. after all this it has either an array [] or a 2d array [[]] so it calls flat with Infinity as we know it could be 2d but we don't care if it's more so even if it's 10d we want a normal 1d array.
TLDR; it strips out any commas then it strips out any spaces if it can and finally it ensures the array sent to ANY or LIKE is a single dimensional array.
anytime you use {{ or }} you want to put a space before and after it especially when you start doing more JS as Retool can get confused sometimes. i think it's only when you have {{{ or }}} but it's best to play it safe and just put a space before and after them
from table1
where table1.data = ANY ( {{
textInput1.value.split(' ,')
.map((item) => {
item.split(' ') ? item.split(' ') : item
})
.flat(Infinity)
}} ) OR table1.data = ANY ( {{ textInput1.value.split(' ') }} ) OR table1.data = ANY ( {{ textInput1.value.split(' ,').map((item) => {item.split(' ') ? item.split(' ') : item}).flat(Infinity) }} ) OR table1.data LIKE {{ textInput1.value.split(',').map((item) => {item.split(' ') ? item.split(' ') : ('%' + nitem +'%')}).flat(Infinity) }} OR table1.data LIKE {{textInput1.value}} OR table1.data = ANY ({{textInput1.value.split(',').map((item) => {item.split(' ') ? item.split(' ') : item}).flat(Infinity) }} ) AND {{ textInput1.value }} <> ''
Not sure what this means, can you explain how the supplied code did not do what you intended so we can help to modify it?
I think @bobthebear and myself would find it easier to help you find an answer if we have more detail than "didn't work", what code you used when it didn't work, what results did it give, and what you were expecting it to do
I really appreciate your help but the thing is is copy pasted your code in Transformer & edited small changes like the table name etc. (I am unsure if i did it correct or not as i am new to Retool & jscript. )
It resulted in no results when i tried to search.
Thank you so much again Again appreciating your time & efforts !
I have tried the infinity part but it does not give any results 
I tried searching using comma separating text.
Also, for now what i have done is : I have created multiple textboxes with collapsing container and provided PS query to search since i was unsuccessful in finding partial text when the searched txt is comma separated !