Submitted by reesd on 2011/05/17 21:50
So I spent the evening trying to figure out "smart folders" (see Smart Fields), which I will call Query Equations for the rest of this post.
 
I'll start on the doc itself, it started as a blog post and I think it's time to update it :). I think we need to rework this page in the context of the equations pages and equations in general. Also, some of it duplicates concepts that are now covered better elsewhere like field/folders, grids, and wikitags, Also, many of the examples I think are better (or at least more easily) done as row equations, so I think we need to be clear why they would go the next level to use these.
 
In terms of terminology, smart folder/field now overlaps too much with all the other equations. Perhaps we could call these "Query Equations" to just ensure they are grouped with all the other ways that fields can get their values? "Query Fields" seems more correct to me, I like the common term of equation.
 
I also think that after we pull out the duplicate field/folder/grid stuff, we can then restructure to better focus what is really going on here. There is some important magic going on that needs to be explained better. I'm not sure I understand it so I can't just make the change, but let me guess and perhaps someone (Pierre?) can confirm/correct....
 
What I *think* is happening is:
 
Every field is represented internally by a "field view" - a sql view with the name _q<fieldname>. Each of these field views has two key columns - the ItemID and the field value (which has the same name as the field).
 
A grid source is then essentially a view that consists of all of its field's joined using ItemID. An actual grid is that source combined with a where clause based on its source bar filter and a sort by clause based on its source bar sort.
 
For normal fields the field view is a query against an underlying table with the actual values.
 
For query equations/fields we can replace this view with our own "query field view". This "query field view" can query against other field views and grid views any way it wants as long as it creates the two key columns - ItemID and <itemname>. Then it gets joined into a grid just like any normal view field view.
 
Am I close?
 
Also, there is a FieldID column used in the URl example that isn't explained. Maybe its something special to do with the URL table?
 
And do we want people messing with this? I know I want to mess with it, but should others :)?
 
d
 
 
 

Comments

Figuring this out got me to thinking, how can I use this for things that row equations don't solve?
 
One thing you can't do in row equations that is pretty normal in Excel or DB is lookups. Show me a value based on some values of my current row referenced against values elsewhere. I've often wanted to use a related item's value in my equation.
 
We can currently use a reference for pop-ups, and even pull several values as shown in -- link to nonexistent node ID 294 --. The pulled values really are not references though, they are copied. And they are only copied if you use the pop-up, changing the field directly loses the magic and you end up with unmatched data. That's what you get for copying data rather than referencing it (DRY).
 
So I plugged away and I came up with this alternative. The basic idea is you can create a query field that uses a value in the current row to look up a reference value from another grid.
 
Here is an example based on  -- link to nonexistent node ID 294 -- example, but using query fields rather than pop-up lists.
 
In the sample database we have a grid AdrsBook with the columns Item and Tel, where Item really is the contact name.
 
So now we can create a query field called MyContact that allows you to associate a contact with any other item. Like the linked example, you can setup a drop-down to pull its value from AdrsBook.
 
But you can go further and create query fields that show that item's values. For example, here is TelOfContact field that shows the Tel of the MyContact for your item. Not the item's Tel, the item's MyContact's Tel:
 
SELECT ¯qMyContacts.ItemID, AdrsBook.Tel AS TelOfContact
FROM AdrsBook INNER JOIN ¯qWorkSprint ON cstr(AdrsBook.Item) = cstr(¯qMyContacts.MyContacts)
WHERE AdrsBook.Tel IS NOT NULL
 
The cstr is needed because Items are apparently Memos and can't be joined (cstr chops them down to 255 chars). The IS NOT NULL is needed also, though I am not sure why. Must have to do with how Pierre is joining things.
 
Anyway, the end result is if you enter a MyContact in you will get it's Tel also.
 
I'm actually hoping to use this approach for probject planning. I have a Projects grid with Item as project name and a project due date. I've created a Project and DueDateOfMyProject query field in my Tasks grid. When I set the Project of a task I get its DueDateOfMyProject also. And I can use that for sorting and grouping. Nice actually.
 
My brain is now mush, but I wonder what other cases there are and if there is some general pattern here that can be pulled into IQ in a simpler way.
 
d