3 Replies Latest reply on Oct 11, 2013 10:38 AM by HelenaKaminski

    How to remove comma from value list calculation

    HelenaKaminski

      Title

      How to remove comma from value list calculation

      Post

           Hi,

           My question is probably elementary, but I have been unable to find a similar question in the forums. I apologize if I am repeating a common question!

           I am creating a database in FileMaker Pro 12 for an artist, and have fields for the medium of the artwork (Medium-Flatworks) and for the base on which the artwork is made (Base-Flatworks). These fields are on the layout as checkboxes with the value lists MediumList and BaseList. I have created the following calculation field (Base-Flatworks_c) for each to create a list of the selected values separated by commas:

           Substitute ( FilterValues ( ValueListItems ( Get ( FileName ) ; "BaseList" ) ; Base-Flatworks ) ; ¶ ; ", "  )

           This results in the following format: "BaseA, BaseB, BaseC," with the last value being followed by a comma. How can I remove this last comma so that I can create a calculation field to look like:

           Medium-Flatworks_c & " on " & Base-Flatworks_c

           (for example: "ink, crayon, paint on cardboard, gouache")

           It would be also great for the last value to start with an "and", so that the list would output as "ink, crayon, and paint" but this is less important.

           Thank you for your help!

        • 1. Re: How to remove comma from value list calculation
          philmodjunk

               Something is unusual with your data here. ValueListItems does NOT return a list with a return after the last value and thus there should be no final comma to remove. Is there, by any chance some value in your value list that is a non printing character such as a space? ValueLIstITems also appears to be not the right thing to use here as it will return a list of ALL values from the value list, not just those selected for a given record.

               Substitute ( CheckboxField ; ¶ ; ", "  )

               Should be all that you need to get the same list with commas. (And make sure that you do not have any additional text in the check box field as the checkbox value list will hide this data.) Perhaps there is a return at the end if you are using scripts or other methods that use one of the values functions such as RightValues, LeftValues, MiddleValues, FilterValues... They all make life difficult by appending a return to the end of the list of values which you usually then have to remove.

               Here's a calculation that removes the extra character if it is present and inserts the word "and" where needed:

               I am assuming that MediumList is a field formatted with multiple check boxes
                        

               Let ( [ TheList = IF ( Right ( MediumList ; 1 ) = ¶ ; Left ( MediumList ; Length ( MediumList ) - 1 ) ; MediumList ) ; //remove trailing return if it exists
                          Vcount = ValueCount ( TheList ) ;
                          LeftItems = LeftValues ( TheList ; Vcount - 1 ) ;
                          LeftItems = Left ( LeftItems ; Length ( LeftItems ) - 1 ) ; // strip off the extra return
                          TheList = If ( Vcount > 1 ; LeftItems & " and " & GetValue ( TheList ; vCount ) ; TheList ) // insert " and " if needed
                         ] ;
                           Substitute ( TheList ; ¶ ; ", " )
                      )
                         

          • 2. Re: How to remove comma from value list calculation
            gcatnine

                 $string = Substitute ( FilterValues ( ValueListItems ( Get ( FileName ) ; "BaseList" ) ; Base-Flatworks ) ; ¶ ; ", "  )

                 to remove the lastcharacter:    $new_string = Left( $string; Length ( $string )-1)

                 To substitute the last ",":

                 $a = PatternCount ( $new_string; ",")

                 $b = Position ( $new_string; ","; 1; $a)

                 Replace ( $new_string; $b; 1; " and ")

                  

                 all together: $string_no_last_comma = Replace ( $new_string; Position ( $new_string; ","; 1; PatternCount ( $new_string; ",")); 1; " and ")

            • 3. Re: How to remove comma from value list calculation
              HelenaKaminski

                   Thanks, Phil! Although my value lists are not unusual with no complex formatting or non-printing characters, the long form I was using did not list all the values, only the ones selected for the record. However the simpler script is much nicer! Let ( ... ) is working perfectly, just what I wanted. Thanks again!