Multiple Search terms

[Steven Horn]

I’m moving from Bento and getting acquainted with Tap Forms. In Bento, I can search a database for multiple terms and it will bring up results that include all and only all those terms and in any order.

I haven’t been able to do the same in Tap Forms.

For example, if I search for this: day night cat dog , Bento will display my entries that have those four terms somewhere in the record and in any order. However, in Tap Forms, it appears to regard my search as a single search term and shows zero results. Is there a way to get Tap Forms to behave as I describe Bento doing regarding this sort of search?

[Brendan]

Hi Steven,

You’re right, Tap Forms isn’t searching the same way that Bento did. What it’s doing is searching the field values for matches, but as soon as it finds a field value that matches one or more terms and no other field values contain those exact search terms, it ignores other field values in other fields.

So for example, if you have a single record which has a field which contains “day night” and another field in the same record that contains “cat dog”, then when you type “day night”, Tap Forms will return the record. If you type “cat dog” it will also return the record. But if you type “cat dog day night”, it will not return the record because there’s no one single field value within your record which contains all the terms “cat dog day night” in any order. All terms without quotes otherwise Tap Forms will treat it as an exact phrase search.

I hope that clears it up. I’m sure I could change that to make it work more like Bento. But that’s a down the road type thing.

Thanks!

Brendan

[Steven Horn]

I use that function in Bento to show me a list of entries that meet all the criterea. When I search movies that are comedies with Arnold Schwarzenegger and Charlie Sheen, I can get very specific results.

If you can replicate that, I’d say it would be a good feature in days to come.

Thank you for your quick reply.

[Anthony Cardona]

I also am having issues searching for terms, I currently have over a 3000 person database and cannot search for someone with last name of Mississippi by typing in “ssipp” or does it only work with exact match words ? If so will there be an update for non exact words or numbers ?

[Brendan]

Hi Anthony,

The FTS (full text search) in Tap Forms works off of the SQLite search engine. So it only provides for prefixed search terms. That is, you can search for “Miss”, but not “ssipp”.

Thanks,

Brendan

[Steven Horn]

I don’t know the preferred method or know what goes into various search method logic, but I would assume that “ssipp” would need to produce instances of Mississippi considering the way words are combined, truncated, camelHumped, and so on in file names, email addresses, urls, and more.

I apologize for being relatively new and naive to Tap Forms and for the expectations I bring coming into the arena. With Bento on the way out, you carry the torch.

[appledog]

I know sqlite does support things like ‘%ssipp%’ or ‘%sippi’. It would be nice if Tap Forms could support such wildcard searches. It does not appear to do so now. That’s a feature I’d like to see for more flexible searches.

[Brendan]

Hey guys,

The reason that searching doesn’t work for substrings is because the FTS search engine that Tap Forms uses does not support that. Yes I’m using SQLite and yes it does support wildcard searches such as ‘%ssipp%’. The problem is that those kinds of searches cannot use any form of an index and without an index, searches would be agonizingly slow. Depending on how many rows you have in your form of course. With the Full Text Search engine, text searches are very quick. However, it will only do a prefixed search because that’s how the index works. So you can search for “Miss”, but not “ssipp”. Technically Tap Forms is appending a * (asterisk) character to your search term in order to do the prefixed search. If you want to do an exact search, surround the term by double-quotes. You can also add OR and AND in between search terms, including adding parentheses around terms.

Thanks,

Brendan

[appledog]

Thanks Brendan,

Your explanation was helpful!

[David Oakley]

Investigating how all/any search terms work in TF5.
I see that the search term is ignored if i enter any ‘rules’ underneath. As I’m not familiar with the format of the search term, it would be nice if entering rules would show me in the box exactly what search term I am creating.
Alternatively, where can I find the ‘format’ or ‘grammar’ of a manually typed search term?

[Brendan]

For search terms just putting one word after the other will AND them together. If you want to search for either of the terms you specify, just put ” OR ” in between the terms. For Example:

Terminator OR Hunger

That’ll search for records that contain either Terminator or Hunger. So if you had The Terminator movie and The Hunger Games movie, you’d get 2 records in the result set. Without the OR, both terms would have to be in every record.

Are you looking to do more complex queries than that with the general search function?

[Tyson Schoeber]

The ability to add search terms such as OR or AND is great to know about! I’ll definitely be using that tip!!

Are there any other terms that work — such as NOT, for instance?

[Brendan]

Tap Forms uses the SQLite FTS Search engine, so it can use some of the special functions built-in to that. However, I don’t really like to publish that because I may change the search engine at some point and it may or may not continue to work with those keywords. But yes, there is a NOT function. So you could search “Terminator NOT Arnold” if you want to find all instances of the Terminator that does not also have Arnold within the same block of text.

For example, if you had a record with the text “Terminator starring Arnold Schwarzennegar” and another one with “Terminator starring Christian Bale”, then searching for “Terminator NOT Arnold” would find the Christian Bale version and not the Arnold version.

[David Oakley]

As far as I can tell, by trial and error, the following boolean search terms can be used:
NOT
AND
OR
Parentheses ( )
Quotes ” ”

They are evaluated in the order NOT, AND, OR unless this is modified by the use of parentheses.
If the AND is omitted, it is assumed.
I haven’t been able to establish whether the more exotic XOR and NOR work.
Brendan?

