Question

Hi,

I'm trying to generate a list of unique results from a search. I know the SpreadSheetPlugin has a function called LISTUNIQUE which could pretty much do what I need... is there a way to pass my search results to it so I get back a unique list seperated by commas? Or is there another way of doing this?

My search string right now:

%SEARCH{"ISSUETAG" excludetopic="BugTemplate" regex="on" order="formfield(Platform)" nosearch="on" nototal="on" format=" $formfield(Platform), "}%

And on a related note, I'd like your opinions on what I'm trying to do here... maybe there's a better way of doing it that I don't know about.

Basically I have topics that are categorized by two form fields (Platform and category). What I'd like to do is automatically generate a listing of all these topics, sorted by the platforms and categories being used.

The basic structure would like this...

loop through all existing platforms

• create header for the platform
• loop through all existing categories
  • create header for the category
  • list topics
• iterate loop

I've installed the ForEachPlugin for looping; I figured I'd loop through my unique search result lists for the loops... so the technical snags I've hit so far are generating those unique lists and also using my loop variables within my regex search strings.

So what do you guys think? Is what I'm doing feasible? Is there an easier way of doing it?

I really appreciate any help I can get on this.

Environment

-- BehrouzBayat - 23 Jun 2006

Answer

If you answer a question - or someone answered one of your questions - please remember to edit the page and set the status to answered. The status selector is below the edit box.

You can use SpreadSheetPlugin formulae in a search format. The trick is to delay the execution of the formula so that it runs on each search hit. Do that by escaping the %CALC with $percnt. Here is an untested example how to build up a list in the SEARCH format:

format="$percntCALC{$SET(platforms, $GET(platforms), $formfield(Platform))}$percnt"

After the SEARCH you can use %CALC{}% to manipulate the list.

FormattedSearch has an example on nested searches. A word of caution, nested searches can be slow.

Please let us know your progress.

-- PeterThoeny - 26 Jun 2006

Hi,

Thanks for the reply. I'll fiddle with this some more later this week and let you know what happens.

-- BehrouzBayat - 27 Jun 2006

I got the SpreadSheetPlugin formula working in the the search format... your example was pretty much perfect: I just had to inverse the get with the formfield variable.

format="$percntCALC{$SET(categories, $formfield(Category), $GET(categories))}$percnt"

This way it generates a list "a, b, " instead of " , a, b", which gives the correct final list when parsing it with LISTUNIQUE.

Now, is it possible to use SpreadSheetPlugin formulae within the text string of a search?

As I wrote above, I'm trying to generate a list of platforms and then generate a list of categories based on those platforms...

So I'd need something like:

%FOREACH{"platform" in="%CALC{$LISTUNIQUE($GET(platforms))}%"}%
   * $platform

<!-- Set iteration item to a spreadsheet variable -->
%CALC{$SET(currentPlatform, $platform)}%

%SEARCH{"DESCRIPTOR;[P]latform.*(td..td|value\=).*$percntCALC{$GET(currentPlatform)}$percnt" regex="on" ...}%

%NEXT{"platform"}%

Is there a way to use that iteration variable (platform) inside my regex search string - either directly or through a spreadsheet variable?

-- BehrouzBayat - 28 Jun 2006

I am not familiar wih the ForEachPlugin, may be someone else can help.

SEARCH is an internal command and evaluates before Plugins. That means this time you need to delay the SEARCH. Untested example:
%CALC{"$percntSEARCH{\"DESCRIPTOR;[P]latform.*value\=.*$GET(currentPlatform)\" regex=\"on\" ...}$percnt"}%

