Submitted by Murph on 2025/02/07 15:33

After searching through various forum posts, I believe the answer to this is "no", but I'd like to be sure.

I have a grid listing Next Actions and a separate grid listing Projects.  In the Next Action grid, I have a new field, ProjectID, which is the IDItem value of the Project that the Next Action is associated with.  I'd like create a third grid of Next Actions that pulls in their parent project's due dates based on the ProjectID field, so that on one line I can see both the due date for the next action AND the due date for its parent project.  That way, if I make any changes to a project's due dates, they would be automatically reflected in the third Next Action grid.  I tried creating a new field called ProjectDueDate with a SQL statement in the Field Properties Options to do just that with a JOIN statement, but it doesn't seem to be supported by IQ.  Can anyone please confirm that is the case?  If so, are their other options?  I've looked at possibly using related fields and tags, but neither of those tools is ideal.

I also know that I could create an outline with Next Actions listed as children items under Projects, but I strongly prefer the "top level item only" approach with this kind of data for various reasons.

Thanks.  I'm very new to IQ and looking forward to learning more.

Comments

Hi Murph,

This is most likely possible to do, but to be sure, can you share the two grids. Screenshots showing relevant field values.

Pierre_Admin
IQ Designer

Pierre,

Here is the first grid, which takes items from a Yes/No field called "IsProject".

Here is the second grid, which takes items from a Yes/No field called "IsNextAction".

To populate the fields in this grid, I created a field called "ProjectName". In its field property Editor, I selected "SQL query (advanced)" and added this statement: Select Item As ProjectName, IDItem As ProjectID, DueDate As ProjectDueDate from `IsProject` with the "Save all listed fields" checkbox checked.   That way, when I add an item to the IsNextAction grid, I can select a project from the ProjectName column (default caption = "Project Name") and I automatically populate the Project Name, ProjectDueDate and ProjectID fields.  

All of that works, however my issue is that if I edit one of the "IsProject" items, the edits don't carry over to the "IsNextAction" items.  What I tried to do was to key off of the Project ID with a SQL statement in the Option block "ProjectDueDate" field properties.  The statement that gives me an error is the following:  

<source>Select DueDate From `IsProject` Inner Join `IsNextAction` On `IsProject`.IDItem = `IsNextAction`.ProjectID</source>

Perhaps I am going about this all wrong?  

I was looking at the https://infoqubeim.com/drupal5/node/986 about what goes into the Options box in the Field Properties dialog.  I don't understand it much at all, but I notice that the table names following the FROM keyword aren't grid names.  Almost all of the table names start with a "overscore" character ( ‾ ), such as ¯Items or ¯fNumber.  Maybe these are tables that IQ uses internally.  I could use a tutorial breaking down these <source>SELECT</source> statements, explaining each of the components.   

Hi,

I could help you with that query (with the Join statement), but before doing that, have you considered a much simpler solution, namely to use the parent item as the project and sub-items as NextAction?

To carry the project name to the actions, you could use one of (1) use the ItemParent field, or (2) set the column inheritance for the project name field

Hello Pierre,

Yes, I am aware that with an outline it would be simpler to inherit properties from the parent.  I would very much prefer to avoid the outline approach.  If you are able to help with that query, it would be most appreciated!!

FWIW, I opened my IQ database in Access just to see how things look and to see if I could learn something about SQL statement syntax in IQ.  I'm definitely not a SQL pro but it was helpful to look at it.

Again, any help in that direction would be fantastic, thank you!

sure, I'll look at it on Monday. In the meantime, would you share why the outline approach doesn't suit your needs? Perhaps IQ has features which overcomes what you think is a problem with it

When it comes to tracking projects, next actions, someday/maybe lists, etc., my outline would have upwards of 1000 items.  My main complaint with dealing with such a large outline is the maintenance required to keep it current.  As I go through the day I may add 10-20 items and complete or delete another 10-20.  Having to constantly adjust the outline is tedious, IMO.  Yes, I know there are strategies to make the process easier, such as search features and creating various grids to aid in navigation, but the basic nature of an outline creates friction that can frustrate the process, especially when I am busy or not in the mood.  

