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.
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
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.
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
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
Comments