Saved searches

[Wolfgang]

Hi there

I recently bought Tap Forms. So far it meets our needs. The only area I find really lacking is saving searches.
Once you saved a search, there is a 50% chance you can edit it. Some searches are editable, some are not. I haven’t found a pattern to this yet.
This leads to deleting a search and start all over again. Since you can not arrange or sort the saved searches, things rather quickly become cluttered.
By the way – the interface for defining searches could use some rethinking. You have to type something in the search field, then it gets visible. But that’s maybe just me :)

Thanks

Wolfgang

[Brendan]

Hello Wolfgang,

I’m assuming you’re referring to the Mac version? When you select a search underneath the form, the Edit button at the bottom should let you edit the saved search. You’re right that it’s a bit odd that you have to type something into the search field to bring up the search, but it was done this way in the Finder and that’s what I modelled that code after. But I think it would be better with another interface.

Thanks,

Brendan

[Wolfgang]

Hello Brendan

Yes – I’m referring to the Mac version :)
I was aware of the Edit button. What I tried to explain is that it only works for some searches. There are some I can edit afterwards, and some that I can’t. I haven’t found a pattern to this, but it’s either “works always” or “works never”. So I have to delete them and start from scratch.
Hope this helps.

P.S. I wasn’t aware that Finder works the same way – you’re right…

Wolfgang

[Brendan]

Are some of your searches multi-field searches and some are just global text searches? I wonder if that’s the difference.

[Linus]

I’m having trouble with saved searches as well. Whenever I enter dummy text in the search box to bring up the filter controls, that text insists on being part of the search. Sometimes this is fine – searching globally for a string does not hurt if the same string (or one containing it) is also used in a field-based search – but it really hurts when I’m trying to create a search based exclusively on boolean values (checkbox fields). It seems that there is no way to avoid this from happening since deleting the dummy text before hitting “Save” just cancels the whole operation.

I’m also confused when it comes to editing searches. I can add criteria, but those already added are hidden from view.

Linus

[Linus]

Today I noted a strange glitch that may or may not have something to do with this. I recently added a checkbox column to a table that already had about 1000 records. Now it seems that each row has received a random value rather than defaulting to false. I added another checkbox column and noticed that the value in the previous one were copied to the new one. This only applies to the table view; when viewing individual records in form view, the checkboxes seem unticked.

[Linus]

To further add to the confusion, the glitch is intermittent. Values seem to be consistent over time in form view, but vary inexplicably in table view. When starting a global search by entering dummy text, all checkboxes visible in the table view are checked. Occasionally they are checked and greyed out. Hope this helps.

[Brendan]

Hi Linus,

There is a bug with checkboxes in the table view. I have fixed it for the next update. It happened because rows are re-used when you scroll through the list and I neglected to clear out the previous checkmark values each time the row is displayed.

Also, I’m thinking of just having a button you click on to bring up the saved search feature rather than you having to type in a search term first. I modelled it after the way the Finder does its search, but after hearing of this issue, it seems that the interface can be improved.

The iOS version works different of course, so you could create your searches there and then iCloud Sync them over to your Mac. Not ideal, but a workaround.

Thanks,

Brendan

[Linus]

Dear Brendan,

I’m glad to hear that you’re working on these issues.

Yes, I suspected that the table view glitch was more of an UI issue and thus unrelated to my original trouble with saved searches, but I thought I’d mention it anyway – you never know with complicated systems.

It also seems that I jumped to conclusions regarding the nature of the search problem of rows being unduly excluded from the result set. (Just like patients tend to theorise about what ails them, I cannot as an end user resist the temptation to theorise about system logic.) My original finding was that searching for ArbitraryCheckbox=false yielded a result set which was a subset of what I had expected. I thought this was because there was some additional hidden criterion in play. I no longer think this is the case. If I “touch” the check box of a record – check it and then uncheck it – it promptly shows up in the result. Is it possible that when adding a checkbox column, existing records get a value which is neither true nor false? This would explain what just happened.

[Brendan]

Hello Linus,

You’re correct. When you add a check mark field type to a form after the records have been created, the value of the checkbox is in an “unset” state. Checking it then unchecking it will put it into a OFF/FALSE/NO state. The value is there, but it’s just OFF/FALSE/NO. In the initial state, it’s just not there at all. But when you create new records, the new records will have their checkmarks there, but in the OFF/FALSE/NO state by default. This is an anomaly due to the way I store data in the database. Each value gets stored in its own row in the database. Adding a new field doesn’t just add a new column to a table. It adds a new Field row to the Fields table. And a Form has a to-many relationship to its Fields.