In addition, outlines are less than ideal when it comes to the process of reviewing and updating.  I like having different views when looking at projects and tasks on a daily, weekly, and monthly basis and an outline is less than ideal for creating those views.

One thread that I found helpful was here:  https://infoqubeim.com/drupal5/node/4829  I watched the video that David_H made and liked his suggestions to Hilary.

Again, I'm new to IQ and as you suggest, perhaps there are features that I'm not familiar with.  Thanks again for all your help!

Did you happen to find the screenshots "iq2" "iq3" mentioned in DavidH's reply to Hillary?  Apparently they were lost in the migration to the new forum, but KeithB had saved them via httrack and somehow "attached" them to the thread but I don't see them anywhere.  Or maybe you just watched the video?

Hi Murph,

IQ can show / hide context parents and hierarchy to make it much easier than other outliners. Come to a training session if you wish to learn more how IQ manages hierarchies. 

That said, this retrieves the Project name from a Project ID:

<source>SELECT ¯qProjectID.ItemID, ¯fText.Value AS [Project Name]
FROM ¯qProjectID INNER JOIN ¯fText ON ¯qProjectID.ProjectID = ¯fText.ItemID  
WHERE (((¯fText.FieldID)=1));  </source>

 

I am very interested in the information Pierre has provided here - I wonder if it might open up possibilities for IQ that I was not previously aware of.  I am trying to get a good understanding of what Murph's question and Pierre's answer, which takes some doing for someone like me who doesn't have too much experience with working with SQL.  So as far as I can figure it out:

Murph has two grids, IsProject and IsNextAction.  The source field for IsProject is the Y/N field IsProject.  The source field for IsNextAction is the Y/N field IsNextAction.

The IsProject grid shows the following fields in columns: Item, DueDate, IDItem.

The IsNextAction shows the following fields in columns: Item, DueDate, ProjectName, ProjectDueDate, and ProjectID.

The IsNextAction items are intended to show the "next actions" for each project listed in the IsProject grid.

Murph wants some field values for each IsNextAction item to be drawn from the corresponding IsProject item.   Specifically - the "Project Name" field in the IsNextAction item needs to have the same value as the Item field in the corresponding IsProject item.  The "ProjectDueDate" field in the IsNextAction item needs to have the same value as the "DueDate" field in the corresponding IsProject item.  The "ProjectID" field in the IsNextAction item needs to have the same value as the "IDItem" field in the corresponding IsProject item.  I think the idea is for the IsProject item IDItem value to be entered manually into the IsNextAction item ProjectID field, to identify the Item from which the values will be fetched into the IsNextAction item's "Project Name" and "ProjectDueDate" fields.

Now how are these values to be fetched from the IsProject item into the IsNextAction item?  I think that this is done by a string entered into the Field Properties dialog Options box for each field that is to be so populated.

For the "Project Name" field, Pierre has provided the following string to be entered into that field's Option box:

<source>SELECT ¯qProjectID.ItemID, ¯fText.Value AS [Project Name]
FROM ¯qProjectID INNER JOIN ¯fText ON ¯qProjectID.ProjectID = ¯fText.ItemID  
WHERE (((¯fText.FieldID)=1));  </source>

I have been trying to figure out how IQ works with this string to populate the Project Name field.

The string seems to reveal how IQ internally stores the values that we enter into our IQ fields, in SQL database tables that we normally don't work with directly from the IQ user interface.  However, to populate the fields in the IsNextAction items as described above, we can use an SQL Select string in the Options box to work directly with those fields.  But to construct such a string properly, we have to understand how IQ stores field values in those internal tables.

It seems that IQ has an internal table that contains all the text field values for the database, and that the name of the table is ¯fText.

It seems that the ¯fText table contains at least the following fields/columns: ItemID, FieldID, and Value.

The "ItemID" field seems to specify a single IQ item, which we see in IQ as the "IDItem" field for any item.  It is a "Number" type field.

