4 Replies Latest reply on Oct 22, 2012 10:43 AM by philmodjunk

    help with list calculation

    kilbot

      Title

      help with list calculation

      Post

           Is there a way to write this list function to place "and" prior to the last field?

           Example (This is what the calculation does at this time):

           apple, milk, cherry

           I would like it to do:

           apple, milk and cherry

      This is my calculation

      Substitute ( List ( field) ; ¶ ; ", ")

        • 1. Re: help with list calculation
          LaRetta_1

               Try:

               Let ( [
               values = List ( relatedTable::field ) ;
               pos =  Position ( values ; ¶ ; 1 ;  PatternCount ( values ; ¶ ) ) ;
               string = Replace ( values ; pos ; 1 ; " and " )
               ] ;
               Substitute ( string ; ¶ ; ", " )
               )  // end let

               EDITED:  I realized you were pulling from a related table so I changed the calc.

          • 2. Re: help with list calculation
            kilbot

                 Okay that works but what if I want to list the value (name) only if a field with the related record has the correct title

                  

                 i.e. I have a portal pulling over names of parties involed in my complaints. I have a field that designates if the party is a complainant or witness. So how would I make this work if I wanted to list only the "witness"?

            • 3. Re: help with list calculation
              raybaudi

                   You'll have to list a NEW related calculation field with a calc like this:

                   If ( party = "witness" ; name )

              • 4. Re: help with list calculation
                philmodjunk
                                    

                                         You'll have to list a NEW related calculation field with a calc like this:

                                    

                                         If ( party = "witness" ; name )

                               

                                    Or set up a relationship that filters by the typ of individual. The above method is my preferred approach if I only need a specific subset of records and don't ever anticipate using this method to pull up values from other groups. With the second method it's possible to use this method to work with either complainents or witnesses by changing a value in a global match field used in the relationship.