2 Replies Latest reply on Dec 11, 2016 7:21 PM by Vaughan

    Custom Function Help Need (recursive)

    fmdataweb

      I'm stuck trying to write a custom function that encodes a list of email addresses. I have a list of email address in a field, each one on a new line, like this:

       

      bob@hope.com

      sally@smith.com

      julie@acme.com

       

      I need to convert it into this format:

       

      "bob@hope.com", "sally@smith.com", "julie@acme.com"

       

      where each email address is surrounded by quote marks and has a comma separator between each email address (but not after the last email address).

       

      I've created a custom function called ConvertEmails (text) with this formula:

       

      Case ( ValueCount ( text ) > 0 ;

      "\"" & GetValue ( text ; 1 ) & "\""  &", "& ConvertEmails ( RightWords ( text ; ValueCount ( text ) - 1 ) ) ;

      )

       

      which currently outputs this:

       

      "bob@hope.com", "julie@acme.com",

       

      I'm stuck at this point and hope someone more experienced with recursive custom functions can show me what I'm missing here.

       

      thanks,

      Steve

        • 1. Re: Custom Function Help Need (recursive)
          erolst

          Wouldn't

           

          "\"" & Substitute ( yourList ; Char(10) ; "\", \"" ) & "\""

           

          be easier?

           

          As to your original problem: in the last iteration the text passed is only a single line, so you can make the insertion of the comma dependent on

          ValueCount ( text ) > 1. I'd do this like so:

           

          Let ( [

            vc = ValueCount ( text ) ;

            moreToFollow = vc > 1 ;

            theLine = GetValue ( text ; 1 ) ;

            theQuote = "\"" ;

            theComma = Case ( moreToFollow ; ", " )

            ] ;

            theQuote & theLine & theQuote & theComma &

            Case ( moreToFollow ; Char(10) & ConvertEMails ( RightValues ( text ; vc - 1 ) )

          )

          • 2. Re: Custom Function Help Need (recursive)
            Vaughan

            Don't need a recursive custom function for this.

             

            If the values are already in a list (paragraph delimited) then all you need to do is:

             

            prepend a <quote> to the start of the string

            replace the paragraph delimiters with <quote comma space quote>

            add a <quote> to the end of the string

             

            "\"" & Substitute( list_of_addresses ; "¶" ; "\", \"" ) & "\""

             

            This will handle the case of just one value. To handle no values:

             

            Case(

            not IsEmpty( list_of_addresses ) ;

            "\"" & Substitute( list_of_addresses ; "¶" ; "\", \"" ) & "\""

            )

             

            There is no need to make this a recursive function -- recursion will only add complexity and limits.