Sort Parents By Number of Children
If this comes across as me being lazy then it's probably not far from the truth. Some times I just don't have the patience or time to sieve through the forums.
I am trying to sort documents in a view based on the number of responses they have. Sounds easy enough, but having a hard time figuring it out. Whenever I work with views that show responses it's always a case of re-learning the ancient art of "special text" and response-only columns. Nothing could be less intuitive.
Imagine on the codestore homepage I want a section that shows most popular blog entries, based on the number of comments posted to them. Mike Golding used to have one on Notestips.com and I'd always assumed it was simply a view. Having tried to reproduce it I'm starting to think there must have been an agent involved. I'd take a look myself but I'm still waiting on Mike to send over the NSF so I can host the old site for him.
Is the only way of doing it to have some code (WQS of the child document) update a numeric "NumberOfResponses" field on the parent each time a response is added? Or can a view do it?
As much as I hate asking noddy questions like this I always do it in the hope other people will benefit from the answer.
Skip to the 
You can subscribe to an individual
Assuming you want to just use an embedded view, and not mess around with javascript to do the sorting client-side, then yes, I'd go a route similar to what you outlined.
Some years ago I built some functions to deal with responses and responses of responses. You need to do this in a function using doc.responses and call this function iteratively. You could run this in an agent via AJAX.But I think it will be fastest by any means to use a counter in some place (the parent doc or maybe another doc in order to prevent save conflicts) and not to do the computation on the fly in the view or in an agent.
The only method I can remember was to update the parent document every time a child was saved. We used to add the child UNID to a multi-value field in the parent, and then use @Elements to give us the number in a view. High potential for save-conflicts but relatively easy to implement.
This time I actually did think I was just being stupid and that it would be easily possible with a view.
Thanks for confirming guys!
It all leaves me dreaming of SQL...
Don't forget about the DescendantCount and ChildCount properties of the NotesViewEntry. This can save you some time if all you want is the number (integer) of responses.
create a categorised view using the parent UNID as a categorised first column, then transform the view using a XML source url of something like
Report!readviewentries&start=1&Count=1000&Collapse=1#1
and use an .xsl file to read and display the @descendants value from the resulting view
eg ( snipet )
<xsl:for-each select="viewentries/viewentry">
<tr>
<td><xsl:value-of select="@position" /></td>
<td><xsl:value-of select="entrydata[@columnnumber=number(0)]" /></td>
<td><xsl:value-of select="@descendants" /></td>
Hope it help !
andy
*shudder*
Sorry but updating docs and WQS agents are recipes for nightmares. The XML might do it, but if you want this in a form design to give the links try this. A simple piece of @Formula can do the trick.
Part 1.
Get your list of keywords topics for every document. ie txtlist:="Crud" : "Domino" : "Domino" : "Stuff" : "Domino" : "Stuff" : "More Crud"
total:=@Elements(txtlist);
uniquelist:=@Unique(txtlist);
val:=@Transform(uniquelist; "x"; total-@Elements(@Trim(@Replace(txtlist; x; ""))));
val now contains a set of values based on your Uniqued entries. Of course you might have a dbColumn setup which can skip this step.
Part 2.
Now we have the list and totals, we need to sort them. Find the highest numebred entry, it's position in the list, then add that to the sorted entries and set the value in the list to 0. Rinse and repeat for all entries.
tmpval:=@Text(Val);
tmpsorted:="";
elems:=@Elements(uniquelist);
@For(n:=1; n<=total; n:=n+1;
max:=@Text(@Max(@TextToNumber(tmpval)));
pos:=@Member(max; tmpval);
tmpsorted:=tmpsorted : uniquelist[pos];
tmpval:=@If(pos=1; "0" : @Subset(tmpval; 1-elems); @Subset(tmpval; pos-1) : "0" : @subset(tmpval; pos-elems))
);
tmpsorted;
Speakin' of shudders, the idea of doing the top list in real time sounds like a wonderful way to bog a server to a standstill in a hurry.
Obviously, a view can't do it because of the way it's indexed. The parent docs are indexed and sorted, and a placeholder is thrown into the index for the @Descendants value. Then the responses are hung off the main docs, and finally the count of descendants is done and the special text placeholders are filled in.
That leaves an agent writing the top list somewhere -- could be a document, could be a subform design note, could be lots of things.
What about a catagorized view with a sum column counting the number of responses.
Design the view as json, and use some clientside javascript to sort the catagories.
LOL! Reading just the headline, I was sure that this post was related to your family and the joys of fatherhood. Looks like I was wrong, although I'd be interested in your findings from that point of view. :-)
I'd be sorted last in this house Fabian as I have 1.5 children to Karen's two.
My findings were that my only option is to write an agent, which I did and then got rid of the view I'd been hacking about.
Thanks to all for showing me the way.
Jake
Untested, but wouldn't @TextToNumber(@DocChildren("0"; "1"; "%")); work? Bear in mind this doesn't count the Response to Response...
Matt. Last time I tried that it turned out the result of @DocChildren is "special text" and can't be treated as a number or converted to text in that way.
Jake
And unfortunately this is still true, @ToNumber instead of @TextToNumber doesn't help either.
There's a reason for that, Fabian -- the value doesn't exist yet when the formula is computed. The formula is computed when the document is indexed into the view, but the value assigned to the special text placeholder doesn't get filled in until all of the documents and their descendants are indexed into the view. THAT's why you can't do anything with "special text" within Notes.
Stan, I had thought for a moment if I should refer to your above posting, but then decided against it (as everybody should have read that anyway :-)). I have to admit though, that while I always had a vague feeling about why some properties are special text, I never realized what was going on exactly. Because of that I plain LOVED your slick and clear explanation. A clear reasoning about what's going on is always better than vague feelings ...
Of course, there is still the "dirty" way to make use of special text in web applications: As soon as it has been transfered to the browser, it's no longer "special", so JavaScript provided as pass-through HTML can make full use even of the results of @Descendants, @DocNumber and alike. But I'm definitely not going to promote this approach.