One of the main reasons why I designed it this way was to work around a limitation of SQLite. For some reason unbeknownst to me, the designers of SQLite decided that it’s impossible to delete columns from a table. Every other database system I know of lets you do that. So I couldn’t just add a new column to a table because there would be no way to delete it if you chose to do that. So that’s why fields and field values are stored in rows of a table.

Probably more info that you wanted to know :-)

Thanks,

Brendan

[Linus]

Dear Brendan,

Thank you for your thorough explanation. I’ve always found that knowing more about a system makes me more efficient as a user, so I appreciate it. Now I know how to work around these “anomalies” to get the behaviour I’m after.

Hearing about the SQLite feature set makes me … sad.

Best,
Linus

[Brendan]

Ya, I’ve been doing some research on the drop column functionality that doesn’t exist and I still couldn’t find any really good explanation as to why it’s not there. People say that you should just copy every row to a new table with fewer columns, then drop the original table. A very clumsy and time consuming operation that also consumes a lot of disk space during the operation, especially for a mobile device.

Having said that, I have some ideas how I could accomplish what I want. It would take a conversion process though to migrate data from the row-based format I use now to a column-based format. But it would also require that for every field in your form, I’d need 3 columns in a table. One for the data, one for columnX_date_created, and one for columnX_date_modified. This is to facilitate iCloud sync.

You also can’t rename columns either. So I’d have to map which column is being used in the table to the field in the form. So when you create a form with fields, I’d probably end up creating a table with the name based on the primary key of the form and with one column for each field. E.g. column_0, column_1, column_2, etc. Then if you delete a field, I could just clear out the values from the column it was referencing and re-use that column later for another field if you were to add another field. But then the table could end up growing and growing. Depending on whether that caused a performance or disk space issue, perhaps rewriting the table to a temporary table with less columns, then copying all the data over and then dropping the original table, then renaming the temporary table back to the form’s table.

Anyway, even more details here, but it’s good for me to think through these things “out loud” sometimes :-)

[Linus]

Yeah, I know what you mean. Since we are already discussing implementation here, I’ll give you my thoughts on the matter. Mind you, I’m not really an Objective-C programmer (I’m more of a Java guy) and I’m an SQL beginner as well …

Personally, I’d start with the clumsy and time consuming operation (copy table to drop column) just to keep the data model as simple as possible (KISS rule FTW!) and then let extensive testing tell whether I need to worry about performance. If it turns out that I do, I’d add a shadow table containing the names of all columns the user’s decided to drop; I could then tell the UI not to look for them. If the table grows too big, it could be purged through the copying mechanism. I think most users would not mind occasionally waiting while the database is “optimising”. My pardon if you’ve already been there, done that.

Implementing column renaming by keeping a separate table where user-defined column names are mapped to their “true” counterparts makes sense. I wasn’t aware of the need to keep extra columns to facilitate iCloud syncing, but it does explain the granularity of the process (much appreciated, by the way).

Best regards,
Linus

[Brendan]

I already have a Field table which contains a row for each field defined in the Form. So that can be used for the mapping. I’d just need to add a column to indicate what the column number mapping was into the Records table. The Records table being the table that contains all the data.

Right now I have a FormEntry table which contains the records, but only a subset of the data is stored there. Just for the purposes of sorting and grouping. So it stores the first, second, and third sort fields data plus the group field’s data. It was much quicker to sort records this way than to drill down into my FieldValue table but it does require duplication of data.

When you click on a record, I fetch the rest of the data from the FieldValue table. One row for each field in the form for the selected record. It would be faster if I already had the data of course.

But on top of all this I’d have to modify the way the search index worked too. The search index currently mirrors the way the FieldValue table works. In fact, it’s called field_value_fts3. But I’d probably have to make it mirror the new way of storing data.

The tricky part of all this is maintaining backwards compatibility. If you made a backup before the new way, then wanted to restore that backup, the app would still have to work. So I’d have to have two implementations. Maybe I’d have to store on the Form which implementation is being used. Something like a version number.

Anyway, thanks for prompting this discussion. It’s great to work things out like this.

[Linus]

I’m only glad to help.

I found another bug in saved search (iPhone, iPad and Mac editions). I have one record that shows up in checkbox=true searches no matter the actual field value. When the value is set to false, it shows up in both checkbox=true and checkbox=false searches; when set to true, it only shows up in the checkbox=true search. Any ideas?

[Linus]

It happened again! Last time, I had no choice but to remove the offending record. Now another record with a checkbox set to false shows up regardless of what filter I use. Brendan, have you been able to reproduce this?

Best,
Linus

[Author]

Posts