Search for blank fields

[ArsAstronautica]

I was going to update some records I have in one of my databases and the easiest way of locating which records needed updating was to search for an particular field being empty. I ca select the field, of course, but there doesn’t seem to be a way to search for empty/blank fields. Am I missing sometihng here?

[Brendan]

Hi Ars,

Nope, you’re not missing anything. Tap Forms is missing that feature. It’s a heck of a lot slower to search for missing data than for data to exist because the missing data is not in the search index. But that’s definitely something I’d like to add as it makes a lot of sense.

Thanks,

Brendan

[ArsAstronautica]

Thanks for the reply. What I did was to build a rather longish search that omits records that contain various words. I was trying to exclude as many of the non-empty records as possible. Ugly, but it works for now. In the process I have a suggestion, noted a quirk, and found a bug.

Suggestion: In addition to the matching/not matching options, add an “is empty”. I think that would be useful to hunt down missing information.

Quirk: I noticed that only whole words are indexed? At least it looks that way. Searching for say the letter ‘a’ will only pick up the word and not all the records that have an a somewhere in some word, correct? I also noticed that punctuation is ignored. Looking for sentences that have periods on the end, for example, turned up empty. This isn’t a feature request, just making sure I know what is/is not searchable.

Bug: This is in the Mac version running Mavericks. I set up the search criteria on the iPad. It was composed of perhaps half a dozen “if record does not contain”. When I synced, the search criteria is OK on the iPad & iPhone, but the Mac version is broken. It fails to exclude all the records excluded on the iDevs. Trying to edit the search brings up an empty set of criteria, though oddly is DOES exclude some of the content.

[Brendan]

The Mac version is currently missing the does not contain comparison as that’s something I added to the iOS version after Tap Forms Mac 1.5 came out. I’ve added it to the current Mac 2.0 update.

I’m using the SQLite FTS4 search engine if you want to see what capabilities it has:

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

(warning: very technical data here, but has some good info on what you can search for). There are also nuances in the way that I’m using the data. For example, each search rule you add, tells Tap Forms to perform an intersection of the search results. And you can also do things like search a specific field for terms like Movie Title contains “Terminator OR Terminal”. Stuff like that. Note the upper case OR. It’s required in that situation.

So yes, punctuation is ignored specifically because it’s used (along with spaces) to separate words. And yes, you’re right, it’s only indexing whole words. FTS3/4 doesn’t allow searching for substrings.

Thanks!

Brendan

[Author]

Posts