SID-02169: Search Best Practice - Comma and Hash

Status:	Answered	TWiki version:	6.0.1	Perl version:	5.10.1
Category:	CategorySearch	Server OS:	CentOS release 6.5, kernel 2.6.32-431.29.2.e16.x86_64	Last update:	10 years ago

I'm trying to understand what the best way of dealing with multiple entries in a form field is when searching.

I started this search using a nested search but then found Display form field of a referenced topic in formatted search which lead me to using a hash that has saved a considerable amount of time in the search. I've updated FormattedSearch so the Nested Search section has a note referencing hashes as a potential alternate method.

%SEARCH{ "name~'ProgDB????'" type="query" nonoise="on"
format="$percntCALCULATE{$SETHASH(ProgTopic, $formfield(ProgramNumber), $topic) $SETHASH(ProgStatus, $formfield(ProgramNumber), $formfield(ProgramStatus))}$percnt" }%

Search:

%SEARCH{ "name~'DrawingDB*'" type="query" nonoise="on"
format="| $formfield(Part Number) | [[$percntCALCULATE{$GETHASH(ProgTopic, $formfield(ProgramNumber))][$formfield(ProgramNumber)]] | | $GETHASH(ProgStatus, $formfield(ProgramNumber)) }$percnt | [[$topic][Edit]] |" 
}%

The challenge I'm having is that sometimes the ProgramNumber formfield can contain more than one entry (it's a select+multi box). I'm not entirely sure how to handle this in any kind of elegant way. For example, a typical drawing would be a single program 'ABC' which presents no issues but some drawings can have multiple programs 'ABC, DEF'.

My goal is to be able to include any number of ProgramNumber entries in a single cell for the drawing.

Any help, examples, or direction on commands to look into would be appreciated.

-- Jani Hamalainen - 2016-03-08

Discussion and Answer

I've done a bit of a work-around. In the event that ProgramNumber has multiple entries the table just shows "Multiple". The end goal is replacing "Multiple" with a clickable link to each program (without breaking the table).

 format="| $formfield(Part Number) | $percntCALCULATE{$IF($LISTSIZE($formfield(ProgramNumber))>1, Multiple |  -  ,[[$GETHASH(ProgTopic, $formfield(ProgramNumber))][$formfield(ProgramNumber)]] | $GETHASH(ProgStatus, $formfield(ProgramNumber))) }$percnt | [[$topic][Edit]] |" 

To further illustrate the end goal:

-- Jani Hamalainen - 2016-03-08

Thank you Jani for adding the note to FormattedSearch - see my comments on this at the bottom.

In terms of special chracters in search results that might interfere with spreadsheet calculations, you can escape them by enclosing search result in tripple-quotes, such as $SETHASH(someName, '''$formfield(SomeField)'''). The SpreadSheetPlugin properly restores the strings in the final rendering process.

This approach works well if you simply want to manipulate content, such as creating a shortened string for later use. However, it can't be used if you want to operate on commas in strings as lists. In this case don't use tripple-quotes, and assume potential commas and parenthesis that might interfere with functions. For example, if a function expects three parameters, and the first one is a string from a search result, commas in that string are interpreted as function parameter separators, messing up your parameters. In some cases I have used $TRANSLATE() to temporarily change commas to something else, then use other functions that are sensitive to commas in strings, then before final output use $TRANSLATE() again to restore the commas.

Use this formula to operate on a comma list, such as to create a link out of each list item: $LISTJOIN($sp, $LISTEACH(use $item to create a link), $GETHASH(foo, nameReturningValueWithCommaList))

-- Peter Thoeny - 2016-03-09

Thanks a lot for your detailed answer Peter. The formula you provided allowed me to do exactly what I was hoping for. Although, I was unable to get $n working within $LISTJOIN so I used <br>

I've updated FormattedSearch as per your suggestions and created a blog entry to document what I've learned on nested / hash searches so far.

Any feedback or improvement to the post is welcomed - TWiki:Blog.BlogEntry201603x1

-- Jani Hamalainen - 2016-03-12

Cool, thank you Jani!

-- Peter Thoeny - 2016-03-12

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.