This post started off as a question in "How Do I?", then while writing it I found out the answer, but I figured I might still post something here, as it set off some thoughts as a new (and largely contented) user of IQ.
Something I'd puzzled over off and on for a while was how to set a criterion that shows all items where a checkbox is not checked. So, either in the Grid Source or in the Filter, I had tried:
- Is Not [check_box]
- Not [check_box]
- <> [check_box]
- [check_box] = 0
- [check_box] <> -1
These all got no results. What surprised me was that although [check_box] = 0 didn't return anything, [check_box] = -1 returned the items that are checked. How come -1 works and 0 doesn't?
Anyway, I eventually hit on the answer: you put [check_box] Is Null.
OK, that makes sense. I mean, it works. But how are you supposed to know that? I only arrived at it by exhausting all the other options I could think of (somebody else embarked on the same task might have hit on it straightaway. Or might never have). I use databases a lot at work, I know some SQL, and this background has definitely helped me get to grips, not just with IQ, but also with other PIMs like Zoot and Ultra Recall. But especially with IQ, which seems to me to be the most "database-like" of the three (I mean, they're all databases, but some more so than others, as far as the user is concerned). It wasn't of much use to me here though. To me, a yes/no field has one of two values: yes or no, -1 or 0. So it took me a while to get to the point of "Is Null".
So: there are some ragged edges here, perhaps. That's fine. I'm not arguing on the basis of some notion of how SQL should be expressed or accuracy in syntax or anything like that. Apart from anything else I'm no expert in this area, at all. It just made me think about the recurrent question of "how to explain IQ", why do people not get IQ etc. The responses usually seem to revolve round general concepts - explaining how items can appear in more than one grid, the importance of "views" of the data, of filters and criteria, etc etc. But for me, the points of frustration with IQ are not in this area. I get the principle of the thing - grids, fields, views, criteria. The frustration for me is always how to do some particular thing. I understand the idea of a filter, a criterion - but I need to know the syntax, especially in a programme like IQ where so much of the user's interaction is in the form of actual SQL, actual formulas and functions.
I know some SQL, VBS is not unknown to me, but I need to get a handle on them in an IQ context. To that end I've recently been going through the manual and the forum to find examples that can go into a kind of library of code snippets - put this into the Source Bar and it will do this. Same for equations, conditional formats, Options, pop-up lists etc. I really feel the need to gather this kind of stuff together before I can start using IQ to the full. Or, in fact, even do some quite basic things - like having a grid that shows items where a check box is not ticked.
I hasten to add none of this is going to stop me using IQ or anywhere near it! There's far too much functionality there (and to come) for that. Just a few thoughts prompted by (eventually) working something out.
Comments
hi Wayne,
I think SQL is useful generally, and it’s not too hard to pick up – what I feel I need to do is get a better idea of the places within IQ where SQL features and some of the typical things you can do with it. What I’m thinking of is going through locations within IQ like: Source Bar, pop-up lists, Filters, the Options field of the Field section (where you can create “smart fields” etc), and for each of them pulling together from the documentation and forum posts some of the typical things you can do there with SQL. What I really want to do is get a sense of IQ’s SQL – treating it as part of the IQ interface.
If I didn’t know any SQL, that’s probably the end I would start from – how it features within IQ, the things you can use it for, and then use some learning materials to figure out how those particular statements work.
To me, SQL is easier than a programming langauge like VBS. I know VBS a little, but not enough to dive straight in. And in fact I feel I can perfectly well use IQ without thinking about something called “SQL” at all, until I trip over the kind of example I mentioned in my initial post. And then I start to wonder about it and how to make it work. The VBS aspect of IQ is definitely something I'd like to get to grips with as well at some point. Again, with VBS, I feel it's not so much, "what can be done with VBS", but "what can be done with VBS within IQ."
-- DavidF.