Smart Fields 

Smart fields are used in a number of programs, such as Zoot and EverNote. They are sometimes also called Saved Searches as in Ultra Recall. Smart fields collect information about items without the user specifically doing it.
  
Examples of smart fields:
  • Day: tells the day for a specific date
  • ItemIsBold: collects all items that are bold
  • ItemIsRed
  • ItemHasHTML: tells if an item has HTML content
  • TasksForJohn
  • Date of a field with date and times
  • Date which combines 2 date fields
Smart fields typically take their information from:
  • UI related fields (ItemFont, ItemColor, etc)
  • Other fields that the user entered information
This means that you can extract and collect items using a huge variety of techniques:
  • Use any text, number, date field and collect items that have specific values (or value range)
  • Use tags inside the Item field (or another) such as:
    • If the item is: Talk to {joe} on project {somiro}, then a smart folder/field-type field can collect all items tagged as "Joe" or all items tagged "Somiro"
This gives users a simpler, quicker and "softer" coding method, than regular fields.
 
Currently, there is no UI specific to creating these smart folder-type fields, but users can easily modify existing examples:
 

1. Examples of Smart Fields

Let's say you want to have the day of the week for a given date field, so Jan 1st, 2010 would show as Fri (short for Friday)
 
IQ's sample database has a Day field which is linked to the Date field (a general purpose date field, also in IQ's sample database).
  1. Tools > Manage Fields dialog, and select the Day field
  2. Look at the Options section. You should see :
    <source>SELECT ItemID, Format([Date],"ddd") AS Day FROM ¯qDate;</source>
  3. The Format function is a standard VBScript function to format values. "ddd" is the code for the day of the week
 
What this does is to give the day of the week for a date that's inserted in the Date field.
 
Of course, you could simply change the Date field name for any other date type field name you'd need the day week for, and it would do it for that field. Just don't forget to also change the field reference at the end (to ¯qYourDateField)