The "FieldID" field contains a number identifying a single IQ field.  We see it in IQ in the Field Properties dialog, in the "ID" column.

The "Value" field contains the text for a specific text field for a specific IQ item.

For example, the first item in Murph's IsProject grid shows an item with "IDItem" = 1858, and the Item text field contains the text "student consulting engagement with TCT".

So it seems that this particular text value is stored as follows in a single record/row in the ¯fText table:
The ItemID field contains the number 1858.
The FieldID field contains the number 1 - this is the Field ID for the Item field shown in IQ's Field Properties dialog.
The Value field contains the text ""student consulting engagement with TCT".

So this seems to be how the fields and values that we see in Murph's IsProject grid are stored internally by IQ.

Now I'll try to understand how IQ interally stores the fields and values that we see in Murph's IsNextAction grid.

Pierre's SELECT statement contains references to a table named "¯qProjectID".  I don't know what the "¯q" might indicate, but "ProjectID" corresponds to the ProjectID field/column shown in Murph's IsNextAction grid.

So I am thinking that for every field defined in IQ, there might be a corresponding internal table.  Since a "ProjectID" field has been created inside IQ, a "¯qProjectID" table has been created internally.

It seems that this "¯qProjectID" has at least the following fields/columns: ItemID and ProjectID.

I think "ItemID" specifies an IQ item that has a value for the ProjectID field.  In this case the item is one that is in the IsNextAction grid (IsNextAction=Yes).  We see this field in IQ as the "IDItem" field.

I think "ProjectID" - the value in the ProjectID field for that item.  In Murph's case, the value entered in the ProjectID field for the IsNextAction is the IDItem for the corresponding Project shown in the IsProject grid.

So with this understanding of IQ's internal tables and fields, we can understand what Pierre's <source> string is doing.

For every item containing a "ProjectID" field, the string links that item to the corresponding IsProject item, so that the "Project Name" can be drawn from that item.

This linkage is established by the string "¯qProjectID INNER JOIN ¯fText ON ¯qProjectID.ProjectID = ¯fText.ItemID  
WHERE (((¯fText.FieldID)=1))"

This string generates a results table containing fields from both the ¯qProjectID table and the ¯fText table, by means of the INNER JOIN keyword.  The string "ON ¯qProjectID.ProjectID = ¯fText.ItemID" finds the rows in these two tables where the ProjectID number in the ProjectID table (shown in the IsNextAction grid) is the same as the ItemID in the ¯fText table (shown in the IsProject grid).  The Project item shown in the IsProject grid will be the only IQ item that has that ItemID number.  So now we have linked the IsNextAction item to the appropriate IsProject item.   Now we can draw the "Item" field value from that IsProject item into the "Project Name" field for the "IsNextAction" item.

But we have to remember that the IsProject item may have multiple text values associated with it.  Each one of these multiple text values would be stored as a separate row in the ¯fText table.  Each of these rows would have a different value for the FieldID field, indicating each of the IQ text fields containing a value for that Item.  We need to make sure that fetch the text value from the correct row in the  ¯fText table.  This is accomplished by the WHERE clause:

WHERE (((¯fText.FieldID)=1))

The FieldID value of 1 indicates the Item field, as shown in the IQ Field Properties dialog.  This clause identifies the row with the correct ItemID AND the correct FieldID number.  It is from this row that the correct value for the "Project Name" will be drawn.

One thing i'm confused about are the parentheses in this WHERE clause.  I don't know why there any parentheses are required, or why three pairs might be required.

Now that we have the right row in the ¯fText table, we can now extract the value for the "Project Name" field.  So now we can go to the SELECT clause:

SELECT ¯qProjectID.ItemID, ¯fText.Value AS [Project Name]

This SELECT statement specifies two values to be supplied to the <source> string.  I'll address the second one first:

¯fText.Value AS [Project Name]

This string extracts the string value stored in Value field in the already specified row of the ¯fText table.   This is the value that IQ puts into the Project Name field for the item in the IsNextAction table.

So what is the purpose of the first column specified by the SELECT statement, namely:

¯qProjectID.ItemID

