Submitted by David_H on 2015/01/22 05:38
That is, items that don't meet the criteria to belong to any grid?
 
Thanks

Comments

Orphans don't really exist in IQ... items exist, period
Namely the journal grid can show any item, simply set the date filter. If you want a flat list, turn off hierarchy and context parents.
 

David_H

2015/12/11 18:39

In reply to by Pierre_Admin

Let me ask this another way.  There have been occasions where for one reason or another items loses values that were meant to be associated with them and while they may not technically be "orphans", for practical purposes they are.  Is there any way to search for items and have the search return all items that have NO value OTHER than "Item"?  That is what I'd like to be able to do.
 
Thanks

Pierre_Admin

2015/12/11 23:33

In reply to by David_H

Not quite what you want, but this will get you part way...
Set the source of a grid to:
DateFields is null and numberfields is null and binaryfields is null and (item is not null)
 
Turn hierarchy and context parents off. This will list you all items that only have text fields. i.e. no dates, no number and no yes/no values
 
If that is not enough, we could get exactly what you want using a smart field (see Smart Fields (aka smart folders))
 
 

Pierre_Admin

2015/12/12 17:08

In reply to by David_H

The problem is actually a little more complex as it is not sufficient to find items that have just a value for the Items field, but also, the item must not have a parent...
 
So... one must also add "and nbParents=0"
 
or, add the nbParents field to your grid and sort / filter to find those that have no parents
 
HTH !
 

David_H

2015/12/13 00:22

In reply to by Pierre_Admin

OK, that last change certainly made a big difference and helped me narrow things to a smaller set up items, I was about to post asking you if I was doing something wrong.
 
1. Correct me if I'm wrong, but all items with a text field of ANY type are still going to show up, as long as the other criteria are met, correct?  So if I have a grid with 500 items without parents and a text field of "category", those will all show up, correct?
 
2. Is there a way to instead express this as "items where the only value is text in the item field"?
 
3. In the Search grid there is a column titled "shown in".  Is that not a field available to a user to insert into a grid?  I cannot find it, at least not under that name.  I would like to add it to the grid I have created for this.  If I could, than I could just sort on that to quickly find items where these is nothing in that field/column.
 
Thanks Pierre.

OK, I got the final answer on this. It uses 2 smart fields
 
First create a number field, name it CountText with this in the Options textbox:
<SOURCE>SELECT ¯Items.ID AS ItemID, Count(¯qTextFields.ID) AS CountText FROM ¯Items 
LEFT JOIN ¯qTextFields ON ¯Items.ID = ¯qTextFields.ItemID 
GROUP BY ¯Items.ID;
</SOURCE>
 
This calculates the number of text field values
 
Next, create a yes/no field, name it Orphans with this in the Options text box:
<SOURCE>SELECT DISTINCT ¯Items.ID AS ItemID, -1 AS Orphans, ¯qCountText.CountText 
FROM (((((¯Items LEFT JOIN ¯qDateFields ON ¯Items.ID = ¯qDateFields.ItemID) 
LEFT JOIN ¯qnumberfields ON ¯Items.ID = ¯qnumberfields.ItemID) 
LEFT JOIN ¯qbinaryfields ON ¯Items.ID = ¯qbinaryfields.ItemID) 
INNER JOIN ¯qitem ON ¯Items.ID = ¯qitem.ItemID) 
INNER JOIN ¯qnbparents ON ¯Items.ID = ¯qnbparents.ItemID) 
INNER JOIN ¯qCountText ON ¯Items.ID = ¯qCountText.ItemID 
WHERE (((¯qDateFields.DateFields) Is Null) AND ((¯qnumberfields.NumberFields) Is Null) 
AND ((¯qbinaryfields.BinaryFields) Is Null) AND ((¯qitem.Item) Is Not Null) 
AND ((¯qnbparents.nbParents)=0) AND ((¯qCountText.CountText)=1));
</SOURCE>
 
Create a new grid, name it Orphans, it should automatically use the newly created Orphans as its source.
Make sure hierarchy and context parent are turned off. Then all shown items will be orphans. They may have sub-items of course...
 
These fields are quite CPU intensive, for IQBases with many items, so I recomment hiding them from the Properties Pane. In field properties, check Hide in the Info. Pane and Hide in Available fields
 
That said, I don't consider these items as "orphans"... they are just "regular" items, sitting in your IQBase, that can be seen using the Journal grid or by doing a search. 
 
In v63, I'll add "count" fields for each of the 4 field types
 
HTH !
 
Pierre_Admin
 

Armando

2015/12/13 22:54

In reply to by Pierre_Admin

interesting, thanks!
-------------------------------------------------------
Windows 8.1
Sony Vaio S Series 13 (SVS131E21L)
Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

Pierre_Admin

2015/12/14 10:43

In reply to by Pierre_Admin

On second thought, the smart field solution is probably not the best. The impact of having such a field on performance will, in the long term, be significant.
 
Instead, keep the CountText smart field, but delete the Orphans field. Next, set the source of a grid to:
DateFields is null and numberfields is null and binaryfields is null and (item is not null) and nbparents=0 and CountText=1
 
[edit] Having no children is perhaps also something that you want to test, so you can also use this source for that: 
DateFields is null and numberfields is null and binaryfields is null and (item is not null) and nbparents=0 and  CountText=1 and nbchildren=0