Examples

  •  Year:
    • Type: Text
    • Options: <source>SELECT ItemID, Format([Date],"yyyy") AS Year FROM ¯qDate;</source>
  • Month:
    • Type: Text
    • Options: <source>SELECT ItemID, Format([Date],"mmmm") AS Month FROM ¯qDate;</source>
  • WeekNb:
    • Type: Number
    • Options: <source>SELECT ItemID, Format([Date],"ww",2,2) AS WeekNb FROM ¯qDate;</source>
    • Notes: Week number is not standardized. but a common definition is ISO 8601 - Wikipedia which the above example follows. There are 2 parameters:
      • 2: Week start on Monday, use 1 for Sunday, 3 for Tuesday, etc
      • 2: First week includes Jan 4th, use 1 to use Jan 1st 
  • DayOfWeek:
    • Creates a field showing the day of the week (Mon, Tue, etc)
    • Type: Text
    • Options: <source>SELECT ItemID, Format([Date],"ddd") AS DayOfWeek
       FROM ¯qDate;</source>
  • ItemIsBold:
    • Type: Yes/No
    • Options: <source>SELECT ItemID, [ItemID]>0 AS ItemIsBold FROM ¯qItemFont WHERE ((InStr([ItemFont],"|B|")<>0)); </source>
  • Strikethru
    • Type: Yes/No
    • Options: <source>SELECT ItemID, [ItemID]>0 AS Strikethru FROM ¯qItemFont WHERE ((InStr([ItemFont],"|S|")<>0)); </source>
  • ProjectSomiro:
    • Type: Yes/No
    • Options: <source>SELECT ItemID, [ItemID]>0 AS ProjectSomiro FROM ¯qItem WHERE ((InStr([Item],"{Somiro}")<>0)); </source>
  • Dates without times

    Say you have a date field which also contains times. An example would be the field used for the Calendar (ApptDate). You may want to hide the time part. You have at least 2 ways to achieve this:
    • <format> in the Options section of the field management. Details in -- link to nonexistent node ID 1074 --
    • You can create a new field, say YourDate, which would be based on the YourDateTime one:
      • Name: YourDate
      • Type: Date
      • Options: <source>SELECT ItemID, int(YourDateTime) as YourDate FROM ¯qYourDateTime; </source>
      • Make sure you substitute all instances of YourDateTime and YourDate with your own field names!
  •  Date that combines 2 date fields

    In this case, the user wanted to sort on the created date of an item, but at times, that date was not appropriate for the sort. It was proposed to copy the created date to another date field and sort on this new field (ItemCreated is read-only).
    A more elegant solution uses smart fields. Create a date field to overwrite the ItemCreated value (this example uses the field MyItemCreated)
    • Create a new field:
      • Name: MyItemCreated
      • Type: Date
    • Create a new field:
      • Name: SortingDate
      • Type: Date
      • Options: <source>SELECT ¯qItemCreated.ItemID, IIf(IsNull([MyItemCreated]),[ItemCreated],[MyItemCreated]) AS [SortingDate]
        FROM ¯qItemCreated LEFT JOIN ¯qMyItemCreated ON ¯qItemCreated.ItemID = ¯qMyItemCreated.ItemID; </source>
    • This way, the SortingDate will display the item created date unless the user enter something in MyItemCreated
       
  • Text that combine 2 text fields

    A user had entered contacts first and last names in separate fields and wanted to see these in a single column (i.e. in a single field). A row equation could be used and is certainly a good solution.
    A smart field could also do the job:
    • 3 text fields: Fname, Lname, ContactName
    • In the Options section of the field ContactName, enter: SELECT ¯qFname.ItemID, [Fname] & " " & [Lname] AS ContactName
    • FROM ¯qFname LEFT JOIN ¯qLname ON ¯qFname.ItemID = ¯qLname.ItemID;
    • n.b. The recommended approach to contact management is to enter both first and last names in the item field (i.e the main text field)
       
  • Hyperlink for the URL field

    The URL field can contain a URL. Sometimes, one wants to hide the actual link and show some text. To do so:
    • Create a new field:
      • Name: URLLink
      • Type: Text
      • Options: <source>SELECT IIf([URL] Is Null,"","<A href='" & [URL] & "'>Link</A>") AS URLLink, ¯qURL.ItemID FROM ¯qURL WHERE ((¯qURL.URL) Is Not Null); </source>
    • The result gives:

       
  • Main Parent ID
    • name: IDMainParent
    • Type: Number
    • Options: <Source>SELECT ¯Items.ParentID AS IDMainParent, ¯Items.ID AS ItemID FROM ¯Items;</Source>
       
  • Grandparent ID
    • name: IDGParent
    • Type: Number
    • Options: <Source>SELECT ¯Items_1.ID AS ItemID, ¯Items.ParentID AS IDGParent
      FROM ¯Items AS ¯Items_1 INNER JOIN ¯Items ON ¯Items_1.ParentID = ¯Items.ID;</Source>
       
  • Calendar Name

    When an item is shown in the Calendar (i.e. it is an event), this field will display the corresponding date field name:
    • Name: CalendarName
    • Type: Text
    • Options: 
    • <source>SELECT Event.ItemID, ¯Fields.Name AS CalendarName FROM Event INNER JOIN ¯Fields ON Event.FieldID = ¯Fields.ID;</source
       
  • Event Description

    Events have a number of parameters which are not immediately available as field (to be displayed in a grid column for example). They can nevertheless be viewed (but not edited) using a smart field. Here we'll show the event description:
    • Name: EventDescr
    • Type: Text
    • Options: 
    • <source>SELECT ItemID, Body AS EventDescr FROM Event WHERE ((Body<>"") AND (Deleted=False));</source>
  • Item is Repeating

    Item can have repeating events. This smart field will show this information:
    • Name: ItemIsRepeating
    • Type: Y/N
    • Options: 
    • <source>SELECT DISTINCT [ItemIsRepeating] AS [Value], -1 AS [ItemIsRepeating], [ItemID] FROM Event WHERE ((recurrence<>"") AND (ItemID<>0));</source>
  • Items that are only in the Inbox

    This smart field counts the number of Y/N values each item has. This can be used to find items that have only one and the one being Inbox.
    Of course grids can have complex sources, but many simply use a Y/N field, so this will work:
    In a grid (perhaps a new one), set the source to [ItemYNCount]=1 and [inbox]
    • Name: ItemYNCount
    • Type: Number
    • Properties: Check both Hide in... checkboxes
    • Options: 
    • <source>SELECT ItemID, Count(ID) AS ItemYNCount FROM ¯fYesNo GROUP BY ItemID;</source> 
    • Another option is to use the Inbox grid and set the source filter to [ItemYNCount]=1
      Best would be to set the grid display mode to Flat list with context parents Off
  • Count of Item Tags for each tag

    Tags can be shown and managed in the Tags pane. For some more advanced features, users can also use the Tags grid to manage tags. However, this grid cannot show the Item Tag count for each tag. This smart field will do just that:
    • Name: TagItemTagCount
    • Type: Number
    • Properties: Check both Hide in... checkboxes
    • Options: 
    • <source>SELECT Count(¯Tags.ItemID) AS TagItemTagCount, ¯fNumber.ItemID
      FROM ¯fNumber INNER JOIN ¯Tags ON ¯fNumber.Value = ¯Tags.TagID
      GROUP BY ¯fNumber.FieldID, ¯Tags.TagID, ¯fNumber.ItemID
      HAVING (((¯fNumber.FieldID)=47));
      </source>       
    • Warning: The impact of creating this field is not known at this time. So it is safer to delete the field when no longer require 
  • Count of Item Values

    Values are of 4 types in IQ. We need to create a field for each field type and a last field which sum the number of values for the 4 types:
    • ItemDateCount
      • Name: ItemDateCount
      • Type: Number
      • Properties: Check both Hide in... checkboxes
      • Options: 
      • <source>SELECT ¯Items.ID AS ItemID, Count(¯fDate.ItemID) AS ItemDateCount
        FROM ¯Items LEFT JOIN ¯fDate ON ¯Items.ID = ¯fDate.ItemID
        WHERE FieldID is not null
        GROUP BY ¯Items.ID;
        </source>         
    • ItemNumberCount
      • Name: ItemNumberCount
      • Type: Number
      • Properties: Check both Hide in... checkboxes
      • Options: 
      • <source>SELECT ¯Items.ID AS ItemID, Count(¯fNumber.ItemID) AS ItemNumberCount
        FROM ¯Items LEFT JOIN ¯fNumber ON ¯Items.ID = ¯fNumber.ItemID
        WHERE ((¯fNumber.FieldID Not In (19,27,30,29,28,39,56)))
        GROUP BY ¯Items.ID;</source>         
    • ItemTextCount
      • Name: ItemTextCount
      • Type: Number
      • Properties: Check both Hide in... checkboxes
      • Options: 
      • <source>SELECT ¯Items.ID AS ItemID, Count(¯fText.ItemID) AS ItemTextCount
        FROM ¯Items LEFT JOIN ¯fText ON ¯Items.ID = ¯fText.ItemID
        WHERE (((¯fText.FieldID) Not In (6,16,18,21,55,82)))
        GROUP BY ¯Items.ID;</source>         
    • ItemYNCount
      • Name: ItemYNCount
      • Type: Number
      • Properties: Check both Hide in... checkboxes
      • Options: 
      • <source>SELECT ItemID, Count(ID) AS ItemYNCount FROM ¯fYesNo GROUP BY ItemID;</source>
    • NbValues
      • Name: NbValues
      • Type: Number
      • Properties: Check both Hide in... checkboxes
      • Options: 

      • <source>SELECT ¯Items.ID AS ItemID, [n1]+[n2]+[n3]+[n4] AS NbValues, IIf(IsNull([ItemNumberCount]),0,[ItemNumberCount]) AS N1, IIf(IsNull([ItemTextCount]),0,[ItemTextCount]) AS N2, IIf(IsNull([ItemYNCount]),0,[ItemYNCount]) AS N3, IIf(IsNull([ItemDateCount]),0,[ItemDateCount]) AS N4
        FROM (((¯Items LEFT JOIN ¯qItemDateCount ON ¯Items.ID = ¯qItemDateCount.ItemID) LEFT JOIN ¯qItemNumberCount ON ¯Items.ID = ¯qItemNumberCount.ItemID) LEFT JOIN ¯qItemTextCount ON ¯Items.ID = ¯qItemTextCount.ItemID) LEFT JOIN ¯qItemYNCount ON ¯Items.ID = ¯qItemYNCount.ItemID;</source>

2. Notes:

  • It is important that the field name be the same as the AS xxx part of the SQL clause ("AS ItemIsBold" and "AS ProjectSomiro" in the above 2 examples)
  • You can create grids to show items in these smart fields, use them in filters and in sorting, just like any other field.
  • If you want, you can hide these specialized field in sections of the properties pane (so it doesn't get too crowded, if you do so, you can still show the field in forms and in grids). In Field Properties:
    • Hide in Info. Pane
    • Hide in Available fields
  • You don't need to use curly brackets if you don't want to, any delimiter will do, even no delimiter, as this system is entirely into your hands
  • These smart fields, take no database space at all and is automatically updated
  • Changes to underlying values (other fields, events, etc) will not update values shown in grids. Press F5 to refresh a grid