This item specifies the IsNextAction item for which the "Project Name" value will be filled in.  You might wonder why this is necessary.  I guess the <source> statement in the Options box would trigger the population of the Project Name field in every database item where the ProjectID number matches an ItemID stored in a row in the ¯fText table.  So maybe it is necessary to specify that the Project Name be filled with the appropriate value for each specific Item.

So, obviously I don't fully understand this <source> string, but at least I think I'm getting a pretty good idea about how to construct strings to pull values from one item's fields into another item's field, as Murph was trying to do.   A lot left to learn though.  I hope I have gotten the explanation mostly right - as far as what's going on "under the hood", I'm just guessing that from the how Pierre's <source> string is constructed.  So I could be very wrong about that.

One question that occurs to me - does the <source> string actually store a value into the database for the Project Name field?  Or is it like a "calculated field", where no value for the field is stored in the database, and the appropriate value is simply calculated via the JOIN as necessary?

I hope some other SQL newbie like myself might find this discussion helpful.

Hi Jim (aka Sherlock Holmes),

Great post, and mostly correct. I don't have the time right now to fully respond but IQ is centered on a small number of actual database tables and a larger number of SQL queries

Tables

  1. items
  2. fields 
  3. text values
  4. date values
  5. number values
  6. y/n values
  7. parent-child links
  8. related item links
  9. events
  10. item tags
  11. grids
  12. forms

Queries

  • Field values are SQL queries into one of the 4 data tables (in most cases). They must return the ItemID and a value. The value must be named the same as the field name
  • Calculated fields don't save anything in the IQBase. They are always calculated on-the-fly

Note

  • There is indeed an extra set of parathesis in the Where clause

HTH!

Pierre_Admin
IQ Designer

Hello Pierre, thank you very much for the help with the SQL statement ... it worked perfectly!

Jim, I feel your pain ...

It took me a while to digest Pierre's SQL statement and you are on the right track.  It helped me a great deal to open my IQ database in Microsoft Access to examine the tables and queries that Pierre described.  (Pierre recommended in another post that this would be a great way to learn how to construct SQL statements.)

The bulk of the user data in IQ are found in four tables:   ¯fText,  ¯fDate,  ¯fNumber, and ¯fYesNo.  The data are distributed among these tables based on the field type.  So dates are stored in the ¯fDate table, and so on.  Each of these four tables includes the following fields: ItemID, FieldID, Value, Modified, IDInherited and IDUser.  So if you enter a DueDate of 2/20/2025 for an item that happens to have an ID of 1875, then a record is added to the ¯fDate table with ItemID=1875, FieldID=80, and Value=2/20/2025.  (I don't know how the Modified, IDInherited and IDUser fields are populated by IQ and it isn't important to this exercise.)

Side note:  The symbol before the "f" is called a macron.  I don't have it on my keyboard but it is ASCII code 0175.  I'm sure there's a reason for it but it's also not important to this discussion.

In my example, I created a number field called "ProjectID".  In the bowels of the database, when I created that field, a query was created with the title ¯qProjectID.  In SQL, the ¯qProjectID query is "SELECT Value AS ProjectID, * FROM ¯fNumber WHERE (FieldID=241);"  So when I enter a value in the ProjectID field, a record is created in the ¯fNumber table that has the FieldID set to 241.  The Value of that record is the ProjectID.  If I create a grid where the source is ProjectID, IQ will look for all the records in the ¯fNumber table where FieldID=241, grab the ItemID's of those records, and then show me the text for each of those ItemIDs in the ¯fText table where FieldID=1.

Another rabbit trail:  To select a ProjectID for an IsNextAction item, I don't want to have to remember the item number associated with each project.  To solve this, in the Editor section of the Field Properties dialogue box for ProjectID, I selected "SQL query (advanced)" and entered Select IDItem As ProjectID, Item from `IsProject`   When I click on the ProjectID column for a particular item in my IsNextAction list, IQ shows me a list of the projects that I can select from:

