'does not contain' search criteria

[marquesroyallaume]

Dear Brendan
Love your database but was shocked to find out that I cannot use a ‘not equal to’ search function which is the main reason why I need a database to check who has not paid, what has not been paid for, etc. Is this function in the pipeline?

This is an essential part of any database for businesses.
By the way, I still think it is a great product and have purchased it three times and hope to purchase more once the update is made available. Great work in progress.
I presume that such an obvious function is in fact a problem with the programming limitations?
Also any chance of windows support.

[Brendan]

Hello Marques,

There is a technical limitation with a “does not contain” query unfortunately. Tap Forms uses the SQLite database engine and the FTS3 full text search engine to do its searching. Unfortunately the FTS3 search engine does not support a “does not contain” query. At least not all by itself. So you couldn’t specify something like “movie title does not contain ‘Terminator'”. But the engine would let you specify “movie title contains ‘Terminator’ AND NOT ‘Judgement Day’.

I had a detailed discussion about this with a customer recently, so I’m going to just paste that email into this reply:

I had originally wanted to put this functionality into the Search Rules system, but unfortunately the SQLite Full Text Search system doesn’t support that.

For example, this query would work just fine:

Terminator NOT Salvation

but:

NOT Salvation

would not work unfortunately. And that’s kind of the syntax that I need to use in my SQL queries.

It seems really crazy that this kind of thing wouldn’t work. I’ve done a bunch of research on it and that’s what I’ve found out. I just looked again hoping that the people who wrote SQLite’s FTS full text search engine would have enhanced it by now, but they haven’t.

There are certainly other ways that I can include this function, but it would be quite a slow query depending on the size of your database.

But I’ll keep looking for a solution to this problem because I know it would be very useful.

[DrJJWMac]

Out of curiosity, would ” ” NOT “Terminator” work to do a search on all fields not containing “Terminator”? The initial phrase is one single blank space.

—
JJW

[Brendan]

That was a good idea, but unfortunately it didn’t work. I just tested it. I tried a bunch of variations of that too.

[Brendan]

I have come up with a solution, but it’s not particularly quick. It’s much quicker to do a “contains” search than it is to do a “does not contain” search. But at least it’s working. I’ll see if I can optimize it further, but at least it brings back the correct results.

[Brendan]

The “does not contain” search option is available in the latest version of Tap Forms by the way.

Brendan

[bvision]

Brandan,

I see that Does Not Contain is available on my iPhone version, but not on the Mac version. Am I doing something wrong?

-Brad

[Brendan]

Hi Brad,

It will be available in the Mac version when I’m finished my latest round of development for Tap Forms Mac. When that will be exactly I can’t say at the moment.

Thanks,

Brendan

[Author]

Posts