[Brendan]

Nope. No XOR or NOR is supported.

Here’s the docs on FTS in SQLite:

https://www.sqlite.org/fts3.html

[Tyson Schoeber]

This discussion has been very helpful but I have one more question about it. Would it be possible to add support for these kinds of searches to the Search field in the Pick List window? That window doesn’t seem to support such searches at the moment.

In the database I use in my classroom, I have a whole lot of saved comments for reuse in a Pick List. As is, I can search for single words to help find the text string I want but being able to refine those searches with AND or NOT would really help!

[Brendan]

The search in the Pick List popover is completely different. Because it’s not doing a database search. All the Pick List values are already in memory so it’s doing an in memory search using Apple’s NSPredicate class.

This is the code used for that entire function:

- (IBAction)searchValues:(id)sender {
	if ([[sender objectValue] length] == 0) {
		self.valuesArrayController.filterPredicate = nil;
	} else {
		NSPredicate *predicate = [NSPredicate predicateWithFormat:@"value contains[cd] %@", [sender objectValue]];
		self.valuesArrayController.filterPredicate = predicate;
		[self.valuesArrayController setSelectionIndex:0];
	}
	[self.aTableView reloadData];
}

Not much to do actually. The “value contains[cd] %@” bit there is what is doing the search. The [cd] means case insensitive and diacritic insensitive. Diacritics are the accents on characters, like umlauts and accent grave in French, etc.

[Tyson Schoeber]

In English please! ; )

[Brendan]

In English… I’m not using the SQLite FTS search engine for the Pick List searching as I am for searching for records. So that’s why you can’t use the AND, NOT, and OR functions.

[Tyson Schoeber]

Great!!! Thanks for the explanation! I now understand WHY that kind of searching is not currently possible! Yet your note also said “Not much to do actually” — and that indicates that it might be possible to add that sort of functionality in the future!

[JB Be]

I am new to Tap Forms and I have no knowledge of Java Script (I am basically coming from early FileMaker).

1. Is there a more comprehensive explanation available of search capabilities in Version 5.3 than the little explanation provided in the online ‘Manual’, which is just not enough. E.g is there finally regular Boolean search possible (NOT, AND, OR, Parentheses ( ), Quotes ” ”)?

2. How do I search in Version 5.3 e.g. records in which Value A may appear in field 1 OR value B may appear in field 2, but in no case Value C appears in field C.

3. Is there a way to avoid that after every advanced search, the search is saved and populates the side bar to the left?

[Brendan]

Hi JB,

1. When you create a Saved Search, the default is to use the AND operator between the fields you specify. That’s determined by the “All” or “Any” “of the following are true”, popup button on the Saved Search edit popover view.

2. You would want to use the “Any” operator for that. But you would also have to do a compound search. To do that on the Mac version, hold the option key down and click the ... button that appears. That’ll let you create nested search rules. However, be warned that this isn’t supported for editing on the iOS version. THat’s why I haven’t really advertised this feature. It works for executing the search on both macOS and iOS, but not for editing on iOS.

3. No. This is the nature of the way the searches work with the CouchbaseLite database engine I’m using right now. All searches turn into basically a materialized view in the database that is created when you click the Save button. And when you edit, add, or delete a record in that form, these saved searches get re-evaluated the moment you click on them to fetch the results.

Thanks,

Brendan

[JB Be]

Thank you for an incredibly swift response. Impressive!

To 1. above: This unfortunately doesn’t really reply to my question. is there a regular Boolean search possible (NOT, AND, OR, Parentheses ( ), Quotes ” ”)? The popup button on the Saved Search edit popover view only explains to AND and OR. Can I enter some advanced boolean query into the ‘Search term’ entry box? If yes, what would be the syntax for such advanced queries? What about truncated search (all instances that end on ‘….searchstring’? What about wildcards? Is there somewhere a comprehensive description of all search possibilities on the Mac (I have understood that the search functionalities on iOS are limited to some extent).

To 2. above: Ok, solves this issue. I am mostly interested in features on the Mac.

To 3. above: Understood. I am nevertheless unhappy with the way this works.

[Brendan]

Hi JB,

Well, Tap Forms uses the SQLite FTS search engine for the general searches (not the Saved Searches). So you can do things like “Terminator OR Hunger” to search for movies that include the word Terminator or movies that include the word Hunger. You can also put double-quotes around text you want to do an exact match for. So if you search for “Hunger Games”, that text must appear as is within any of the fields in your form.

You can search for prefixed values, but not substring values. So you can find Term, but not nator or min.

[JB Be]

Thank you, I see. How can I narrow general searches (not the Saved Searches) to specific fields only, e.g. “Eastwood” in field ‘actor’ only (not in field ‘Director’) and field ‘Year of release in Canada’ only after 1.1.2011 (not in field ‘Release in US’); i.e. what precise syntax to use in that case? Which wildcards are admitted? Extensive search possibilities are very important for my purposes; basically storing and swiftly retrieving imformation, not building a system for commercial use.

[Brendan]

Hi JB,

THere’s no way to target a specific field with the general search. Tap Forms just searches all text type and numeric type fields when you use the general search. To search specific fields, you must use the Saved Search function.

[Author]

Posts