When I select the project, the project's ProjectID value is poplulated in that field.  Initially, I thought about using the same trick to also populate the ProjectName and ProjectDueDate fields at the same time, which IQ could do.  To do that, my ProjectID Editor SQL statement would be   Select Item As ProjectName, IDItem As ProjectID, DueDate As ProjectDueDate from `IsProject`  and I would check the box "Save all listed fields".  That works great, but if I were to change a project's DueDate field later, then it wouldn't automatically update the ProjectDueDate associated with my next actions for that project.  That is what prompted me to ask the forum for help on this.

To apply Pierre's SQL code, I added his recommended text to the Options section of the field properties dialogue for my ProjectName field.  It becomes a read-only field and when I refresh my IsNextAction list, the ProjectName (caption="Project Name") appears.  If I change the name of the project in the IsProject grid, it is automatically changed in the IsNextAction list on refresh.  I created another field called ProjectDueDate and its SQL code in the Options section is the following:

<source>SELECT ¯qProjectID.ItemID, ¯fDate.Value AS [ProjectDueDate]
FROM ¯qProjectID INNER JOIN ¯fDate ON ¯qProjectID.ProjectID = ¯fDate.ItemID  
WHERE (((¯fDate.FieldID)=80));  </source>

Pierre, please correct any of my misunderstandings of your elegantly designed database, and again, thanks for all your help!!

 

 

Your description is perfect. I actually use MS Access to generate these specialized fields... as my understanding of SQL is good, but not great. It does the work for me

p.s. it is on the (long) list of things to improve to update smart fields automatically (that is, without requiring a grid refresh). Perhaps this discussion will be the incentive to do just that !

Thanks Pierre and Murph for your great explanations!  I'm looking forward to experimenting with all of this.  

I just noticed something regarding your query in Field Properties > Editor > SQL Query (Advanced):

Select Item As ProjectName, IDItem As ProjectID, DueDate As ProjectDueDate from `IsProject`

I see that the object of the FROM clause is 'IsProject', not one of the underlying tables or queries like ¯qProjectID or ¯fText.  So in this context at least, it seems that an IQ Grid can be referenced as if it were a database table in a SELECT statement.

In contrast, to accomplish the population of the "Project Name" field in the IsNextAction grid, we couldn't do a join between the IsProject and the IsNextAction grids - rather we had to join the underlying query ¯qProjectID with the underlying table ¯fText:

¯qProjectID INNER JOIN ¯fText ON ¯qProjectID.ProjectID = ¯fText.ItemID

I don't have any thoughts about that right now beyond this observation (it's beyond the CPU in my skull at the moment - I think it's an Intel Celeron and not a Core 9 Ultra.)

Jim,

I'm not sure why I can get away with the "shorthand" SQL query in the Editor box but it fails in the Options box, but it works.

Going back to your previous post ...

You asked why there is a need to have ¯qProjectID.ItemID after SELECT in the following query:

<source>SELECT ¯qProjectID.ItemID, ¯fText.Value AS [Project Name]
FROM ¯qProjectID INNER JOIN ¯fText ON ¯qProjectID.ProjectID = ¯fText.ItemID  
WHERE (((¯fText.FieldID)=1));  </source>

The query creates a lookup table with two columns:  the ProjectID and the ProjectName.  When ProjectName is added to a grid, it uses the query to look up the text of the ProjectName based on the ProjectID associated with each item in the grid.  Without the column created by ¯qProjectID.ItemID, it wouldn't be able to find which ProjectName goes with which item.  Hope that makes sense.

You had another question if the <source> string stores the ProjectName data in a table.  I don't believe it does.

FWIW, I stumbled onto this site to get simple explanations of SQL commands:  https://www.w3schools.com/sql/default.asp

 

Thanks Murph, I't's not easy for me to grasp some of this stuff.  I hope if I keep at it the light will go on.

The SELECT statement in this example returns two values - an ItemID and a ProjectName value.  i think the ItemID specifies which item will receive that ProjectName value.

This does seem to be consistent with the all the examples on the Smart Fields manual page - https://infoqubeim.com/drupal5/node/986 - I plan on studying those examples closely.

 

How do I ?