BTW, you do not need the (td..td| unless you have topics created by a very old TWiki version.

-- PeterThoeny - 29 Jun 2006

Ok, let's forget about the ForEachPlugin... What I'm having difficulties with isn't related to that, it's more regarding search and calc syntax.

Using the untested example above, I've managed to output a list:

%CALC{"$percntSEARCH{\"DESCRIPTOR;[P]latform.*value\=.*$GET(currentPlatform)\" regex=\"on\" ... format=\"$formfield(Category), \"}$percnt"}%

Now I need to merge this with the first example you gave me:

format="$percntCALC{$SET(categories, $formfield(Category), $GET(categories))}$percnt"

I just can't seem to wrap my head around the proper syntax... how would I go about using the format string above within my search?

Thanks again.

-- BehrouzBayat - 29 Jun 2006

Oh, that is like solving a puzzle. If I understand correctly, you want to issue a SEARCH for each category item assembled in the first SEARCH. You can use $LISTMAP() do do that. Here is the idea:

%CALC{"$LISTMAP($percntSEARCH{\"DESCRIPTOR;[P]latform.*value\=.*$GET($item)\" regex=\"on\" ... format=\"$formfield(Category)\"}$percnt, $GET(categories))"}%

Watch out that you do not introduce commas in the formula part of the $LISTMAP().

Keep in mind that nested SEARCHes can be slow.

-- PeterThoeny - 01 Jul 2006

Almost...

You're right, I have to issue a SEARCH for each item assembled in the first SEARCH. However, I want these subsequent searches to generate lists.

Maybe a concrete example will make things clearer:

Let's say you have topics that can be categorized by two things: fruit type and color.

I have topics created for Apples (Colors Yellow, Green and Red) and Berries(Colors Green and Red).

So here's what I want to do:

• Step 1: generate a list of all fruit types currently used (Apples, Berries) -> OK
• Step 2: generate a list of colors used for each fruit type: first list would be "Yellow, Green, Red", the second "Green, Red". -> Not OK: need help
• Step 3: list all topics in the proper categories -> OK

The final output would look something like:

• Apples
  • Yellow
    • Yellow_Apple1
    • Yellow_Apple2
    • ...
  • Green
    • Green_Apple
  • Red
    • Red_Apple1
    • Red_Apple2

• Berries
  • Green
    • Green_Berry1
  • ...

etc.

So like I said above, step 2 is what I'm missing right now... The reason why I mentioned the ForEachPlugin is because I thought it would be an easy way to get the formatting I wanted. To make it work though, I need to have those lists for each formfield (fruit type and color in the example above). With your help I was able to get the first list going, but getting the subsequent ones seem to be trickier.

-- BehrouzBayat - 04 Jul 2006

Ok, here's a more direct question which might be easier to answer:

What would be the syntax to get $LISTUNIQUE to work with the results of a search that has this format:

%CALC{"$percntSEARCH{\"DESCRIPTOR;[P]latform.*value\=.*$GET(currentPlatform)\" regex=\"on\" ... format=\"$formfield(Category), \"}$percnt"}%

Again, thanks for your help.

-- BehrouzBayat - 04 Jul 2006

I'm trying to implement $LISTMAP like Peter suggested above and am having some syntax problem. Anybody know what's wrong with the following line:

%CALC{"$LISTMAP($percntSEARCH{\"DESCRIPTOR;[P]latform.*value\=.*J2me;[C]ategory.*value\=.*$item\" regex=\"on\" excludetopic=\"BugTemplate\" nosearch=\"on\" nototal=\"on\" format=\"   * $topic\"}$percnt, $LISTUNIQUE($GET(categories)))"}%

$LISTUNIQUE is ok, but when I do this listmap it outputs the search string as if there's a typo in it somewhere... it looks ok to me?

-- BehrouzBayat - 27 Jul 2006

$percnt is used to escape something in a SEARCH format, for CALC you can use $NOP() to achieve the same. Here is an example:

%CALC{$LISTMAP(%$NOP()SEARCH{\"META\:FIELD.*SupportStatus.*AnsweredQuestions;$item\" regex=\"on\" excludetopic=\"BugTemplate\" nosearch=\"on\" nototal=\"on\" format=\"   * $topic contains $item\"}$NOP()%, $LISTUNIQUE(seldom, rarely, seldom))}%

Expands to:

• ConfigurationErrors contains seldom
• ConfigureSyntaxError contains seldom
• DifferenceBetweenAddOnAndPlugin contains seldom
• HowToCombineCalcAndSearch contains seldom
• ImproperUseOfTheSaveScript contains seldom
• ResearchSurveyOfCorporateWikiUsers contains seldom
• TWikipmNotFound contains seldom, * BlockingBasedOnIP contains rarely
• FormsConfusion contains rarely
• HowToCombineCalcAndSearch contains rarely
• LdapContribLoginsAreSpotty contains rarely
• RcsHistoryRefusesToWork contains rarely
• SaveForever contains rarely
• SluggishPerformance contains rarely
• ThePageCannotBeDisplayed contains rarely
• WebNameAsWikiName contains rarely

-- PeterThoeny - 29 Jul 2006

I modified your example above to format it the way I'd like... here it is:

%CALC{$LISTMAP(<h2>$item</h2> %$NOP()SEARCH{\"META\:FIELD.*SupportStatus.*AnsweredQuestions;$item\" regex=\"on\" excludetopic=\"PhoneBugTemplate\" nosearch=\"on\" nototal=\"on\" format=\"$topic%BR%\"}$NOP()%, $LISTUNIQUE(seldom, rarely, seldom))}%

expands to

seldom

rarely

It seems almost perfect... the only hitch is that extra comma that's being displayed between the two headers. Any idea how I can get rid of it? It's a little annoying, especially when there are several elements in the list part of the listmap.

-- BehrouzBayat - 31 Jul 2006

Feed the output of $LISTMAP() into a $LISTJOIN().

-- PeterThoeny - 01 Aug 2006

Seems like a slightly roundabout way of doing it, but thanks; it works.

-- BehrouzBayat - 01 Aug 2006

This is spec, the output of a $LISTMAP() is another list, the reason why you need $LISTJOIN() to change the list separator from comma to something else.

-- PeterThoeny - 01 Aug 2006

Yeah I get that. I was talking more about the whole function...

Calc(Listjoin(Listmap(NOP(search),Listunique(Get(Myarray)))))

I just didn't realize it would be so complicated to output the search results I wanted.

Thanks a lot for your help though... couldn't have done it without you.

-- BehrouzBayat - 02 Aug 2006

Ok, now that you all have worked that out: Is there a way to do a search that will return the "ID" and the "Current State" from the approval plugin?

-- SharonStrait - 02 Aug 2006

Please open a new support question.

-- PeterThoeny - 02 Aug 2006

By the way, this is one of the ways I've been trying:

• Topic: Current State:
• HowToCombineCalcAndSearch: %APPROVALSTATEMESSAGE%

And this is the other way:

Searched: SprId[0-9]+ |" }%

I am using the Approval Plugin and can't find a way to list the %APPROVALSTATEMESSAGE% with the SprId. (Or even by itself.)

I saw the QM tool, but it really gives me more than I need.

Any help would be appreciated.

-- SharonStrait - 02 Aug 2006