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

)

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.

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

Edited August 25, 200916 yr by Guest
Ninja'd by commet!

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.

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 )

)

)

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