4 Replies Latest reply on Jul 1, 2014 8:51 AM by beverly

    Sending HTML with portal data using 360 works

    slayden@msn.com

      Is there a way to send a table embedded in an HTML email using 360 works (or if not, anything) showing portal data?

       

      I can't seem to find an example.

       

      For a mortgage company.

       

      Dear <Salutation>;

       

      Below is a list of documentation recieved and needed for your loan application.

      Please review and send in the needed documentation as quickly as possible:

       

      <nPortalRows.>

       

      Thank you

      <Employee user data>

        • 1. Re: Sending HTML with portal data using 360 works
          Mike_Mitchell

          I'm not entirely sure what your question is. Are you asking how to encode an HTML table based on a set of related records (presumably from a current parent record)? Or are you asking how to encode HTML mail so a 360Works plugin can send it appropriately?

           

          The reason I ask is you have a "merge_fields" tag attached to your message. I'm not familiar with using 360Works plugins to send HTML mail, so I'm a little confused as to what your question actually is.

           

          Mike

          • 2. Re: Sending HTML with portal data using 360 works
            slayden@msn.com

            Sorry for not being clear.  Unclear questions lead to unclear solutions!

             

            I need to send HTML form letters with portal data in them.

            In PHP (or my native language, Coldfusion), this would be done using a table populated by looping a query variable.

             

            What is the way this is done using fmPro, so I can send a fully formatted html email with accurate table data populated by portalData?

             

            Is that more clear?

            Thanks!

            • 3. Re: Sending HTML with portal data using 360 works
              Mike_Mitchell

              Yes, you can do this. You can either loop over the portal rows using Go to Portal Row, or use a couple of methods that result in more batch-like processing. For example, you can:

               

              1) Use the List function to grab all the values each field, the loop over those values as you build the table (inserting appropriate delimiters in between). Example:

               

              Set Variable [ $column01 ; List ( relatedTable::field01 ) ]

              Set Variable [ $column02 ; List ( relatedTable::field02 ) ]

              Set Variable [ $htmlTable ; "<table>" ]

              Set Variable [ $count ; 1 ]

              Loop

                Set Variable [ $htmlTable ; $htmlTable & "<tr>" ]

                Set Variable [ $htmlTable ; $htmlTable & "<td>" & GetValue ( $column01 ; $count ) & "</td>" ]

                Set Variable [ $htmlTable ; $htmlTable & "<td>" & GetValue ( $column02 ; $count ) & "</td>" ]

                Set Variable [ $htmlTable ; $htmlTable & "</tr>" ]

                Exit Loop If [ Let ( $count = $count + 1 ; $count > ValueCount ( $column01 )) ]

              End Loop

              Set Variable [ $htmlTable ; "</table>" ]

               

              2) Use ExecuteSQL to grab all the necessary values at once, inserting the delimiters as part of the query:

               

              Set Variable [ $sqlQuery ; "SELECT field01, field02 FROM relatedTable WHERE primaryKey = ?" ]

              Set Variable [ $htmlTable ; "<table><tr><td>" & ExecuteSQL ( $sqlQuery ; "</td><td>" ; "</td></tr>" ; currentTable::primaryKey ) & "</table>" ]

               

              You can add whatever additional formatting you need (like CSS styling, etc.) in your table delimiters as you build the table.

               

              HTH

               

              Mike

              • 4. Re: Sending HTML with portal data using 360 works
                beverly

                or a variation where the table row is calculated (in the related record), then use list() to get the calculation field for the table. This allows many fields (table cells).

                 

                the eSQL method works well, too.

                 

                Beverly