recursive help

[rivet]

Can anyone help reduce this to a recursive calculation. The actual list is over fifty items.

Let ( [

lst = "car¶dog¶apple¶kitchen sink¶ball¶tree¶table¶bottle"

tab = " " ] ;

If ( PatternCount ( specialty ; Substitute ( GetValue ( lst ; 1 ) ; "¶" ; "" ) ); "•" ; "" ) & tab &

If ( PatternCount ( specialty ; Substitute ( GetValue ( lst ; 2 ) ; "¶" ; "" ) ); "•" ; "" ) & tab &

If ( PatternCount ( specialty ; Substitute ( GetValue ( lst ; 3 ) ; "¶" ; "" ) ); "•" ; "" ) & tab &

If ( PatternCount ( specialty ; Substitute ( GetValue ( lst ; 4 ) ; "¶" ; "" ) ); "•" ; "" ) & tab &

If ( PatternCount ( specialty ; Substitute ( GetValue ( lst ; 5 ) ; "¶" ; "" ) ); "•" ; "" ) & tab &

If ( PatternCount ( specialty ; Substitute ( GetValue ( lst ; 6 ) ; "¶" ; "" ) ); "•" ; "" ) & tab &

If ( PatternCount ( specialty ; Substitute ( GetValue ( lst ; 7 ) ; "¶" ; "" ) ); "•" ; "" ) & tab &

If ( PatternCount ( specialty ; Substitute ( GetValue ( lst ; 8 ) ; "¶" ; "" ) ); "•" ; "" ) & tab

)

[comment]

What's in the specialty field, and what exactly is the purpose of this calculation?

BTW, the result of GetValue() cannot - by definition - contain a carriage return, so all those substitutions are redundant.

[David Jondreau]

What does the data in the field "specialty" look like? What is your goal with this calculation?

Edited August 25, 2009 by Guest
Ninja'd by commet!

[rivet]

specialty is a single field formatted as a check box, with that list of values.

In the end I am trying to create a chart. Records down the left, value list across the top, then a grid of dots showing which values where checked for that records.

         v1  v2   v3  v4  v5  v6  v7  v8  v9....

record1  | • |   | • |   | • |   | • | • | • |

record2  |   |   | • | • |   | • | • | • | • |

record3  |   | • | • |   | • | • |   |   |   |

ignore the vertical bars, they are to illustrate the columns.There will be a tab character for each column, so I can space the result with the text ruler.

[comment]

1. PatternCount() is not a good test for finding if a specific value has been checked: it can easily return a false positive when one value contains another, e.g. "run" and "crunch".

2. IMHO, the best way to do this is to use a repeating calculation field =

not IsEmpty ( FilterValues ( GetValue ( ValueListItems ( Get (FileName) ; "Categories" ) ; Get ( CalculationRepetitionNumber ) )  ; Extend ( Specialty ) ) )

Make the result type a Number, and format the field as Boolean, showing non-zeros as a bullet.





3. If you prefer to use a custom function, try:



TabularizeValues ( listOfValues ; selectedValues )

Let ( [

countValues = ValueCount ( listOfValues )

] ; 

Case ( not IsEmpty ( FilterValues ( GetValue ( listOfValues ; 1 ) ; selectedValues ) ) ; "•" )

&

Case ( 

countValues > 1 ; 

Char (9) & TabularizeValues ( RightValues ( listOfValues ; countValues - 1 ) ; selectedValues )

)

)

[rivet]

Thanks Comment. I went with the repeating fields, which saves much time -- no tabbing, and no placing of vertical lines.