I'm trying to create a search feature where a user can search for let's say John Smith or a Tax id or PIN. For the standard search, if the user searches for John Smith, it does not return any records, even though there is a John Smith. I'm assuming this is happening because the first and last name are in different fields.
I kind of want an ai search but only search one table and any fields or combination of fields in that table.
Is this possible?
Unless I'm misunderstanding, I'm pretty sure you can achieve this in a few ways:
Qrew LegendCarol. If you need a technique to allow searching on any specific fields, if even if they are not on the report, I have an easy trick for that just post back here and I will share that with you. For example, if you specifically wanted them to search on John Smith, there is a way to add a secret hidden field on the report that would include the string John Smith
Denin, the search widget on the dashboard does not work. You would still need to put in the AND in between each word to get back the records.
The formula field is the same situation, you have to have the AMD in between each word.
I don't know about the code page.
Qrew LegendThe low tech solution is to train your users to use the syntax format below in the filter box
john AND smith
The AND must be in upper case. The other words are not case sensitive. Is that good enough?
You can edit the report description to include this as a helpful "Pro Tip".
I didn't even know that. Are there other options?
Qrew LegendAnother option is to use an <ask the user> report with two questions.
ALL of
"some field" contain <ask the user>
"some field" contains <ask the user>
You would have to check if it works ok if they just have one search term to provide or if for example you ask three questions and they only want to search for john and smith.
Never done this before but basically my thought would be to include a formula text field which will be our search field with all the fields you want to search and then mark it searchable in fields.
[First Name] & " " & [Last Name] & " " &
[Tax ID] & " " &
[PIN] & " " &
You can add other fields you want to use to search. Use this field in reports or also in widgets and you will get your matching records beause the combined text is indexed.
I had already done this but when you do the search you still have to put in AND between each search criteria.
Qrew LegendCarol,
Let's nail this in three steps.
Make a new formula field called Search Target with the formula
List(" ", [First Name], [Last Name], [Tax ID], [PIN])
Put that field on the report and observe that the search for John Smith works.
Then, you can make a new formula rich text field called Hidden Search Target.
"<a name=" & URLEncode([Search Target]) & "</a>"
add that to a report. Either name the rename the field like a dot . or else do a column override on the report to have a . or blank for the column name and put the field at the far right of the report where users will not really notice it.
That allows you to make a hidden column with all the possible search terms you want without cluttering up the report with extra visible fields.
I used a similar solution to what you came up with, Mark! I made a formula rich text field, using the List function to string all of my searchable fields together, then inserted a small transparent image and used the result of the List function in the "title" attribute of the <img> tag. It ended up looking something like this. I purposely didn't include all of my searchable fields, just a few for this example.
var text agency = [Agency];
var text recordowner = UserToName([Record Owner],"FF");
var text projectname = [Project Name];
var text searchtitle = List(" ",$agency,$recordowner,$projectname);
var text transparent = "<img src='INSERT TRANSPARENT IMAGE URL HERE' title='" & $searchtitle & "'>";
$transparent
Then I added that field to the far right of my report. I overrode the column name with a dash (-). To the end user, it just looks like a blank column, but it allows them to search a lot of things that aren't on the report.
I already had a report like that. It works, I was hoping QuickBase had come up with a better solution. I did a combined field of all the possible fields I would want them to search on but it doesn't work unless you but the And in between each search item.
Qrew LegendWell, the AND syntax is actually quite a low tech and easy solution, but I understand the problem is you need to socialize the existence of that feature within your user base. Maybe you should start a monthly newsletter to your users about tips and tricks. :)
Ok, thanks for the suggestions.
