Question

I'd like to format a data taken from a form field with checkbox type. For example, the field is named Fruits and the selections are Apple, Banana, Mango, Orange and Pineapple. If the user ticks the checkbox beside Banana and Mango, the data displayed in the Fruits field after the form is saved is Banana, Mango. Basically, upon data extraction, I want to replace the comma with " and" (without the quotation marks). I've tried to do just that with the following:

%CALC{$SET(fruits, Banana, Mango)}%

%CALC{$SET(last, $LISTITEM($LISTSIZE($GET(fruits)), $GET(fruits)))}%

%CALC{$SET(fruits, $LISTIF($NOT($EXACT($item, $GET(last))), $GET(fruits)))}%

%CALC{$IF($LISTSIZE($GET(fruits))==0, $GET(last), $GET(fruits) and $GET(last))}%

Result:

Banana and Mango

But, when the data is taken from a form field with checkbox type, the calculations don't seem to work. I don't know if you have experienced this but I just did. Any enlightenment would be of great help.

Environment

-- FredanLangam - 21 Oct 2007

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.

Your problem is to change "," for "and" in displaying a list of item from a formfield() ? That can be accomplish with code like this:

%SEARCH{ "search_string"
format="  * fruits :  $percntCALC{ $SUBSTITUTE($formfield(fruits),$comma, and ) }<nop>%" }%

-- MarcoSilva - 21 Oct 2007

Ok, that did work as seen from the following lines. I had to change the substitution first from comma to semicolon and had to use TRANSLATE instead of SUBSTITUTE because it doesn't work. I don't know why. Then from there, SUBSTITUTE semicolon with " and " (minus the quotation marks). This is a real data taken from CheckboxSelectionTemplate.

SEARCH:

%SEARCH{ "META:FORM.*[W]ebForm" web="Sandbox" nonoise="on" type="regex" topic="CheckboxSelectionTemplate" format="$percntCALC{$SET(program, $TRANSLATE($formfield(Program Name),$comma,;))$SUBSTITUTE($GET(program),;, and )}$percnt" }%

Result:
CIMand WEB Ticket

But it has disadvantages though:
1. The " and " in the substitution, though prefixed with a space, ignores it.
2. The " and " substitution will only be logical if there are two items selected. A more generalized approach would be to put it between the last two items.

I guess for these reasons I used a different set of calculations above.

-- FredanLangam - 22 Oct 2007

I think I know the reason now why it's not working. In my original SEARCH, I used a CALC to search for something and within the SEARCH's format string, another set of calculations, those enumerated above, are nested. I replaced the CALC in the SEARCH string with a static text and the symptom went away.

-- FredanLangam - 22 Oct 2007